如何看懂ORACLE執行計畫

標籤：rom   mit   嵌套   rac   row   index   驅動   who   statement   

如何看懂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語句變更，使最佳化器可以選擇正確的驅動表。

如何看懂ORACLE執行計畫