如何看懂Oracle执行计划.docx

如何看懂ORACLE执行计划一、什么是执行计划An explain plan is a representation of the access path that is taken when a query is executed within Oracle.二、如何访问数据At the physical level Oracle reads blocks of data. The smallest amount of data read is a single Oracle block, the largest is constrained by operating system limits (and multiblock i/o). Logically Oracle finds the data to read by using the following methods:Full Table Scan (FTS) -全表扫描Index Lookup (unique & non-unique) -索引扫描（唯一和非唯一）Rowid -物理行id 三、执行计划层次关系When looking at a plan, the rightmost (ie most inndented) uppermost operation is the first thing that is executed. -采用最右最上最先执行的原则看层次关系，在同一级如果某个动作没有子ID就最先执行1.一个简单的例子：SQL select /*+parallel (e 4)*/ * from emp e;Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=82 Bytes=7134)1 0 TABLE ACCESS* (FULL) OF EMP (Cost=1 Card=82 Bytes=7134):Q5000-:Q5000表示是并行方式1 PARALLEL_TO_SERIAL SELECT /*+ NO_EXPAND ROWID(A1) */ A1.EMPNO,A1.ENAME,A1.JOB,A1.MGR,A1.HI优化模式是CHOOSE的情况下，看Cost参数是否有值来决定采用CBO还是RBO：SELECT STATEMENT CHOOSE Cost=1234 -Cost有值，采用CBOSELECT STATEMENT CHOOSE -Cost为空，采用RBO(9I是如此显示的)2.层次的父子关系的例子：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 finally the PARENT collates the output.四、例子解说Execution Plan-0 *SELECT STATEMENT Optimizer=CHOOSE (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 shortened summary of this is:Execution starts with ID=0: SELECT STATEMENT but this is dependand on its child objectsSo it executes its first child step: ID=1 PID=0 HASH JOIN but this is dependand on its child objectsSo it executes its first child step: ID=2 PID=1 TABLE ACCESS (FULL) OF DEPTThen the second child step: ID=3 PID=2 TABLE ACCESS (FULL) OF EMPRows are returned to the parent step(s) until finished 五、表访问方式1.Full Table Scan (FTS) 全表扫描In a FTS operation, the whole table is read up to the high water mark (HWM). The HWM marks the last block in the table that has ever had data written to it. If you have deleted 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即表示表曾经扩展的最后一个数据块），读取速度依赖于Oracle初始化参数db_block_multiblock_read_count(我觉得应该这样翻译:FTS扫描会使表使用上升到高水位(HWM),HWM标识了表最后写入数据的块,如果你用DELETE删除了所有的数据表仍然处于高水位(HWM),只有用TRUNCATE才能使表回归,FTS使用多IO从磁盘读取数据块).Query Plan-SELECT STATEMENT CHOOSE Cost=1*INDEX UNIQUE SCAN EMP_I1 -如果索引里就找到了所要的数据，就不会再去访问表2.Index Lookup 索引扫描There are 5 methods of index lookup:index unique scan -索引唯一扫描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 the index. eg:SQL explain plan for select empno,ename from emp where empno=10;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. = explain plan for select mgr from emp where mgr = 5;index full scan -索引全局扫描Full index scans are only available in the CBO as otherwise we are unable to determine 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 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 are not returned in sorted 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. eg: SQL explain plan for select empno,ename 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 from emp where ename=SMITH;3.Rowid 物理ID扫描This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in. -Rowid扫描是最快的访问数据方式六、表连接方式七、运算符1.sort -排序，很消耗资源There are a number of different operations that promote sorts:(1)order by clauses (2)group by (3)sort merge join -这三个会产生排序运算2.filter -过滤，如not in、min函数等容易产生Has a number of different meanings, used to indicate partition elimination, may also indicate an actual filter step where one row source is filtering, 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 from directly as opposed to being broken down into joins on the base tables. A number of constructs make a view non mergeable. Inline views are also non mergeable.eg: SQL explain plan forselect ename,tot from emp,(select empno,sum(empno) tot from big_emp group by empno) tmpwhere emp.empno = tmp.empno; Query Plan-SELECT STATEMENT CHOOSE*HASH JOIN*TABLE ACCESS FULL EMP ANALYZED*VIEW*SORT GROUP BY*INDEX FULL SCAN BE_IX 4.partition view -分区视图Partition views are a legacy technology that were superceded by the partitioning option. This section of the article is provided as reference for such legacy systems. 示例:假定A、B、C都是不是小表，且在A表上一个组合索引：A(a.col1,a.col2) ，注意a.col1列为索引的引导列。考虑下面的查询：selectA.col4 from A , B , CwhereB.col3 = 10 andA.col1 = B.col1andA.col2 = C.col2andC.col3 = 5;Execution Plan- 0SELECT STATEMENT Optimizer=CHOOSE 10MERGE JOIN 21SORT (JOIN) 32NESTED LOOPS 43TABLE ACCESS (FULL) OF B 53TABLE ACCESS (BY INDEX ROWID) OF A 65INDEX (RANGE SCAN) OF INX_COL12A (NON-UNIQUE) 71SORT (JOIN) 87TABLE ACCESS (FULL) OF CStatistics(统计信息参数,参见另外个转载的文章)-0recursive calls（归调用次数）8db block gets(从磁盘上读取的块数,即通过update/delete/select for update读的次数)6consistent gets(从内存里读取的块数,即通过不带for update的select 读的次数)0physical reads(物理读从磁盘读到数据块数量,一般来说是consistent gets + db block gets)0redo size (重做数执行SQL的过程中，产生的重做日志的大小)551bytes sent via SQL*Net to client430bytes received via SQL*Net from client2SQL*Net roundtrips to/from client2sorts (memory) (在内存中发生的排序)0sorts (disk) (在硬盘中发生的排序)6rows processed 在表做连接时，只能2个表先做连接，然后将连接后的结果作为一个row source，与剩下的表做连接，在上面的例子中，连接顺序为B与A先连接，然后再与C连接：B A Ccol3=10col3=5如果没有执行计划，分析一下，上面的3个表应该拿哪一个作为第一个驱动表？从SQL语句看来，只有B表与C表上有限制条件，所以第一个驱动表应该为这2个表中的一个，到底是哪一个呢？B表有谓词B.col3 = 10，这样在对B表做全表扫描的时候就将where子句中的限制条件(B.col3 = 10)用上，从而得到一个较小的row source, 所以B表应该作为第一个驱动表。而且这样的话，如果再与A表做关联，可以有效利用A表的索引(因为A表的col1列为leading column)。上面的查询中C表上也有谓词(C.col3 = 5)，有人可能认为C表作为第一个驱动表也能获得较好的性能。让我们再来分析一下：如果C表作为第一个驱动表，则能保证驱动表生成很小的row source，但是看看连接条件A.col2 = C.col2，此时就没有机会利用A表的索引，因为A表的col2列不为leading column，这样nested loop的效率很差，从而导致查询的效率很差。所以对于NL连接选择正确的驱动表很重要。因此上面查询比较好的连接顺序为(B - - A) - - C。如果数据库是基于代价的优化器，它会利用计算出的代价来决定合适的驱动表与合适的连接顺序。一般来说，CBO都会选择正确的连接顺序，如果CBO选择了比较差的连接顺序，我们还可以使用ORACLE提供的hints来让CBO采用正确的连接顺序。如下所示select /*+ ordered */ A.col4from B,A,CwhereB.col3 = 10 andA.col1 = B.col1 andA.col2 = C.col2 andC.col3 = 5 既然选择正确的驱动表这么重要，那么让我们来看一下执行计划，到底各个表之间是如何关联的，从而得到执行计划中哪个表应该为驱动表：在执行计划中，需要知道哪个操作是先执行的，哪个操作是后执行的，这对于判断哪个表为驱动表有用处。判断之前，如果对表的访问是通过rowid，且该rowid的值是从索引扫描中得来得，则将该索引扫描先从执行计划中暂时去掉。然后在执行计划剩下的部分中，判断执行顺序的指导原则就是：最右、最上的操作先执行。具体解释如下：得到去除妨碍判断的索引扫描后的执行计划：Execution Plan- 0SELECT STATEMENT Optimizer=CHOOSE 10 MERGE JOIN 21 SORT (JOIN) 32 NESTED LOOPS 43 TABLE ACCESS (FULL) OF B5 3 TABLE