如何看懂ORACLE执⾏计划
sort of等于什么
如何看懂执⾏计划
⼀、什么是执⾏计划
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=8
2 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有值，采⽤CBO
SELECT 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 it's child objects
So it executes its first child step: ID=1 PID=0 HASH JOIN but this is dependand on it's child objects
So it executes its first child step: ID=2 PID=1 TABLE ACCESS (FULL) OF 'DEPT'
Then 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) 全表扫描
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 oper
ations (e.g. > < <> >= <= between) .
eg:SQL> 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 for
select ename,tot from emp,(select empno,sum(empno) tot from big_emp group by empno) tmp
pno = pno;
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表上⼀个组合索引：l2) ，注意a.col1列为索引的引导列。考虑下⾯的查询：
select　A.col4  from  A , B , C
where　B.col3 = 10  and　A.col1 = B.col1　and　A.col2 = C.col2　and　C.col3 = 5;
Execution Plan
------------------------------------
  0  　SELECT STATEMENT Optimizer=CHOOSE
  1  0　MERGE JOIN
  2  1　SORT (JOIN)
  3  2　NESTED LOOPS
  4  3　TABLE ACCESS (FULL) OF 'B'
  5  3　TABLE ACCESS (BY INDEX ROWID) OF 'A'
  6  5　INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
  7  1　SORT (JOIN)
  8  7　TABLE ACCESS (FULL) OF 'C'
Statistics(统计信息参数，参见另外个转载的⽂章)
--------------------------------------
    　0　recursive calls（归调⽤次数）
    　8　db block gets(从磁盘上读取的块数，即通过update/delete/select for update读的次数)
    　6　consistent gets(从内存⾥读取的块数，即通过不带for update的select 读的次数)
    　0　physical reads(物理读—从磁盘读到数据块数量,⼀般来说是'consistent gets' + 'db block gets')
    　0　redo size      (重做数——执⾏SQL的过程中，产⽣的重做⽇志的⼤⼩)
    551　bytes sent via SQL*Net to client
    430　bytes received via SQL*Net from client
    　2　SQL*Net roundtrips to/from client
    　2　sorts (memory) (在内存中发⽣的排序)
    　0　sorts (disk)  (在硬盘中发⽣的排序)
    　6　rows processed
  在表做连接时，只能2个表先做连接，然后将连接后的结果作为⼀个row source，与剩下的表做连接，在上⾯的例⼦中，连接顺序为B与A先连接，然后再与C连接：
B  <---> A <--->  C
col3=10      　col3=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.col4
from  B,A,C
where　B.col3 = 10  and  A.col1 = B.col1  and  A.col2 = C.col2  and  C.col3 = 5
既然选择正确的驱动表这么重要，那么让我们来看⼀下执⾏计划，到底各个表之间是如何关联的，从⽽得到执⾏计划中哪个表应该为驱动表：
在执⾏计划中，需要知道哪个操作是先执⾏的，哪个操作是后执⾏的，这对于判断哪个表为驱动表有⽤处。判断之前，如果对表的访问是通过rowid，且该rowid的值是从索引扫描中得来得，则将该索引扫描先从执⾏计划中暂时去掉。然后在执⾏计划剩下的部分中，判断执⾏顺序的指导原则就是：最右、最上的操作先执⾏。具体解释如下：
得到去除妨碍判断的索引扫描后的执⾏计划：Execution Plan
-------------------------------------
  0  　SELECT STATEMENT Optimizer=CHOOSE
  1  0 MERGE JOIN
  2  1 SORT (JOIN)
  3  2    NESTED LOOPS
  4  3      TABLE ACCESS (FULL) OF 'B'
5            3      TABLE ACCESS (BY INDEX ROWID) OF 'A'
6  5        INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
  7  1 SORT (JOIN)
  8  7    TABLE ACCESS (FULL) OF 'C'
  看执⾏计划的第3列，即字母部分，每列值的左⾯有空格作为缩进字符。在该列值左边的空格越多，说明该列值的缩进越多，该列值也越靠右。如上⾯的执⾏计划所⽰：第⼀列值为6的⾏的缩进最多，即该⾏最靠右；第⼀列值为4、5的⾏的缩进⼀样，其靠右的程度也⼀样，但是第⼀列值为4的⾏⽐第⼀列值为5的⾏靠上；谈论上下关系时，只对连续的、缩进⼀致的⾏有效。
  从这个图中我们可以看到，对于NESTED LOOPS部分，最右、最上的操作是TABLE ACCESS (FULL) OF 'B'，所以这⼀操作先执⾏，所以该操作对应的B表为第⼀个驱动表(外部表)，⾃然，A表就为内部表了。从图中还可以看出，B与A表做嵌套循环后⽣成了新的row source ，对该row source进⾏来排序后，与C表对应的排序了的row source(应⽤了C.col3 = 5限制条件)进⾏SMJ连接操作。所以从上⾯可以得出如下事实：B表先与A表做嵌套循环，然后将⽣成的row source与C表做排序—合并连接。
通过分析上⾯的执⾏计划，我们不能说C表⼀定在B、A表之后才被读取，事实上，B表有可能与C表同时
被读⼊内存，因为将表中的数据读⼊内存的操作可能为并⾏的。事实上许多操作可能为交叉进⾏的，因为ORACLE读取数据时，如果就是需要⼀⾏数据也是将该⾏所在的整个数据块读⼊内存，⽽且有可能为多块读。
　看执⾏计划时，我们的关键不是看哪个操作先执⾏，哪个操作后执⾏，⽽是关键看表之间连接的顺序(如得知哪个为驱动表，这需要从操作的顺序进⾏判断)、使⽤了何种类型的关联及具体的存取路径(如判断是否利⽤了索引)
在从执⾏计划中判断出哪个表为驱动表后，根据我们的知识判断该表作为驱动表(就像上⾯判断ABC表那样)是否合适，如果不合适，对SQL 语句进⾏更改，使优化器可以选择正确的驱动表。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。