怎样看懂Oracle的执行计划

1、一、什么是执行计划An explain plan is a representation of the access path that is taken when a queryexecuted within Oracle.二、如何访问数据At the physical level Oracle reads blocks of data. The smallest amount of data reada single Oracle block, the largest is constrained by operating system limits (and multiblocki/o)

2、.LogicallyOraclefindsthedatatoreadbyusingthefollowingmethods:Full TableScan(FTS)-全表扫描Index Lookup (unique&non-unique)-索引扫描（唯一和非唯一Rowid-物理行id三、执行计划层次关系When looking at a plan, the rightmost (ie most inndented) uppermost operation isfirst thing that is executed.ID1、看一个简单的例子： Query PlanSELECT STATEMENT

3、CHOOSE Cost=1234*TABLE ACCESS FULL LARGE :Q65001 ANALYZED-:Q65001表示是并行方式， ANALYZED表示该对象已经分析过了CHOOSECostCBORBO：SELECT STATEMENT CHOOSE SELECT STATEMENT CHOOSE Cost=-CostCBO-CostRBO2、层次的父子关系，看比较复杂的例子：PARENT1*FIRST CHILD*FIRST GRANDCHILD*SECOND CHILDHere the same principles apply, the FIRST GRANDCHILD

4、is the initial operation then the FIRST CHILD followed by the SECOND CHILD and finally the PARENT collates the output.四、例子解说Execution Plan*SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=8Bytes=248)0 *HASH JOIN (Cost=3 Card=8Bytes=248)1 *TABLE ACCESS (FULL) OF DEPT (Cost=1 Card=3Bytes=36)1 *TABLE ACCE

5、SS (FULL) OF EMP (Cost=1 Card=16Bytes=304) IDPID（ID）。A shortened summary of thisis:Execution starts with ID=0: SELECT STATEMENT；but this is dependand on itsobjects；So it executes its first child step: ID=1 PID=0 HASH JOI；but this is dependand its child objects；Soitexecutesitsfirstchildstep:ID=2PID=1

6、TABLEACCESS(FULL)OFDEPTThen the second child step: ID=3 PID=2 TABLE ACCESS (FULL) OF EMP；Rows are returned to the parent step(s) until finished五、表访问方式1、Full Table Scan (FTS)全表扫描InaFTSoperation, thewholetableisreaduptothehigh watermark(HWM)TheHWMmarks the last block in the table that has ever had dat

7、a written to it. If you havedeleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multiblock i/o to read the blocks from disk. - 全表扫描模式下会读数据到表的高水位线（HWM），读取速度Oracledb_block_multiblock_read_countQuery PlanSELECT STATEMENT CHOOSE C

8、ost=1*INDEX UNIQUE SCAN EMP_I1 -如果索引里就找到了所要的数据，就不会再去访问表了2、Index Lookup 索引扫描There are 5 methods of index lookup:index unique scan -索引唯一扫描Methodforlookingupasinglekeyvalueviaauniqueindex.alwaysreturnsasingle value, You must supply AT LEAST the leading column of the index to access datatheindex.eg:SQLe

9、xplain plan forselect empno,ename from emp where empno=10; index range scan -索引局部扫描Index range scan is a method for accessing a range values of a particularcolumn. 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. = explain

10、 plan for select mgr from emp where mgr = 5; index full scan -索引全局扫描FullindexscansareonlyavailableintheCBOasotherwiseweareunabletodetermine whether a full scan would be a good idea or not. We choose an index Full Scanwhen we have statistics that indicate that it is going to be more efficient than at

11、ablescanandasort.ForexamplewemaydoaFullindexscanwhenwedoanunbounded scan of an index and want the data to be ordered in the index order.eg:SQL explain plan for select empno,ename from big_emp order by empno,ename; index fast full scan -索引快速全局扫描，不带order by 情况下常发生Scans all the block in the index, Rows

12、 are in sorted order, Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO, hinted using INDEX_FFS hint,usesmultiblocki/o,canbeexecutedinparallel,canbeusedtoaccesssecond column of concatenated indexes. This is because we are selecting all of theindex. eg:SQL explain plan for select empno,e

13、name from big_emp;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 column(s) during the search.eg:SQL create index i_emp on emp(empno, ename);SQL select /*+ index_ss(emp i_emp)*/ job fro

14、m emp whereename=SMITH; 3.Rowid物理IDThis is the quickest access method available.Oracle retrieves the specified blockextracts the rows it is interested in.Rowid六、表连接方式1、Sort Merge Join (SMJ)由于sort是非常耗资源的，所以这种连接方式要避免；Rows are produced by Row Source 1 and are then sorted Rows from Row Source 2 arethen

15、produced and sorted by the same sort key as Row Source 1. Row Source 1 and 2 are NOT accessed concurrently.SQL explain plan forselect /*+ ordered */ e.deptno,d.deptno from emp e,dept dwhere e.deptno = d.deptno order by e.deptno,d.deptno;Query PlanSELECT STATEMENT CHOOSE Cost=17*MERGE JOIN*SORT JOIN*

16、TABLE ACCESS FULL EMP ANALYZED*SORT JOIN*TABLE ACCESS FULL DEPT ANALYZEDSorting is an expensive operation, especially with large tables. Because of this, SMJ is often not a particularly efficient join method.NestedLoops(NL)-比较高效的一种连接方式Fetches the first batch of rows from row source 1, Then we probe

17、row source 2 onceeach row returned from row source 1.For nested loops to be efficient it is important that the first row source returnsfew rows as possible as this directly controls the number of probes of the secondsource.Alsoithelpsiftheaccessmethodforrowsource2isefficientasthis operation is being

18、 repeated once for every row returned by row source 1.SQL explain plan for select a.dname,b.sql from dept a,emp bwhere a.deptno = b.deptno; Query PlanSELECT STATEMENT CHOOSE Cost=5*NESTED LOOPS*TABLE ACCESS FULL DEPT ANALYZED*TABLE ACCESS FULL EMP ANALYZEDHashJoin-最为高效的一种连接方式Newjointypeintroducedin7

19、.3,MoreefficientintheorythanNL&SMJ,Onlyaccessible via the Smallest row source is chosen and used to build a hash table and abitmap The second row source is hashed and checked against the hash table looking for joins. The bitmap is used as a quick lookup to check if rows are in the hash table and are

20、 especially useful when the hash table is too large to fit inmemory.SQL explain plan forselect /*+ use_hash(emp) */ empno from emp,deptwhere emp.deptno = dept.deptno;Query PlanSELECT STATEMENT CHOOSE Cost=3*HASH JOIN*TABLE ACCESS FULL DEPT*TABLE ACCESS FULL EMPHash joins are enabled by the parameter

21、 HASH_JOIN_ENABLED=TRUE in the init.orasession. TRUE is the default in 7.3.4、Cartesian Product卡迪尔积，不算真正的连接方式肯定写的有问题ACartesianProductisdonewheretheyarenojoinconditionsbetween2rowsourcesand thereisnoalternativemethodofaccessingthedata.Notreallyajoinassuchasthere isnojoin!Typicallythisiscausedbyacoding

22、mistakewhereajoinhasbeenleftout. Itcanbeusefulinsomecircumstances-Starjoinsusescartesianproducts.Noticethere is no join between the 2tables:SQL explain plan forselect emp.deptno,dept,deptno from emp,deptQuery PlanSLECT STATEMENT CHOOSE Cost=5*MERGE JOIN CARTESIAN*TABLE ACCESS FULL DEPT*SORT JOIN*TAB

23、LE ACCESS FULL EMPThe CARTESIAN keyword indicate that we are doing a cartesian product.七、运算符1、sort排序，很消耗资源There are a number of different operations that promote sorts: order by clausesgroup bysort merge join2、filter过滤，如not in、min函数等容易产生Hasanumberofdifferentmeanings,usedtoindicatepartitioneliminatio

24、n,mayindicateanactualfilterstepwhereonerowsourceisfiltering,another,functions such as min may introduce filter steps into query plans.3、view -视图，大都由内联视图产生When a view cannot be merged into the main query you will often see a projection view operation. This indicates that the view will be selected fro

25、m directly as opposed to being broken down into joins on the base tables. A number ofconstructs make a view non mergeable. Inline views are also non mergeable.eg:SQL explain plan for select ename,totfrom emp,(select empno,sum(empno) tot from big_emp group by empno) tmp where emp.empno = tmp.empno;Qu

26、ery PlanSELECT STATEMENT CHOOSE*HASH JOIN*TABLE ACCESS FULL EMP ANALYZED*VIEW*SORT GROUP BY*INDEX FULL SCAN BE_IX4、partition view分区视图Partition views are a legacy technology that were superceded by the option.Thissectionofthearticleisprovidedasreferenceforsuchlegacysystems.=oracle数据库方面的性能。常用方法有以下几种：一

27、、通过 PL/SQL Dev 工具1File-New-Explain Plan Window，sql计划结果。其中，Costcpun%，CardinalityRows。2、先执行 EXPLAIN PLAN FOR select * from tableA where select * from table(DBMS_XPLAN.DISPLAYoracle11注意：PL/SQLDevCommandwindowsetautotranceonsqlplus二、通过 sqlplus1、一般情况都是本机链接远程服务器，所以命令如下： sqlplus user/pwdserviceNameserviceN

28、ametnsnames.ora2、执行 set autotrace on，然后执行 sql 语句，会列出以下信息：。（省略一些信息） 统计信息1 recursive calls （归调用次数）0 db block gets2 consistent gets0 physical reads （SQL的数据块个数）0 redo size (重做数执行 SQL 的过程中，产生的重做日志的大小)358 bytes sent via SQL*Net to client366 bytes received via SQL*Net from client1 SQL*Net roundtrips to/fro

29、m client0 sorts (memory)在内存中发生的排序sorts (disk)在硬盘中发生的排序rows processedPL/SQL Dev息是更详细的。判断 SQL 效率高低不关通过时间来衡量，还应该通过执行 SQL 执行状态里面的逻辑读的数量逻辑读=（db block gets+ consistent gets）1、SET AUTOTRACE ON EXPLAIN（set autot on exp)SQLPLUSSQLEXP(LAIN)是只显示执行计划而不显示统计信息.。2 、 SQLexplain plan for select ; SQLselect * from ta

30、ble(dbms_xplan.display);set autotrace on explainautotraceoff;”set autotrace on，除了会显示执行计划之外，还会显示一些有用的统计信息。执行 EXPLAIN PLAN FOR 可以只显示执行计划,然后执行如下查询SQL select * from table(dbms_xplan.display);如：SQL explain plan for select * from emp where deptno=20; Explained.SQL select * from table(dbms_xplan.display);

31、PLAN_TABLE_OUTPUTPlan hash value: 3956160932| Id| Operation| Name | Rows| Bytes | Cost Time|0 | SELECT STATEMENT|5 |150 |3(0)|00:00:01 |*1 |TABLE ACCESS FULL| EMP|5 |150 |300:00:01 |Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT1 - filter(DEPTNO=20)13 rows selected.3、SQLexec db

32、ms_stats.delete_table_stats(USER,表);(删除表的统计信息）SQLexec dbms_stats.gather_table_stats(USER,表,METHOD_OPT=FOR ALL COLUMNS SIZE 100)（收集表的统计信息）4、AUTOTRACE 的几个常用选项setsetautotraceautotraceononsetsetautotraceautotraceononexplain autotracestatistics - 只显示执行统计信息setautotraceon 包含执行计划和统计信息set autotrace traceonly

33、set autotrace on，(1). set autotrace on explain; -只显示执行计划SQL set autotrace on explain; SQLselect count(*) from COUNT(*)31820Execution Plan0SELECTSTATEMENT Optimizer=CHOOSE10SORT(AGGREGATE)21VIEW OF DBA_OBJECTS32UNION-ALL43FILTER54TABLE ACCESS (BYINDEX ROWID) OF OBJ$65NESTED LOOPS76TABLE ACCESS(FULL)

34、OF USER$86INDEX (RANGESCAN) OF I_OBJ2 (UNIQUE)94TABLE ACCESS (BYINDEX ROWID) OF IND$109INDEX (UNIQUE SCAN) OF I_IND1 (UNIQUE)113NESTED LOOPS1211TABLE ACCESS (FULL) OF USER$1311INDEX (RANGE SCAN) OF I_LINK1 (NON-UNIQUE)(2).setautotrace on statistics;-只显示统计信息SQLsetautotrace on statistics;SQL select co

35、unt(*) from dba_objects;COUNT(*)31820Statistics0 recursive calls0 db block gets 25754 consistent 0 physical reads0 redo size383 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)sorts (disk)rows processed(3).set autotrace tra

36、ceonly;-同 set autotrace on只是不显示查询输出SQLset autotrace traceonly;SQLselect count(*) from dba_objects;Execution Plan0SELECT STATEMENT Optimizer=CHOOSE10SORT (AGGREGATE)21VIEW OF DBA_OBJECTS32UNION-ALL43FILTER54TABLE ACCESS (BY INDEX ROWID) OF OBJ$65NESTED LOOPS76TABLE ACCESS (FULL) OF USER$86INDEX (RANG

37、E SCAN) OF I_OBJ2 (UNIQUE)94TABLE ACCESS (BY INDEX ROWID) OF IND$109INDEX (UNIQUE SCAN) OF I_IND1 (UNIQUE)113NESTED LOOPS1211TABLE ACCESS (FULL) OF USER$1311INDEX (RANGE SCAN) OF I_LINK1 (NON-UNIQUE)Statistics0 recursive calls0 db block gets 25754 consistent 0 physical reads0 redo size383 bytes sent

38、 via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)sorts (disk)rows processed(4).set autotrace traceonly explain;-比较实用的选项，只显示执行计划， 但是与 set autotrace on explain;相比不会执行语句，对于仅仅查看大表的Explain Plan 非常管用。SQL set autotrace traceonly explain; SQL

39、 select * from dba_objects;已用时间: 00: 00: 00.00Execution Plan0SELECT STATEMENT Optimizer=CHOOSE10VIEW OF DBA_OBJECTS21UNION-ALL32FILTER43TABLE ACCESS (BY INDEX ROWID) OF OBJ$54NESTED LOOPS65TABLE ACCESS (FULL) OF USER$75INDEX (RANGE SCAN) OF I_OBJ2 (UNIQUE)83TABLE ACCESS (BY INDEX ROWID) OF IND$98IND

40、EX (UNIQUE SCAN) OF I_IND1 (UNIQUE)102TABLE ACCESS (BYINDEX ROWID) OF LINK$1110NESTED LOOPS1211TABLE ACCESS(FULL) OF USER$1311INDEX (RANGESCAN) OF I_LINK1 (NON-UNIQUE)5、analyzeanalyze table hr.employees compute(estimate) statistics;(compute每一行数据的统计信息，比较耗时；estimateselect t.owner,t.table_name,t.tables

41、pace_name,t.blocks,t.empty_blocks,t.avg_ spacefrom dba_tables t where t.owner=HR;如何生成 explain plan?解答:运行 utlxplan.sql. 建立 plan 表SQLexplain plan set statement_id = tst1 plan_table for sql statement运行 utlxplp.sql 或 utlxpls.sql 察看 explain plan（select * from table(dbms_xplan.display();）EXPLAINPLAN 是一个很好

42、的分析SQLSQLORACLE式扫描表(索引扫描或全表扫描)以及使用到的索引名称.你需要按照从里到外,从上到下的次序解读分析的结果. EXPLAIN PLAN分析的结果是用缩进的格式排列的, 最内部的操作将被最先解读, 如果两个操作处于同一层中,带有最小操作号的将被首先执行.NESTED LOOP 是少数不按照上述规则处理的操作 , 正确的执行路径是检查对NESTED LOOP 提供数据的操作,其中操作号最小的将被最先处理.译者按:SQLPLUSSET TRACE举例:SQL listSELECT *FROM dept, emp3* WHERE emp.deptno = dept.deptno

43、SQLset autotrace traceonly /*traceonly可以不显示执行结果*/SQL14 rows/selected.Execution Plan0 SELECT STATEMENT Optimizer=CHOOSE1 0NESTED LOOPS2 1TABLE ACCESS (FULL) OFEMP3 1TABLE ACCESS (BY INDEXROWID) OFDEPT4 3INDEX (UNIQUE SCAN) OFPK_DEPT(UNIQUE)Statistics0 recursive calls2 db block gets30 consistent gets0

44、 physical reads0 redo size2598 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)14 rows processed通过以上分析,可以得出实际的执行步骤是:1. TABLEACCESS (FULL) OFEMP2. INDEX(UNIQUE SCAN) OFPK_DEPT (UNIQUE)3. TABLEACCESS (BY INDEXRO

45、WID) OF DEPTNESTED LOOPS (JOINING 1 AND 3)注: 目前许多第三方的工具如 TOAD 和 ORACLE 本身提供的工具如 OMS 的 AnalyzeEXPLAINPLAN可以选用它们.sql看该SQL语句执行的效率如何,下面就简单的介绍一下执行计划的使用ExplainOracleRDBMSSQLOracleSQLExplainSQLAccessPath)。从而使我们选择最优的查询方式达到最大的优化效果。安装要使用执行计划首先需要执行相应的脚本。ExplainExplain_planOracleSQL例如：ORA_RDBMS: XPLAINPL.SQL (V

46、MS)$ORACLE_HOME/rdbms/admin/utlxplan.sql (UNIX)该脚本后会生成一个表这个程序会创建一个名为 plan_table 的表，表结构如下：我们简单的介绍一下主要的字段含义： 字段名 字段类型 含义STATEMENT_ID VARCHAR2(30) explain PLANEXPLAN PLANSET STATEMENT_IDNULL。REMARKS VARCHAR2(80) 与被解释规划的各步骤相关联的注释最长可达 80 字节OPERATION VARCHAR2(30) 各步骤所执行内部操作的名称在某条语句所产生的第一行中该列的可能取值如下 DELETE

47、 STATEMENT INSERT STATEMENT SELECT STATEMENT UPDATE STATEMENTOPTIONS VARCHAR2(30) 对 OPERATION 列中所描述操作的变种OBJECT_NODE VARCHAR2(128) 用于访问对象的数据库链接 database link 的名称对于使用并行执行的本地查询该列能够描述操作中输出的次序OBJECT_OWNERVARCHAR2(30)schema的名称OBJECT_NAME VARCHAR2(30) 表或索引的名称OBJECT_INSTANCEINTEGERoriginal的相应次序编号就原始的语句文本而论其

48、处理顺序为自左至右自外向内景象扩viewOBJECT_TYPE VARCHAR2(30) 用于提供对象描述性信息的修饰符例如索引的NON-UNIQUEOPTIMIZER VARCHAR2(255) 当前优化程序的模式ID INTEGER 分配给执行规划各步骤的编号PARENT_ID INTEGERIDPOSITION INTEGERPARENT_ID 的步骤其相应的处理次序COST INTEGER 根据优化程序的基于开销的方法所估计出的操作开销值对于使用基于规则方法的语句该列为空该列值没有特定的测量单位它只是一个用于比较执行规划开销大小的权重值CARDINALITY INTEGER 根据基于开

49、销的方法对操作所访问行数的估计值BYTES INTEGER 根据基于开销的方法对操作所访问字节的估计使用常规使用语法：explain PLAN SET STATEMENT_ID = INTO FOR 其中：STATEMENT_IDPLAN其它执行计划区别开来。TABLE_NAMEplanSQL_STATEMENTSQL如：SQL explain plan set statement_id=test1 forSELECT a.soctermbegin,a.soctermend,a.dealserialno,a.levydataid,a.dealtotal,e.categoryitemcode,r

50、ow_number() over(PARTITION BY a.levydataid ORDER BY 1) AS theRowFROM tb_soc_packdealdata a,tb_Lvy_TaxDataBillMap c,Tb_lvy_BillData d,tb_soc_levydetaildata eWHERE a.levydataid = c.datafrompointer(+)AND c.billdataid = d.billdataid(+)AND a.levydataid = e.levydataidAND a.packdealstatuscode = 10AND (a.da

51、tastatus 9 OR a.datastatus is NULL)AND (d.billstatus IS NULL OR(d.billstatus 2 AND d.billstatus 8)AND a.Insurcode = 6010952 21 ;Explained执行下面语句就可以查看该语句执行的执行计划:SQLSELECTA.OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,ID,PARENT_IDFROM PLAN_TABLE aWHERE STATEMENT_ID=test1ORDER BY Id;OPERATION OPTIONS OBJEC

52、T_NAME OBJECT_TYPEID PARENT_IDSELECT STATEMENT 0WINDOW SORT 1 0FILTER 2 1NESTED LOOPS OUTER 3 2NESTED LOOPS OUTER 4 3NESTED LOOPS 5 4TABLE ACCESS FULL TB_SOC_PACKDEALDATA 6 5TABLE ACCESS BY INDEX ROWID TB_SOC_LEVYDETAILDATA 7 5 INDEX RANGE SCAN IND_DATAID_LEVSOC NON-UNIQUE 8 7 TABLE ACCESS BY INDEX ROWID TB_LVY_TAXDATABILLMAP 9 4INDEX RANGE SCAN TBLVYTAXDATABIL_DATAFROMPOINTE NON-UNIQUE 10 TABLE ACCESS BY INDEX ROWID TB_LVY_BILLDATA 11 3INDEX UNIQUE SCAN TBLVYBILL