Oracle执行计划讲解复习课程.doc

《Oracle执行计划讲解复习课程.doc》由会员分享，可在线阅读，更多相关《Oracle执行计划讲解复习课程.doc（89页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Good is good, but better carries it.精益求精，善益求善。Oracle执行计划讲解-Oracle执行计划讲解看懂Oracle执行计划是优化的第一步，让我们从下面的例子开始吧。下面为补充内容1、创建测试表SQLcreatetabletasselect1id,object_namefromdba_objects;TablecreatedSQLupdatetsetid=99whererownum=1;1rowupdatedSQLcommit;CommitcompleteSQLcreateindext_indont(id);Indexcreatedoracle优化器：

2、RBO和CBO两种，从oracle10g开始优化器已经抛弃了RBO，下面的列子说明CBO大概是怎样的SQLselect/*+dynamic_sampling(t0)*/*fromtwhereid=1;50819rowsselected.ExecutionPlan-Planhashvalue:1376202287-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|195|15405|51(0)|00:00:01|1|TABLEACCESSBYINDEXROWID|T|195|15405|51(0)|00:00:01

3、|*2|INDEXRANGESCAN|T_IND|78|50(0)|00:00:01|-PredicateInformation(identifiedbyoperationid):-2-access(ID=1)现象t表还没有被分析，提示/*+dynamic_sampling(t0)*/*的目的是让CBO无法通过动态采样获取表中的实际数据情况，此时CBO只能根据T表中非常有限的信息（比如表中的extents数量，数据块的数量）来猜测表中的数据。从结果中可以看到CBO猜出表中id=1的有195条，这个数值对于表的总数来说，是一个非常小的值，所以CBO选择了索引而不是全表扫描。而实际情况如下所示：S

4、QLselect*fromtwhereid=12;50819rowsselected.ExecutionPlan-Planhashvalue:1601196873-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|49454|3815K|67(2)|00:00:01|*1|TABLEACCESSFULL|T|49454|3815K|67(2)|00:00:01|-PredicateInformation(identifiedbyoperationid):-1-filter(ID=1)通过动态取样，CBO估算出行数

5、为49454，非常接近于真实50820数目。选择了全表扫描。我们来收集一下统计信息SQLexecdbms_stats.gather_table_stats(user,t,cascade=true);SQLselect*fromtwhereid=1;50819rowsselected.ExecutionPlan-Planhashvalue:1601196873-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|50815|1339K|67(2)|00:00:01|*1|TABLEACCESSFULL|T|50815

6、|1339K|67(2)|00:00:01|-PredicateInformation(identifiedbyoperationid):-1-filter(ID=1)现在扫描过的行数为50815。如果我们更新了所有的id为99看看。SQLupdatetsetid=99;50820rowsupdatedSQLselect*fromtwhereid=99;ExecutionPlan-Planhashvalue:1376202287-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|1|27|2(0)|00:00:0

7、1|1|TABLEACCESSBYINDEXROWID|T|1|27|2(0)|00:00:01|*2|INDEXRANGESCAN|T_IND|1|1(0)|00:00:01|-PredicateInformation(identifiedbyoperationid):-2-access(ID=99)因为没有对表进行分析，所以表中的分析数据还是之前的信息，CBO并不知道。我们可以看出Rows值为1，也就是说CBO人为表T中的ID=99的值只有1条，所有选择仍然是索引。我们收集一把统计信息。SQLexecdbms_stats.gather_table_stats(user,t,cascade=

8、true);PL/SQLproceduresuccessfullycompletedSQLselect*fromtwhereid=99;50820rowsselected.ExecutionPlan-Planhashvalue:1601196873-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|50815|1339K|67(2)|00:00:01|*1|TABLEACCESSFULL|T|50815|1339K|67(2)|00:00:01|-PredicateInformation(identifiedby

9、operationid):-1-filter(ID=99)上面为补充内容，下面正式开始1、sql的执行计划创建测试表SQLcreatetablet1(idint,namevarchar2(1000);TablecreatedSQLcreatetablet2(idint,namevarchar2(1000);TablecreatedSQLcreateindexind_t1ont1(id);IndexcreatedSQLcreateindexind_t2ont2(id);IndexcreatedSQLcreateindexind_t2_nameont2(name);IndexcreatedSQLi

10、nsertintot1selecta.OBJECT_ID,a.OBJECT_NAMEfromall_objectsa;50206rowsinsertedSQLinsertintot2selecta.OBJECT_ID,a.OBJECT_NAMEfromall_objectsawhererownumcommit;CommitcompleteSQLexecdbms_stats.gather_table_stats(user,t1,cascade=true);PL/SQLproceduresuccessfullycompletedSQLexecdbms_stats.gather_table_stat

11、s(user,t2,cascade=true);PL/SQLproceduresuccessfullycompleted2、产生执行计划SQLselect*fromt1,t2wheret1.id=t2.id;20rowsselected.ExecutionPlan-Planhashvalue:828990364-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|20|780|43(0)|00:00:01|1|TABLEACCESSBYINDEXROWID|T1|1|28|2(0)|00:00:01|2|NESTE

12、DLOOPS|20|780|43(0)|00:00:01|3|TABLEACCESSFULL|T2|20|220|3(0)|00:00:01|*4|INDEXRANGESCAN|IND_T1|1|1(0)|00:00:01|-PredicateInformation(identifiedbyoperationid):-4-access(T1.ID=T2.ID)Statistics-1recursivecalls0dbblockgets37consistentgets0physicalreads0redosize1452bytessentviaSQL*Nettoclient503bytesrec

13、eivedviaSQL*Netfromclient3SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)20rowsprocessed看执行计划时，我们首先从缩进最大的行读取，它是最先被执行的步骤。在执行计划中：id=3和id=4是最先被执行的，|3|TABLEACCESSFULL|T2|20|220|3(0)|00:00:01|*4|INDEXRANGESCAN|IND_T1|1|1(0)|00:00:01|两行缩进一样的，最上面的最先被执行，在这里就是id=3|3|TABLEACCESSFULL|T2|20|220|3(0)|00

14、:00:01|选择次之缩进的行数id=2，表连接方式为NESTEDLOOPS。|2|NESTEDLOOPS|20|780|43(0)|00:00:01|然后是id=1，扫描表的方式为TABLEACCESSBYINDEXROWID|1|TABLEACCESSBYINDEXROWID|T1|1|28|2(0)|00:00:01|最后是id=0|0|SELECTSTATEMENT|20|780|43(0)|00:00:01|我们翻译成语言大概如下，从t2表第一行读取，查看每一行是否符合下面条件：T1.ID=T2.ID如果符合就拿出一行来，扫描整个t2表，这个过程就叫NESTEDLOOPS当整个t2表

15、被扫描完之后，会产生一个结果集，这个结果集是IND_T1的一个索引集，然后oracle根据索引键值上的rowid去T1表中找到相应的记录，就是这一步：TABLEACCESSBYINDEXROWID然后将结果返回：SELECTSTATEMENTid列为：id=3-id=4-id=2-id=1-id=0让我们再看一看表中每一行表示什么含义：1）Operation列：当前操作的内容。2）Rows列：就是当前操作的cardinality，Oracle估算当前操作的返回结果集。3）Cost(%CPU):Oracle计算出来的一个数值（代价），用于说明sql执行的代价。4）Time列：Oracle估算当前

16、操作的时间。PredicateInformation(identifiedbyoperationid):-4-access(T1.ID=T2.ID)这里有access和filter区别，access就表示这个谓词的条件的值将会影响数据的访问路径（一般针对索引），filter只起过滤作用。举个例子SQLselect*fromt1wheret1.name=AA;norowsselectedExecutionPlan-Planhashvalue:3617692013-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|2

17、|56|69(2)|00:00:01|*1|TABLEACCESSFULL|T1|2|56|69(2)|00:00:01|-PredicateInformation(identifiedbyoperationid):-1-filter(T1.NAME=AA)懂了吧。下面我们来仔细分析Operation里面的内容a、表访问方式1.FullTableScan(FTS)全表扫描InaFTSoperation,thewholetableisreaduptothehighwatermark(HWM).TheHWMmarksthelastblockinthetablethathaseverhaddataw

18、rittentoit.IfyouhavedeletedalltherowsthenyouwillstillreaduptotheHWM.TruncateresetstheHWMbacktothestartofthetable.FTSusesmultiblocki/otoreadtheblocksfromdisk.-全表扫描模式下会读数据到表的高水位线（HWM即表示表曾经扩展的最后一个数据块），读取速度依赖于Oracle初始化参db_block_multiblock_read_count(我觉得应该这样翻译:FTS扫描会使表使用上升到高水位(HWM),HWM标识了表最后写入数据的块,如果你用DE

19、LETE删除了所有的数据表仍然处于高水位(HWM),只有用TRUNCATE才能使表回归,FTS使用多IO从磁盘读取数据块).QueryPlan-SELECTSTATEMENTCHOOSECost=1*INDEXUNIQUESCANEMP_I1-如果索引里就找到了所要的数据，就不会再去访问表2.IndexLookup索引扫描Thereare5methodsofindexlookup:1）indexuniquescan-索引唯一扫描Methodforlookingupasinglekeyvalueviaauniqueindex.alwaysreturnsasinglevalue,Youmustsu

20、pplyATLEASTtheleadingcolumnoftheindextoaccessdataviatheindex.eg:SQLselectempno,enamefromempwhereempno=10;SQLselectempno,enamefromempwhereempno=10;norowsselectedExecutionPlan-Planhashvalue:2949544139-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|1|20|1(0)|00:00:01|1|TABLEACCESSBYI

21、NDEXROWID|EMP|1|20|1(0)|00:00:01|*2|INDEXUNIQUESCAN|PK_EMP|1|1(0)|00:00:01|-PredicateInformation(identifiedbyoperationid):-2-access(EMPNO=10)Statistics-24recursivecalls0dbblockgets3consistentgets0physicalreads0redosize385bytessentviaSQL*Nettoclient481bytesreceivedviaSQL*Netfromclient1SQL*Netroundtri

22、psto/fromclient0sorts(memory)0sorts(disk)0rowsprocessed2）indexrangescan-索引局部扫描Indexrangescanisamethodforaccessingarangevaluesofaparticularcolumn.ATLEASTtheleadingcolumnoftheindexmustbesuppliedtoaccessdataviatheindex.Canbeusedforrangeoperations(e.g.=selectempnofromempwhereEMPNO=7902;ExecutionPlan-Pla

23、nhashvalue:1567865628-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|2|26|2(0)|00:00:01|*1|INDEXRANGESCAN|PK_EMP|2|26|2(0)|00:00:01|-PredicateInformation(identifiedbyoperationid):-1-access(EMPNO=7902)Note-dynamicsamplingusedforthisstatementStatistics-0recursivecalls0dbblockgets2co

24、nsistentgets0physicalreads0redosize569bytessentviaSQL*Nettoclient492bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)2rowsprocessed3）indexfullscan-索引全局扫描FullindexscansareonlyavailableintheCBOasotherwiseweareunabletodeterminewhetherafullscanwouldbeagoodideaornot.WechooseanindexFullScanwhenwehavestatisticsthatindicatethatitisgoingtobemoreefficientthanaFulltablescanandasort.ForexamplewemaydoaFullindexscanwhenwedoanunboundedscanofanindexandwantthedatatobeorderedintheindexorder.