Oracle执行计划讲解.doc

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

1、【精品文档】如有侵权，请联系网站删除，仅供学习与交流Oracle执行计划讲解.精品文档.Oracle执行计划 讲解看懂Oracle执行计划是优化的第一步，让我们从下面的例子开始吧。下面为补充内容1、创建测试表SQLcreatetabletasselect1id,object_namefromdba_objects;TablecreatedSQLupdatetsetid=99whererownum=1;1rowupdatedSQLcommit;CommitcompleteSQLcreateindext_indont(id);Indexcreated oracle优化器：RBO和CBO两种， 从o

2、racle10g开始优化器已经抛弃了RBO，下面的列子说明CBO大概是怎样的SQLselect/*+dynamic_sampling(t0)*/*fromtwhereid=1;50819rowsselected.ExecutionPlanPlanhashvalue: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|*2|INDEXRANGESC

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

4、whereid=12;50819rowsselected.ExecutionPlanPlanhashvalue: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估算出行数为49454，非常接近于真实50820

5、数目。选择了全表扫描。 我们来收集一下统计信息SQLexecdbms_stats.gather_table_stats(user,t,cascade=true);SQLselect*fromtwhereid=1;50819rowsselected.ExecutionPlanPlanhashvalue: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|

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

7、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=true);PL/SQLproceduresuccess

8、fullycompletedSQLselect*fromtwhereid=99;50820rowsselected.ExecutionPlanPlanhashvalue: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(identifiedbyoperationid):1-filter(ID=99)上面为补充

9、内容，下面正式开始1、 sql的执行计划创建测试表SQLcreatetablet1(idint,namevarchar2(1000);TablecreatedSQLcreatetablet2(idint,namevarchar2(1000);TablecreatedSQLcreateindexind_t1ont1(id);IndexcreatedSQLcreateindexind_t2ont2(id);IndexcreatedSQLcreateindexind_t2_nameont2(name);IndexcreatedSQLinsertintot1selecta.OBJECT_ID,a.OB

10、JECT_NAMEfromall_objectsa;50206rowsinsertedSQLinsertintot2selecta.OBJECT_ID,a.OBJECT_NAMEfromall_objectsawhererownumcommit;CommitcompleteSQLexecdbms_stats.gather_table_stats(user,t1,cascade=true);PL/SQLproceduresuccessfullycompletedSQLexecdbms_stats.gather_table_stats(user,t2,cascade=true);PL/SQLpro

11、ceduresuccessfullycompleted2、产生执行计划SQLselect*fromt1,t2wheret1.id=t2.id;20rowsselected.ExecutionPlanPlanhashvalue: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|NESTEDLOOPS|20|780|43(0)|00:00:01|3|TABLEA

12、CCESSFULL|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)Statistics1recursivecalls0dbblockgets37consistentgets0physicalreads0redosize1452bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient3SQL*Netroundtr

13、ipsto/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:00:01| 选择次之缩进的行数id=2，表连接方式为NESTED LOO

14、PS。|2|NESTEDLOOPS|20|780|43(0)|00:00:01| 然后是id=1，扫描表的方式为TABLE ACCESS BY INDEX ROWID|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表，这个过程就叫NESTED LOOPS 当整个t2表被扫描完之后，会产生一个结果集，这个结果集是IND_

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

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

17、)|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).TheHWMmarksthelastblockinthetablethathaseverhaddatawrittentoit

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

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

20、theleadingcolumnoftheindextoaccessdataviatheindex.eg:SQLselectempno,enamefromempwhereempno=10;SQLselectempno,enamefromempwhereempno=10;norowsselectedExecutionPlanPlanhashvalue:2949544139|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|0|SELECTSTATEMENT|1|20|1(0)|00:00:01|1|TABLEACCESSBYINDEXROWID|EMP|1

21、|20|1(0)|00:00:01|*2|INDEXUNIQUESCAN|PK_EMP|1|1(0)|00:00:01|PredicateInformation(identifiedbyoperationid):2-access(EMPNO=10)Statistics24recursivecalls0dbblockgets3consistentgets0physicalreads0redosize385bytessentviaSQL*Nettoclient481bytesreceivedviaSQL*Netfromclient1SQL*Netroundtripsto/fromclient0so

22、rts(memory)0sorts(disk)0rowsprocessed2）index range scan -索引局部扫描Index range scan is a method for accessing a range values of a particular column. AT LEAST the leading column of the index must be supplied to access data via the index. Can be used for range operations (e.g. = selectempnofromempwhereEMP

23、NO=7902;ExecutionPlanPlanhashvalue: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-dynamicsamplingusedforthisstatementStatistics0recursiveca

24、lls0dbblockgets2consistentgets0physicalreads0redosize569bytessentviaSQL*Nettoclient492bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)2rowsprocessed3）index full scan -索引全局扫描Full index scans are only available in the CBO as otherwise we are unable to determin

25、e whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be orde

26、red in the index order.SQLselectempnofromemporderbyempno;14rowsselected.ExecutionPlanPlanhashvalue:179099197|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|0|SELECTSTATEMENT|14|182|2(0)|00:00:01|1|INDEXFULLSCAN|PK_EMP|14|182|2(0)|00:00:01|Note-dynamicsamplingusedforthisstatementStatistics4recursivecal

27、ls0dbblockgets11consistentgets0physicalreads0redosize676bytessentviaSQL*Nettoclient492bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)14rowsprocessed4）index fast full scan -索引快速全局扫描，不带order by情况下常发生Scans all the block in the index, Rows are not returned in s

28、orted order, Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock i/o, can be executed in parallel, can be used to access second column of concatenated indexes. This is because we are selecting all of the index.SQLselectempnofromemp;14ro

29、wsselected.ExecutionPlanPlanhashvalue:366039554|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|0|SELECTSTATEMENT|14|182|2(0)|00:00:01|1|INDEXFASTFULLSCAN|PK_EMP|14|182|2(0)|00:00:01|Note-dynamicsamplingusedforthisstatementStatistics4recursivecalls0dbblockgets13consistentgets0physicalreads0redosize676b

30、ytessentviaSQL*Nettoclient492bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)14rowsprocessed5）index skip scan -索引跳跃扫描，where条件列是非索引的前导列情况下常发生Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first co

31、lumn(s) during the search.SQLcreateindexi_emponemp(empno,ename);Indexcreated.SQLselect/*+index_ss(empi_emp)*/jobfromempwhereename=SMITH;ExecutionPlanPlanhashvalue:98078853|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|0|SELECTSTATEMENT|1|13|5(0)|00:00:01|1|TABLEACCESSBYINDEXROWID|EMP|1|13|5(0)|00:00:

32、01|*2|INDEXSKIPSCAN|I_EMP|1|4(0)|00:00:01|PredicateInformation(identifiedbyoperationid):2-access(ENAME=SMITH)filter(ENAME=SMITH)Note-dynamicsamplingusedforthisstatementStatistics5recursivecalls0dbblockgets11consistentgets0physicalreads0redosize513bytessentviaSQL*Nettoclient492bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessed3.Rowid 物理ID扫描This is the quickest access method availa