ORACLE数据库数据操作优化(优化器)(讲)

1 The Optimizer 1 1 SQLSQL processingprocessing 2 2 OptimizationOptimization methodsmethods 3 3 ExecuteExecute SQLSQL statementsstatements SQL Processing Architecture EXPLAIN PLAN What Is The Optimizer Choosing an Optimizer Approach and Goal Cost Based Optimizer CBO CBO Parameters Extensible Optimizer Rule Based Optimizer RBO Overview of Optimizer Operations Optimizing Joins Optimizing Statements that Use Common Subexpressions Evaluation of Expressions and Conditions Transforming and Optimizing Statements SQL Processing Architecture 2 Parser Syntax analysis checks SQL statements for correct syntax Semantic analysis checks if the current database objects and attributes referenced are correct Optimizer rule basedrule based optimizeroptimizer RBO RBO cost basedcost based optimizeroptimizer CBO CBO Row Source Generator Inputs Inputs optimaloptimal planplan fromfrom thethe optimizeroptimizer Outputs Outputs executionexecution planplan forfor thethe SQLSQL statementstatement ExecutionExecution plan plan a a collectioncollection ofof rowrow sources asources a tree tree RowRow source source anan iterativeiterative controlcontrol structurestructure SQL Execution SQLSQL execution execution a a componentcomponent thatthat operatesoperates onon thethe executionexecution planplan Output Output thethe resultsresults ofof thethe query query What Is The Optimizer Optimizer Optimizer thethe mostmost efficientefficient wayway toto executeexecute a a SQLSQL statementstatement DMLDML statement statement SELECTSELECT INSERTINSERT UPDATEUPDATE oror DELETEDELETE InfluenceInfluence thethe optimizer optimizer byby settingsetting thethe optimizeroptimizer approachapproach andand goal goal byby gatheringgathering statisticsstatistics forfor thethe CBO CBO byby usingusing hintshints inin SQLSQL statementsstatements Execution Plan AnAn executionexecution planplan includes includes anan accessaccess methodmethod forfor eacheach tabletable andand anan orderingordering ofof thethe tablestables the the joinjoin orderorder SELECTSELECT ename ename job job sal sal dnamedname FROMFROM emp emp deptdept WHEREWHERE emp deptnoemp deptno dept deptnodept deptno ANDAND NOTNOT EXISTSEXISTS SELECT SELECT FROMFROM salgradesalgrade WHEREWHERE emp salemp sal BETWEENBETWEEN losallosal ANDAND hisal hisal 3 Steps of Execution Plan A A rowrow sourcesource a a setset ofof rowsrows returnedreturned byby a a stepstep TheThe numberingnumbering ofof thethe steps steps thethe orderorder forfor thethe EXPLAINEXPLAIN PLANPLAN statementstatement Steps indicated by the shaded boxes retrieve data from an object in the database Such steps are called access paths oSteps 3 and 6 read all the rows of the emp and salgrade tables respectively oStep 5 looks up each deptno value in the pk deptno index returned by step 3 oStep 4 retrieves the rows whose rowids were returned by step 5 from the dept table Steps indicated by the clear boxes operate on row sources oStep 2 performs a nested loops operation accepting row sources from steps 3 and 4 joining each row from step 3 source to its corresponding row in step 4 and returning the resulting rows to step 1 oStep 1 performs a filter operation It accepts row sources from steps 2 and 6 eliminates rows from step 2 that have a corresponding row in step 6 and returns the remaining rows from step 2 to the user or application issuing the statement Execution Order ExecutionExecution order order formform thethe leafleaf nodesnodes toto rootroot nodes 3 5 4 2 6 1 nodes 3 5 4 2 6 1 4 Choosing an Optimizer Approach and Goal OptimizingOptimizing forfor bestbest throughput throughput resultresult inin a a fullfull tabletable scanscan ratherrather thanthan anan indexindex scan scan oror a a sort mergesort merge joinjoin ratherrather thanthan a a nestednested loopsloops join join OptimizingOptimizing forfor bestbest responseresponse time resultstime results inin anan indexindex scanscan oror a a nestednested loopsloops join join For applications performed in batch such as Oracle Reports applications optimize for best throughput For interactive applications such as Oracle Forms applications or SQL Plus queries optimize for best response time For queries hat use ROWNUMROWNUM to limit the number of rows optimize for best response time OPTIMIZER MODE Initialization Parameter CHOOSECHOOSEcontainscontains statisticsstatistics forfor atat leastleast oneone ofof thethe accessedaccessed tables tables a a cost basedcost based approachapproach andand bestbest throughputthroughput containscontains nono statisticsstatistics forfor anyany ofof thethe accessedaccessed tables tables a a rule basedrule based approach defaultapproach default value value ALL ROWSALL ROWSa a cost basedcost based approachapproach andand bestbest throughputthroughput minimum minimum resourceresource useuse toto completecomplete thethe entireentire statement statement FIRST ROWSFIRST ROWS a a cost basedcost based approachapproach andand bestbest responseresponse timetime minimum minimum resourceresource useuse toto returnreturn thethe firstfirst rowrow ofof thethe set set RULERULEa a rule basedrule based approachapproach internalinternal informationinformation such such asas thethe numbernumber ofof datadata blocksblocks allocatedallocated toto thesethese tables tables Statistics in the Data Dictionary Statistics Statistics columns columns tables tables clusters clusters indexes indexes andand partitions CBO partitions CBO collectcollect exactexact oror estimatedestimated statistics statistics DBMS STATSDBMS STATS package package thethe ANALYZEANALYZE statement statement oror thethe COMPUTECOMPUTE STATISTICSSTATISTICS clauseclause OPTIMIZER GOAL Parameter of the ALTER SESSION Statement OPTIMIZER GOALOPTIMIZER GOAL parameter parameter cancan overrideoverride OPTIMIZER MODEOPTIMIZER MODE Affect Affect thethe optimizationoptimization ofof SQLSQL statementsstatements issuedissued byby storedstored proceduresprocedures andand functionsfunctions calledcalled duringduring thethe sessionsession 5 doesdoes notnot affect affect thethe optimizationoptimization ofof recursiverecursive SQLSQL statementsstatements thatthat OracleOracle issuesissues duringduring thethe session session CHOOSECHOOSEcontainscontains statisticsstatistics forfor atat leastleast oneone ofof thethe accessedaccessed tables tables a a cost basedcost based approachapproach andand bestbest throughputthroughput containscontains nono statisticsstatistics forfor anyany ofof thethe accessedaccessed tables tables a a rule basedrule based approachapproach ALL ROWSALL ROWSa a cost basedcost based approachapproach andand bestbest throughputthroughput minimum minimum resourceresource useuse toto completecomplete thethe entireentire statement statement FIRST ROWSFIRST ROWS a a cost basedcost based approachapproach andand bestbest responseresponse timetime minimum minimum resourceresource useuse toto returnreturn thethe firstfirst rowrow ofof thethe resultresult set set RULERULEa a rule basedrule based approachapproach Changing the Goal with Hints Hint Hint FIRST ROWSFIRST ROWS ALL ROWSALL ROWS CHOOSECHOOSE oror RULE RULE overrideoverride OPTIMIZER MODEOPTIMIZER MODE andand OPTIMIZER GOAL OPTIMIZER GOAL ALTERALTER SESSIONSESSION SETSET OPTIMIZER MODEOPTIMIZER MODE FIRST ROWS FIRST ROWS Cost Based Optimizer CBO InIn general general youyou shouldshould alwaysalways useuse thethe cost basedcost based approach approach TheThe rule rule basedbased approachapproach isis availableavailable forfor thethe benefitbenefit ofof existingexisting applications applications 1 The optimizer generates a set of potential plans for the SQL statement based on its available access paths and hints 2 The optimizer estimates the cost of each plan based on statistics in the data dictionary for the data distribution and storage characteristics of the tables indexes and partitions accessed by the statement 3 The optimizer compares the costs of the plans and chooses the one with the smallest cost ToTo maintainmaintain thethe effectivenesseffectiveness ofof thethe CBO CBO youyou mustmust gathergather statisticsstatistics andand keepkeep themthem current current Architecture of the CBO 6 Query Transformer Input Input a a parsedparsed query representedquery represented byby a a setset ofof queryquery blocks blocks MainMain objective objective determinedetermine ifif itit isis advantageousadvantageous toto changechange thethe formform ofof thethe query query soso thatthat itit enablesenables generationgeneration ofof a a betterbetter queryquery planplan ThreeThree queryquery transformationtransformation techniques techniques viewview merging merging subquerysubquery unnesting unnesting andand queryquery rewriterewrite usingusing materializedmaterialized views combination views combination Estimator ThreeThree differentdifferent typestypes ofof measures measures selectivity selectivity cardinality cardinality 基数基数 andand cost cost TheThe endend goalgoal ofof thethe estimator estimator estimateestimate thethe overalloverall costcost ofof a a givengiven plan plan Selectivity Selectivity a a fractionfraction ofof rowsrows fromfrom a a rowrow set set thethe selectivityselectivity ofof a a predicatepredicate indicatesindicates howhow manymany rowsrows fromfrom a a rowrow setset willwill passpass thethe predicatepredicate test test Cardinality Cardinality thethe numbernumber ofof rowsrows inin a a rowrow set set Cost Cost unitsunits ofof workwork oror resourceresource used used TheThe CBOCBO usesuses diskdisk I OI O asas a a unitunit ofof work work Plan Generator MainMain function function trytry outout differentdifferent possiblepossible plansplans forfor a a givengiven queryquery andand pickpick thethe oneone thatthat hashas thethe lowestlowest cost cost 7 Features that Require the CBO Partitioned tables Index organized tables Reverse key indexes Function based indexes SAMPLESAMPLE clauses in a SELECTSELECT statement Parallel execution and parallel DML Star transformations Star joins Extensible optimizer Query rewrite materialized views Progress meter Hash joins Bitmap indexes Partition views release 7 3 Using the CBO ToTo useuse thethe CBOCBO forfor a a statement statement collectcollect statisticsstatistics andand enableenable thethe CBO CBO Make sure that the OPTIMIZER MODEOPTIMIZER MODE initialization parameter is set to its default value of CHOOSECHOOSE To enable the CBO for your session only issue an ALTERALTER SESSIONSESSION SETSET OPTIMIZER MODEOPTIMIZER MODE statement with the ALL ROWSALL ROWS or FIRST ROWSFIRST ROWS clause To enable the CBO for an individual SQL statement use any hint other than RULERULE Access Paths for the CBO A A fullfull tabletable scan scan retrievesretrieves rowsrows fromfrom a a table table cancan bebe performedperformed veryvery efficientlyefficiently usingusing multiblockmultiblock reads reads A A samplesample tabletable scan scan retrievesretrieves a a randomrandom samplesample ofof datadata fromfrom a a tabletable whenwhen thethe statement sstatement s FROMFROM clauseclause includesincludes thethe SAMPLESAMPLE clauseclause oror thethe SAMPLESAMPLE BLOCKBLOCK clause SELECTclause SELECT FROMFROM empemp SAMPLESAMPLE BLOCKBLOCK 1 1 1 1 A A tabletable accessaccess byby rowed rowed retrievesretrieves rowsrows fromfrom a a table table LocatingLocating a a rowrow byby itsits rowidrowid isis thethe fastestfastest wayway forfor OracleOracle toto findfind a a singlesingle row row 8 A A clustercluster scan scan retrievesretrieves rowsrows thatthat havehave thethe samesame clustercluster keykey value value InIn anan indexedindexed cluster cluster allall rowsrows withwith thethe samesame clustercluster keykey valuevalue areare storedstored inin thethe samesame datadata blocks blocks A A hashhash scan scan locatelocate rowsrows inin a a hashhash clustercluster basedbased onon a a hashhash value value InIn a a hashhash cluster cluster allall rowsrows withwith thethe samesame hashhash valuevalue areare storedstored inin thethe samesame datadata blocks blocks AnAn indexindex scan scan retrievesretrieves datadata fromfrom anan indexindex basedbased onon thethe valuevalue ofof oneone oror moremore columnscolumns ofof thethe index types index types Unique Range Full FastUnique Range Full Fast full Indexfull Index join Bitmap join Bitmap How the CBO Chooses an Access Path The available access paths for the statement The estimated cost of executing the statement using each access path or combination of paths Extensible Optimizer ItIt allowsallows thethe authorsauthors ofof user defineduser defined functionsfunctions andand domaindomain indexesindexes toto controlcontrol thethe threethree mainmain componentscomponents thatthat thethe CBOCBO usesuses toto selectselect anan executionexecution plan plan statistics statistics selectivity selectivity andand costcost evaluation evaluation Overview of Optimizer Operations thethe typestypes ofof SQLSQL statementsstatements thatthat cancan bebe optimizedoptimized thethe operationsoperations performedperformed byby thethe optimizeroptimizer Types of SQL Statements SimpleSimple statementstatementAnAn INSERTINSERT UPDATEUPDATE DELETEDELETE oror SELECTSELECT statementstatement thatthat involvesinvolves onlyonly a a singlesingle table table SimpleSimple queryqueryAnotherAnother namename forfor a a SELECTSELECT statement statement JoinJoinA A queryquery thatthat selectsselects datadata fromfrom moremore thanthan oneone table table EquijoinEquijoin A A joinjoin conditioncondition containingcontaining anan equalityequality operator operator Non equijoinNon equijoin A A joinjoin conditioncondition containingcontaining somethingsomething otherother thanthan anan equalityequality operator operator OuterOuter joinjoinA A joinjoin conditioncondition usingusing thethe outerouter joinjoin operatoroperator withwith oneone oror moremore columnscolumns ofof oneone ofof thethe tables tables CartesianCartesian productproduct A A joinjoin withwith nono joinjoin conditioncondition resultsresults inin a a CartesianCartesian product product oror a a crosscross product product ComplexComplex statementstatement AnAn INSERTINSERT UPDATEUPDATE DELETEDELETE oror SELECTSELECT statementstatement thatthat 9 containscontains a a subquery subquery CompoundCompound queryqueryA A queryquery thatthat usesuses setset operatorsoperators UNIONUNION UNIONUNION ALLALL INTERSECTINTERSECT oror MINUSMINUS toto combinecombine twotwo oror moremore simplesimple oror complexcomplex statements statements StatementStatement accessingaccessing viewsviews Simple Simple join join complex complex oror compoundcompound statementstatement thatthat accessesaccesses oneone oror moremore viewsviews asas wellwell asas tables tables DistributedDistributed statementstatement A A statementstatement thatthat accessesaccesses datadata onon twotwo oror moremore distinctdistinct nodesnodes ofof a a distributeddistributed database database Optimizer Operations 1 1EvaluationEvaluation ofof expressionsexpressions andand conditionsconditions 2 2StatementStatement transformationtransformation 3 3ViewView mergingmerging 4 4ChoiceChoice ofof optimizeroptimizer approachesapproaches 5 5ChoiceChoice ofof accessaccess pathspaths 6 6ChoiceChoice ofof joinjoin ordersorders 7 7ChoiceChoice ofof joinjoin operationsoperations Optimizing Joins howhow thethe optimizeroptimizer executesexecutes SQLSQL statementsstatements thatthat containcontain joins joins anti joins anti joins andand semi joinssemi joins howhow thethe optimizeroptimizer cancan useuse bitmapbitmap indexesindexes toto executeexecute starstar queries queries whichwhich joinjoin a a factfact tabletable toto multiplemultiple dimensiondimension tablestables Optimizing Join Statements Access Paths As for simple statements the optimizer must choose an access path to retrieve data from each table in the join statement Join Operations To join each pair of row sources Oracle must perform one of these operations Nested Loops NL Join Sort Merge Join Hash Join not available with the RBO Cluster Join Join OrderOracle joins two of the tables and then joins the resulting row source to the next table Join Operations 10 Nested Loops NL Join 1 The optimizer chooses one of the tables as the outer table or the driving table The other table is called the inner table 2 For each row in the outer table Oracle finds all rows in the inner table that satisfy the join condition 3 Oracle combines the data in each pair of rows that satisfy the join condition and returns the resulting rows Sort Merge Join 1 Oracle sorts each row source to be joined if they have not been sorted already by a previous operation The rows are sorted on the values of the columns used in the join condition 2 Oracle merges the two sources so that each pair of rows one from each source that contain matching values for the columns used in the join condition are combined and returned as the resulting row source Hash Join 1 Oracle performs a full table scan on each of the tables and splits each into as many partitions as possible based on the available memory 2 Oracle builds a hash table from one of the partitions if possible Oracle selects a partition that fits into available memory Oracle then uses the corresponding partition in the other table to probe the hash table All partition pairs that do not fit into memory are placed onto disk 3 For each pair of partitions one from each table Oracle uses the smaller one to build a hash table and the larger one to probe the hash table Cluster Join OracleOracle cancan performperform a a clustercluster joinjoin onlyonly forfor anan equijoinequijoin thatthat equatesequates thethe clustercluster keykey columnscolumns ofof twotwo tablestables inin thethe samesame cluster cluster Evaluation of Expressions and Conditions HowHow thethe optimizeroptimizer evaluatesevaluates expressionsexpressions andand conditionsconditions thatthat containcontain thethe following following Constants 11 LIKE Operator IN Operat