怎样看懂Oracle的执行计划

1、、什么是执行计划An expla in pla n is a represe ntati on of the access path that is take n whe n a query is executed withi n Oracle.二、如何访问数据At the physical level Oracle reads blocks of data. The smallest amount of data read isa single Oracle block, the largest is constrained by operating system limits (andmu

2、ltiblock i/o). Logically Oracle finds the data to read by using the followingmethods:Full Table Scan (FTS)-全表扫描Index Lookup (unique & non-unique)-索引扫描(唯一和非唯一)Rowid-物理行id三、执行计划层次关系When looking at a plan, the rightmost (ie most inndented) uppermost operation is thefirst thi ng that is executed.-采用

3、最右最上最先执行的原则看层次关系，在同一级如果某个动作没有子ID就最先执行1、看一个简单的例子：Query Pla nSELECT STATEMENT CHOOSE Cost=1234*TABLE ACCESS FULL LARGE :Q65001 ANALYZED -:Q65001 表示是并行方式,ANALYZED表示该对象已经分析过了优化模式是 CHOOS的情况下，看 Cost参数是否有值来决定采用CBO还是RBOSELECT STATEMENT CHOOSE Cost=1234 -Cost 有值，采用 CBOSELECT STATEMENT CHOOSE Cost=-Cost 为空，采用

4、 RBO2、层次的父子关系，看比较复杂的例子：PARENT1*FIRST CHILD*FIRST GRANDCHILD*SECOND CHILD Here the same principles apply, the FIRST GRANDCHILD is the initial operation then the FIRST CHILD followed by the SECOND CHILD and fin ally the PARENT collates the output.四、例子解说Executi on Pla n0 *SELECT STATEMENT ptimizer=CHOO

5、SE (Cost=3 Card=8 Bytes=248)1 0 *HASH JOIN (Cost=3 Card=8 Bytes=248)2 1 *TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=3 Bytes=36)3 1 *TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=304)左侧的两排数据，前面的是序列号ID，后面的是对应的PID (父ID )。A shorte ned summary of this is:Execution starts with ID=0: S

6、ELECT STATEMENT ； but this is dependand on it's childobjects ；So it executes its first child step: ID=1 PID=0 HASH JOIN； but this is dependand onit's child objects ；So it executes its first child step: ID=2 PID=1 TABLE ACCESS (FULL) OF 'DEPT'；The n the seco nd child step: ID=3 PID=2

7、TABLE ACCESS (FULL) OF 'EMP'；Rows are retur ned to the pare nt step(s) un til fini shed五、表访问方式1、Full Table Scan (FTS)全表扫描In a FTS operation,the whole table is read up to the high water mark (HWM) The HWMnarksthe last block in the table that has ever had data writte n to it. If you have delet

8、edall the rows then you will still read up to the HWM. Trun cate resets the HWM back tothe start of the table. FTS uses multiblock i/o to read the blocks from disk.-全表扫描模式下会读数据到表的高水位线(HWM即表示表曾经扩展的最后一个数据块)，读取速度依赖于Oracle初始化参数db_block_multiblock_read_cou ntQuery PlanSELECT STATEMENT CHOOSE Cost=1*INDEX

9、 UNIQUE SCAN EMP_I1 -如果索引里就找到了所要的数据，就不会再去访问表了2、Index Lookup 索引扫描There are 5 methods of in dex lookup:in dex uni que sca n -索弓丨唯一扫描Method for looking up a single key value via a unique index. always returns a single value, You must supply AT LEAST the leading column of the index to access data via th

10、e in dex.eg:SQL：expla in pla n forselect emp no,en ame from emp where emp no=10;index range sca n -索引局部扫描In dex range sca n is a method for access ing a range values of a particular colu mn.AT LEAST the leading column of the index must be supplied to access data via thein dex. Can be used for range

11、operati ons (e.g. > < <> >= <= betwee n).eg:SQL> explai n pla n for select mgr from emp where mgr = 5;in dex full sca n -索引全局扫描Full index scans are only available in the CBQs otherwise we are unable to determine whether a full sca n would be a good idea or not. We choose an in d

12、ex Full Scan whe nwe have statistics that indicate that it is going to be more efficient than a Fulltable scan and a sort. For example we may do a Full index scan when we do an unbounded sca n of an in dex and want the data to be ordered in the in dex order.eg:SQL> expla in pla n for select emp n

13、o,en ame from big_emp order by emp no,en ame;index fast full sca n -索引快速全局扫描，不带order by 情况下常发生Scans all the blockin the in dex, Rows are notretur ned in sorted order,In troducedin 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock i/o, can be execut

14、ed in parallel, can be used to access second colum n of con cate nated in dexes. This is because we are selecti ng all of the in dex.eg:SQL> explai n pla n for select emp no,en ame from big_emp;index skip sean -索引跳跃扫描，where条件列是非索引的前导列情况下常发生Index skip sean finds rows even if the eolumn is not the

15、leading eolumn of aeon eate nated in dex. It skips the first eolu mn(s) duri ng the search.eg：SQL> ereate in dex i_emp on emp(emp no, en ame);SQL> seleet /有三种连接方式：1、Sort Merge Join (SMJ)- 由于sort是非常耗资源的，所以这种连接方式要避免;Rows are produeed by Row Souree 1 and are then sorted Rows from Row Souree 2 are

16、 then produeed and sorted by the same sort key as Row Souree 1. Row Souree 1 and 2 are NOT aeeessed eon eurre ntly.SQL> expla in pla n forseleet /*+ ordered */ e.dept no ,d.dept nofrom emp e,dept dwhere e.dept no = d.dept noorder by e.dept no ,d.dept no;Query Pla nSELECT STATEMENT CHOOSE Cost=17

17、MERGE JOIN*SORT JOIN *TABLE ACCESS FULL EMP ANALYZED SORT JOIN*TABLE ACCESS FULL DEPT ANALYZEDSort ing is an expe nsive operati on, espeeially with large tables. Beeause of this,SMJ is ofte n not a partieularly effieie nt join method.+ in dex_ss(emp i_emp)*/ job from emp where en ame='SMITH'

18、3. Rowid物理ID扫描This is the quiekest aeeess method available.Oracle retrieves the speeified bloek andextraets the rows it is interested in. -Rowid 扫描是最快的访问数据方式六、表连接方式2. Nested Loops (NL)-比较高效的一种连接方式Fetches the first batch of rows from row source 1, Then we probe row source 2 once for each row retur ne

19、d from row source 1.For nested loops to be efficient it is important that the first row source returns asfew rows as possible as this directly con trols the n umber of probes of the sec ond rowsource. Also it helps if the access method for row source 2 is efficientas thisis being repeated once for e

20、very row retur ned by row source 1.SQL> explai n pla n forselect a.d name,b.sqlfrom dept a,emp bwhere a.dept no = b.dept no;Query Pla nSELECT STATEMENT CHOOSE Cost=5*NESTED LOOPS卄TABLE ACCESS FULL DEPT ANALYZED卄TABLE ACCESS FULL EMP ANALYZED3. Hash Join -最为高效的一种连接方式New join type introduced in 7.3

21、, More efficient in theory than NL & SMJ,0nly via the CBO . Smallest row source is chosen and used to build a hash table and a bitmap The sec ond row source is hashed and checked aga inst the hash table look ing for joins.The bitmap is used as a quick lookup to check if rows are in the hash tabl

22、e and are especially useful when the hash table is too large to fit in memory.SQL> explai n pla n forselect /*+ use_hash(emp) */ emp nofrom emp,deptwhere emp.dept no = dept.dept no;Query Pla noperati onaccessibleSELECT STATEMENT CHOOSE Cost=3*HASH JOIN卄table ACCESS FULL DEPT卄table access full emp

23、Hash joi ns are en abled by the parameter HASH_JOIN_ENABLED=TRUE in the in it.ora orsessi on. TRUE is the default in 7.3.4、Cartesian Product -卡迪尔积，不算真正的连接方式，sql肯定写的有问题A CartesianProduct is done where they are no join conditionsbetween 2 row sources andthere is no alter nativemethod of access ing the

24、 data. Not really a joi n as such as thereis no join! Typically this is caused by a coding mistake where a join has been left out. It can be useful in some circumstances- Star joins uses cartesianproducts.Notice thatthere is no join between the 2 tables:SQL> explai n pla n forselect emp.dept no ,

25、dept,dept nofrom emp,deptQuery Pla nSLECT STATEMENT CHOOSE Cost=5*MERGE JOIN CARTESIAN卄table ACCESS FULL DEPT*SORT JOIN*TABLE ACCESS FULL EMPThe CARTESIAN keyword in dicate that we are doing a cartesia n product.七、运算符1、sort - 排序，很消耗资源There are a n umber of differe nt operati ons that promote sorts:o

26、rder by clausesgroup bysort merge join2、 filter - 过滤，如not in 、min函数等容易产生Has a number of differentmeanings, used to indicatepartitionelimination, may alsoindicate an actual filterstep where one row source is filtering, another, functionssuch as min may in troduce filter steps into query pla ns.3、view

27、 - 视图，大都由内联视图产生When a view cannot be merged into the main query you will often see a projectionview operation. This indicates that the 'view' will be selected from directly asopposed to being broke n dow n into joins on the base tables. A n umber of con structs make a view non mergeable. In

28、li ne views are also non mergeable.eg:SQL> explai n pla n forselect en ame,totfrom emp,(select emp no ,sum(emp no) tot from big_emp group by emp no) tmpwhere emp.emp no = tmp.emp no;Query Pla nSELECT STATEMENT CHOOSE*HASH JOIN*TABLE ACCESS FULL EMP ANALYZED*VIEW*SORT GROUP BY*INDEX FULL SCAN BE_I

29、X4、partition view -分区视图Partiti on views are a legacy tech no logy that were superceded by the partiti oningoption. This section of the article is provided as reference for such legacy systems.= =日常开发活动中，有时候需要对oracle执行计划进行监控，以此来调优程序和数据库方面的性能。常用方法有以下几种:、通过PL/SQL Dev工具1 、直接 File->New->Explain Pla

30、n Window ，在窗口 中执行 sql 可以查看计划结果。其中，Cost表示cpu的消耗，单位为n% Cardinality表示执行的行数，等价Rows2 、先执行 EXPLAIN PLAN FOR select * from tableA where paraA=1 ， 再 select * from table（DBMS_XPLAN.DISPLAY） 便可以看到 oracle 的执行计划 了，看到的结果和1中的一样，所以使用工具的时候推荐使用 1方法。注意：PL/SQL Dev工具的 CommanWindow 中不支持 set autotranee on 的命令。 还有使用工具方法查看

31、计划看到的信息不全，有些时候我们需要 sqlplus的支 持。、通过 sqlplus1 、一般情况都是本机链接远程服务器，所以命令如下：sqlplususer/pwdserviceName此处的serviceName为tnsnames.ora中定义的命名空间。2 、执行set autotrace on ，然后执行sql语句，会列出以下信息: 。（省略一些信息） 统计信息1 recursive calls0 db block gets2 con siste nt gets0 physical reads 的数据块个数)0 redo size ( 小)（归调用次数）（物理读一一执行SQL的过程中，

32、从硬盘上读取重做数执行SQL的过程中，产生的重做日志的大358 bytes sen t via SQL*Net to clie nt366 bytes received via SQL*Net from clie nt1 SQL*Net ro un dtrips to/from clie nt0 sorts (memory)在内存中发生的排序0 sorts (disk)在硬盘中发生的排序1 rows processed省略部分信息和通过PL/SQL Dev工具查看执行计划的信息一样，下面的统计信 息是更详细的。判断SQL效率高低不关通过时间来衡量，还应该通过执行SQL执行状态里面的逻辑读的数量

33、逻辑读 =(db block gets+ consistent gets)1、SET AUTOTRACE ON EXPLAIN( set autot on exp)SQLPLU的命令，在执行SQL语句的同时显示执行计划，设置 EXP(LAIN)的目的 是只显示执行计划而不显示统计信息 . 。2、SQL>explain plan for select;SQL>select * from table(dbms_xplan.display);执行了 set autotrace on explain 语句之后，接下来的查询、插入、更新、删 除语句就会显示执行计划，直到执行“ set aut

34、otrace off; ”语句。如果是设置 了 set autotrace on ，除了会显示执行计划之外，还会显示一些有用的统计信 息。执行EXPLAIN PLAN FOF可以只显示执行计划，然后执行如下查询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);PLAN_TABLE_OUTPUTPl

35、an hash value: 3956160932| Id | Operation | Name | Fows | Bytes | Cost (%CPU)| Time | 0 | SELECT STATEMENT |5 | 150 |3 (0)|00:00:01 |* 1 | TABLE ACCESS FULL| EMP |5 | 150 |3 (0)|00:00:01 |Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT1 - filter("DEPTNO"=20)13 rows sel

36、ected.3、SQL>exec dbms_stats.delete_table_stats(USER,'表 ');( 删除表的统计信息)SQL>exec dbms_stats.gather_table_stats(USER,'表',METHOD_OPT=>'FOR ALLCOLUMNS SIZE 100')(收集表的统计信息)4、AUTOTRACE几个常用选项不生成 autotrace 报告，这是只显示优化器执行路径报告set autotrace off 缺省模式set autotrace on explain autotra

37、ceset autotrace on statistics -set autotrace on set autotrace traceonly 只显示执行统计信息 包含执行计划和统计信息 同 set autotrace on ，但是不显示查询输(1) . set autotrace on explain; -只显示执行计划SQL> set autotrace on explain;SQL>select count(*) from dba_objects;COUNT(*)31820Execution Plan0 SELECT STATEMENT Optimizer=CHOOSE1 0

38、 SORT (AGGREGATE)2 1 VIEW OF 'DBA_OBJECTS'32UNION-ALL43FILTER54TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'65NESTED LOOPS76TABLE ACCESS (FULL) OF 'USER$'86INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)94TABLE ACCESS (BY INDEX ROWID) OF 'IND$'109INDEX (UNIQUE SCAN) OF '

39、;I_IND1' (UNIQUE)113NESTED LOOPS1211TABLE ACCESS (FULL) OF 'USER$'1311INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)(2) . set autotrace on statistics;-只显示统计信息SQL> set autotrace on statistics;SQL> select count(*) from dba_objects;COUNT(*)31820Statistics0 recursive calls0 db bl

40、ock gets25754 consistent gets0 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)0 sorts (disk)1 rows processed(3) . set autotrace traceonly;-同 set autotrace on 只是不显示查询输出SQL> set autotrace trac

41、eonly;SQL> select count(*) from dba_objects;Execution Plan0 SELECT STATEMENT Optimizer=CHOOSE1 0 SORT (AGGREGATE)2 1 VIEW OF 'DBA_OBJECTS'3 2 UNION-ALL4 3 FILTER5 4TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'6 5NESTED LOOPS7 6TABLE ACCESS (FULL) OF 'USER$'8 6INDEX (RANGE SCAN)

42、OF 'I_OBJ2' (UNIQUE)9 4TABLE ACCESS (BY INDEX ROWID) OF 'IND$'10 9INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)11 3NESTED LOOPS12 11 TABLE ACCESS (FULL) OF 'USER$'13 11INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)Statistics0 recursive calls0 db block gets25754 consi

43、stent gets0 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)0 sorts (disk)1 rows processed(4) .set autotrace traceonly explain;-比较实用的选项，只显示执行计划，但是与 set autotrace on explain; 相比不会执行语句，对于仅仅查看大表的 E

44、xplain Plan 非常管用。SQL> set autotrace traceonly explain;SQL> select * from dba_objects;已用时间 : 00: 00: 00.00Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE1 0 VIEW OF 'DBA_OBJECTS'2 1 UNION-ALL3 2 FILTER4 3TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'5 4NESTED LOOPS6 5TABLE ACCESS (F

45、ULL) OF 'USER$'7 5 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)8 3TABLE ACCESS (BY INDEX ROWID) OF 'IND$'9 8 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)10 2 TABLE ACCESS (BY INDEX ROWID) OF 'LINK$'11 10NESTED LOOPS12 11 TABLE ACCESS (FULL) OF 'USER$'13 11 INDE

46、X (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)5、analyzeanalyze table hr.employees compute(estimate) statistics;(compute 收集 每一行数据的统计信息，比较耗时； estimate 收集一部分数据行的统计信息)select t.owner,t.table_name,t.tablespace_name,t.blocks,t.empty_blocks,t.avg_ spacefrom dba_tables twhere t.owner='HR'如何生成 expla

47、in plan?解答: 运行 utlxplan.sql.建立 plan 表针对特定 SQL语句，使用 explain plan set statement_id = 'tstl' into plan_table for sql statement运行 utlxplp.sql 或 utlxpls.sql 察看 explain plan(select * from table(dbms_xplan.display();)EXPLAIhPLAN是一个很好的分析SQL语句的工具,它甚至可以在不执行SQL的情 况下分析语句通过分析,我们就可以知道ORACL是怎么样连接表，使用什么方 式扫

48、描表 （索引扫描或全表扫描 ） 以及使用到的索引名称 .你需要按照从里到外，从上到下的次序解读分析的结果 EXPLAIN PLAN分析的结 果是用缩进的格式排列的 , 最内部的操作将被最先解读 , 如果两个操作处于同 一层中 , 带有最小操作号的将被首先执行 .NESTED LOO是少数不按照上述规则处理的操作，正确的执行路径是检查对NESTED LOO提供数据的操作,其中操作号最小的将被最先处理译者按 :通过实践,感到还是用SQLPLU中的SET TRACE功能比较方便.举例:SQL&gt; list1 SELECT *2 FROM dept, emp可以不显示执行结果 */3* W

49、HERE emp.deptno = dept.deptnoSQL&gt; set autotrace traceonly /*traceonlySQL&gt; /14 rows selected.Execution Plan0 SELECT STATEMENT Optimizer=CHOOSE1 0 NESTED LOOPS2 1 TABLE ACCESS (FULL) OF 'EMP'3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'4 3 INDEX (UNIQUE SCAN) OF 'PK_DEP

50、T' (UNIQUE)Statistics0 recursive calls2 db block gets30 consistent gets0 physical reads0 redo size 2598 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)14 rows processed通过以上分析 , 可以得出实际的执行步骤是 :1. TABLE ACC

51、ESS (FULL) OF 'EMP'2. INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)3. TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'4. NESTED LOOPS (JOINING 1 AND 3)注：目前许多第三方的工具如 TOAD和ORACLE本身提供的工具如 OMS的SQL Analyze都提供了极其方便的EXPLAINPLANT具.也许喜欢图形化界面的朋友们 可以选用它们 .对于 sql 执行的小量高低 . 我们可以通过执行计划的信息基本上可以进行分析查 看该S

52、QL语句执行的时间连接顺序及浪费的数据库资源等信息，从而判断该SQL 语句执行的效率如何 , 下面就简单的介绍一下执行计划的使用2. Explain 使用Oracle RDBM执行每一条SQL语句，都必须经过Oracle优化器的评估。所 以， 了解优化器是如何选择(搜索)路径以及索引是如何被使用的，对优化SQL语句有 很大的帮助。Explain可以用来迅速方便地查出对于给定 SQL语句中的查询数据 是如何得到的即搜索路径 (我们通常称为 Access Path) 。从而使我们选择最优的 查询方式达到最大的优化效果。2.1. 安装 要使用执行计划首先需要执行相应的脚本。使用 Explain 工具

53、需要创建 Explain_plan 表，这必须先进入相关应用表、视图 和索引的所有者的帐户内。Oracle的介质中包含有执行此项工作的 SQL源程序， 例如：ORA_RDBMS： XPLAINPL.SQL (VMS) $ORACLE_HOME/rdbms/admin/utlxplan.sql (UNIX) 该脚本后会生成一个表这个程序会创建一个名为 plan_table 的表，表结构如下： 我们简单的介绍一下主要的字段含义： 字段名 字段类型 含义STATEMENT。VARCHAR2(30) expla in PLA语句中所指定的最优 STATEMENT。 参数值,如果在EXPLAN PLAN

54、©句中没有使用 SET STATEMENTJD那么此值会 被设为 NULL。REMARKS VARCHAR2(8与被解释规划的各步骤相关联的注释最长可达 80字节 OPERATION VARCHAR2(3各)步骤所执行内部操作的名称在某条语句所产生的第 一行中该列的可能取值如下 DELETESTATEMENTNSERT STATEMENSELECTSTATEMENT UPDATE STATEMENTOPTIONS VARCHAR2(3对OPERATIO列中所描述操作的变种OBJECT_NODE VARCHAR2(128于访问对象的数据库链接 database link 的名 称对于使

55、用并行执行的本地查询该列能够描述操作中输出的次序OBJECT_OWNVRRCHAR2(30对于包含有表或索引的架构 schema给出其所有者 的名称OBJECT_NAME VARCHAR2(表或索引的名称 OBJECT_INSTANCETEGER根据对象出现在原始 origi nal语句中的次序所给出的相应次序编号就原始的语句文本而论其处理顺序为自左至右自外向内景象扩 张 viewOBJECT_TYPE VARCHAR2(3用于提供对象描述性信息的修饰符例如索引的 NON-UNIQUEOPTIMIZER VARCHAR2(255当前优化程序的模式ID INTEGER 分配给执行规划各步骤的编号

56、PARENT_ID INTEGE对ID步骤的输出进行操作的下一个执行步骤的 ID POSITION INTEGER寸于具有相同PARENT_ID勺步骤其相应的处理次序COST INTEGE!根据优化程序的基于开销的方法所估计出的操作开销值对于使用 基于规则方法的语句该列为空该列值没有特定的测量单位它只是一个用于比较 执行规划开销大小的权重值CARDINALITY INTEGE根据基于开销的方法对操作所访问行数的估计值BYTES INTEGER艮据基于开销的方法对操作所访问字节的估计2.2. 使用2.2.1. 常规使用 常规使用语法： explain PLAN SET STATEMENT_ID

57、= <string literal> INTO <table> FOR <sql>其中：STATEMENT是一个唯一的字符串，把当前执行计划与存储在同一 PLAN表中的 其它执行计划区别开来。TABLE_NAM是 plan表名，它结构如前所示，你可以任意设定这个名称。 SQL_STATEME是真正的SQL语句。如：SQL&gt; explain plan set statement_id='test1' for2 SELECT a.soctermbegin,3 a.soctermend,4 a.dealserialno,5 a.levydataid,6 a.dealtotal,7 e.categoryitemcode,8 row_number() over(PARTITION BY a.levydataid ORDER BY 1) AS theRow9 FROM tb_soc_packdealdata a,10 tb_Lvy_TaxDataBillMap c,11 Tb_lvy_BillData d,12 tb_soc_levydetaildata e13 WHERE a.levydataid = c.datafrompointer(+)14 AND c.billdataid = d.billdataid(+)