Understand 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有值，采用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 operations (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
where 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列為索引的引導列。考慮下面的查詢：
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語句進行更改，使優化器可以選擇正確的驅動表。

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]