Oracle SQL调优课程教材 - Les06

Copyright©OracleCorporation,2003.Allrightsreserved.IntroductiontotheOptimizerOracle9iDatabase:SQLTuningWorkshopR26-26-2Copyright©OracleCorporation,2003.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•DescribethefunctionsoftheOracle9icost-basedoptimizer(CBO)•IdentifythefactorsthattheCBOconsiders•SettheoptimizerapproachattheinstanceandsessionlevelObjectivesAftercompletingthislesson,youshouldbeableto:•DescribethefunctionsoftheOracle9icost-basedoptimizer(CBO)•IdentifythefactorsthattheCBOconsiderswhenitselectsanexecutionplan•Settheoptimizerapproachattheinstanceandsessionlevel•UsedynamicsamplingOracle9iDatabase:SQLTuningWorkshopR26-36-3Copyright©OracleCorporation,2003.Allrightsreserved.FunctionsoftheOracle9iOptimizer•Evaluatesexpressionsandconditions•Usesobjectandsystemstatistics•Decideshowtoaccessthedata•Decideshowtojointables•DecideswhichpathismostefficientFunctionsoftheOracle9iOptimizerTheoptimizeristhepartoftheOracle9iServerthatcreatestheexecutionplanforaSQLstatement.Anexecutionplanisaseriesofoperationsthatareperformedinsequencetoexecutethestatement.Mostofthesestepswerediscussedinthe“SQLStatementProcessing”lesson.Theoptimizerusesvariouspiecesofinformationtoworkoutthebestpath:•Hintssuppliedbythedeveloper•Statistics•Informationinthedictionary•WHEREclauseTheoptimizerusuallyworksinthebackground.However,withdiagnostictoolssuchasEXPLAINandSQL*PlusAUTOTRACE,youcanseethedecisionsthattheoptimizermakes.TheoptimizerdeterminesthemostefficientwaytoexecuteaSQLstatementafterconsideringmanyfactorsrelatedtotheobjectsreferencedandtheconditionsspecifiedinthequery.ThisdeterminationisanimportantstepintheprocessingofanySQLstatementandcangreatlyaffectexecutiontime.Note:TheoptimizermaynotmakethesamedecisionsfromoneversionoftheOracleDatabasetothenext.Inrecentversions,theoptimizermaymakedifferentdecisionsbecausebetterinformationisavailable.Oracle9iDatabase:SQLTuningWorkshopR26-4FunctionsoftheOracle9iOptimizer(continued)OptimizerOperationsForanySQLstatementprocessedbytheOracle9iServer,theoptimizerperformstheoperationslistedintheslide.Evaluationofexpressionsandconditions:Theoptimizerfirstevaluatesexpressionsandconditionscontainingconstantsasfullyaspossible.Statementtransformation:Forcomplexstatementsinvolving,forexample,correlatedsubqueriesorviews,theoptimizermighttransformtheoriginalstatementintoanequivalentjoinstatement.Choiceofoptimizerapproaches:Theoptimizerusesacost-basedapproachunlesstheOPTIMIZER_MODEparameterissettoRULE.Choiceofaccesspaths:Foreachtableaccessedbythestatement,theoptimizerchoosesoneormoreoftheavailableaccesspathstoobtaintabledata.Choiceofjoinorders:Forajoinstatementthatjoinsmorethantwotables,theoptimizerchooseswhichpairoftablesisjoinedfirst,thenwhichtableisjoinedtotheresult,andsoon.Choiceofjoinmethods:Foranyjoinstatement,theoptimizerchoosesanoperationtousetoperformthejoin.Oracle9iDatabase:SQLTuningWorkshopR26-56-5Copyright©OracleCorporation,2003.Allrightsreserved.Cost-BasedOptimization•Isstatisticsdriven•Basesdecisionsoncostcalculation–Numberoflogicalreads–Networktransmissions•Iscontinuouslyenhanced•EstimatesCPUusage(InOracle9i,costincludesestimatedusageofCPUforSQLfunctionsandoperators.)Cost-BasedOptimizationThecost-basedoptimizerbasesitsdecisionsoncostestimates,whichinturnarebasedonstatisticsstoredinthedatadictionary.Incalculatingthecostofexecutionplans,cost-basedoptimizationconsidersthefollowing:•Numberoflogicalreads(themostimportantfactor)•CPUutilization•NetworktransmissionsCost-basedoptimizationiscontinuallyenhancedwitheachnewOracleserverrelease,andseveralnewerfeatures—suchashashjoins,starqueries,histograms,andindex-organizedtables—areavailableonlytocost-basedoptimization.WithOracle9i,theestimatedusageofCPUforSQLfunctionsandoperatorsisincludedintheoverallestimateofcomputerresourceusage(togetherwithdiskI/Oandmemory)andisusedtocomputethecostofaccesspathsandjoinorders.Anestimateofnetworkusageisalsoincludedwhendataisshippedbetweenqueryserversrunningondifferentnodes.Theresultisanimprovedaccuracyofthecostandsizemodelsusedbythequeryoptimizer.Thishelpstheoptimizerproducebetterexecutionplans,therebyimprovingqueryperformance.Oracle9iDatabase:SQLTuningWorkshopR26-6Cost-BasedQueryOptimizationChallengesThetaskoftheoptimizeristobridgethegapbetweenwhatisspecifiedinaSQLstatementandhowthiscanbesolvedbytheexecutionengineofthedatabase.ForasingleSQLstatement,therecanbeahugenumberofalternativeexecutionplansthatallprovidethecorrectanswerbutdiffersignificantlyinexecutiontime.Thechallengefortheoptimizeristoquicklyfindacost-effectiveplan.Hereitshouldbalancethesearchtimeandthepredictedexecutiontimetofindthebestplanavailable.Toestimatethecostofanexecutionplan,theoptimizerusesacostmodelandstatistics.Thecostmodelconsistsofacostfunctionforeachpossibleoperatorofanexecutionplanandamechanismforestimatingtheintermediateresultsizes.Theproblemofqueryoptimizationisknowntobemathematicallycomplex.Andbecausethedecisionsmadebytheoptimizerarebasedonstatisticsandestimations,itcanneverguaranteefindingthemostcost-effe