【原創】Oracle execute plan 原理分析與例項分享

引言：oracle 執行計劃對我們並不陌生，往往我們在工作中只會在發生效能瓶頸時看一下，沒有想過執行計劃是如何生成的。下面用例項來模擬資料訪問方式和資料處理方式的演變。

1.執行計劃—通過表訪問資料 TABLE ACCESS FULL

LEO1@LEO1> create table leo1 as select * from dba_objects; 我們建立一張表leo1

Table created.

LEO1@LEO1> select count(*) from leo1; 這張表有71955條記錄

COUNT(*)

----------

71955

LEO1@LEO1> set autotrace trace exp; 啟動執行計劃

LEO1@LEO1> select * from leo1;

Execution Plan

----------------------------------------------------------

Plan hash value: 2716644435

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 83162 | 16M| 287 (1)| 00:00:04 |

| 1 | TABLE ACCESS FULL | LEO1 | 83162 | 16M| 287 (1)| 00:00:04 |

--------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

資料訪問方式：走的是全表掃描，因為沒有建立索引，所以沒辦法走索引，這是一種效率不高的資料訪問方式，在實際應用中較少。

2.執行計劃—通過表並行訪問資料 PARALLEL

LEO1@LEO1> select /*+ parallel */ count(*) from leo1; 自動評估並行度

Execution Plan

----------------------------------------------------------

Plan hash value: 452265093

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 159 (0)| 00:00:02 | | | |

| 1 | SORT AGGREGATE | | 1 | | | | | |

| 2 | PX COORDINATOR | | | | | | | |

| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |

| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |

| 5 | PX BLOCK ITERATOR | | 71955 | 159 (0)| 00:00:02 | Q1,00 | PCWC | |

| 6 | TABLE ACCESS FULL| LEO1 | 71955 | 159 (0)| 00:00:02 | Q1,00 | PCWP | |

--------------------------------------------------------------------------------------------------------

Note

-----

- automatic DOP: Computed Degree of Parallelism is 2

如果不指定並行度，優化器自動評估並行度為2，因為我的小本本就是雙核的，並行度最大隻能是2

LEO1@LEO1> select /*+ parallel(leo1 4) */ count(*) from leo1; 指定4個並行度

Execution Plan

----------------------------------------------------------

Plan hash value: 452265093

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 80 (2)| 00:00:01 | | | |

| 1 | SORT AGGREGATE | | 1 | | | | | |

| 2 | PX COORDINATOR | | | | | | | |

| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |

| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |

| 5 | PX BLOCK ITERATOR | | 71955 | 80 (2)| 00:00:01 | Q1,00 | PCWC | |

| 6 | TABLE ACCESS FULL| LEO1 | 71955 | 80 (2)| 00:00:01 | Q1,00 | PCWP | |

--------------------------------------------------------------------------------------------------------

資料訪問方式：這次的訪問方式採用了並行機制，並行比非並行的效率是截然不同的，我們指定了4個並行度那麼就會有4個程式來分割整個表資料，每個程式分別處理1/4資料，這樣理論上提升了4倍的效率（並行度的個數要和cpu數量匹配，目前我的本是2核的所以我們設定了4個並行度也是體現不出來的，如果你指定了並行度，優化器就不會自動評估了）。我們來看一下執行計劃的執行順序，首先全表掃描LEO1->並行迭代方式訪問塊-> SORT AGGREGATE 把檢索出來的結果進行統計-> PX SEND QC (RANDOM)序列的把4個程式的結果逐個傳送到QC並行協調器-> PX COORDINATOR 並行協調器進行結果合併-> SORT AGGREGATE再次統計結果->最後把結果返回給使用者。

3.執行計劃—通過索引唯一掃描訪問資料 INDEX UNIQUE SCAN

LEO1@LEO1> alter table leo1 add constraint pk_leo1 primary key (object_id); 給leo1表object_id列新增主鍵

Table altered.

LEO1@LEO1> set linesize 300

LEO1@LEO1> select * from leo1 where object_id=100; 檢視id=100時資料訪問方式

Execution Plan

----------------------------------------------------------

Plan hash value: 2711624550

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | LEO1 | 1 | 207 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_LEO1 | 1 | | 1 (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

-------------------------------------------------------------------

2 - access("OBJECT_ID"=100) 謂詞條件 object_id=100，就是你根據什麼條件生成執行計劃

資料訪問方式：這條sql語句大家很容易看出，首先執行INDEX UNIQUE SCAN 索引唯一掃描，因為你選擇的是等值範圍，優化器可以直接定位你的索引塊，又因為你要的是id=100這條記錄的所有欄位值(*)，因此TABLE ACCESS BY INDEX ROWID還要通過索引鍵值找到對應的ROWID，再去訪問ROWID所在資料塊找到需要的記錄。這是一種比較快速的資料訪問方式，掃描的塊少，資源佔用率也小，是一種推薦使用的方式。

LEO1@LEO1> select object_id from leo1 where object_id=100;

Execution Plan

----------------------------------------------------------

Plan hash value: 1889847647

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |

|* 1 | INDEX UNIQUE SCAN| PK_LEO1 | 1 | 13 | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("OBJECT_ID"=100)

注：select object_id from leo1 where object_id=100; 如果是執行這條sql語句，那麼我們只需掃描索引鍵值即可得到結果，無需再去訪問資料塊了（因為索引塊上就儲存了id=100資料），這種方式又加快了檢索的速度。

4.執行計劃—通過索引範圍掃描訪問資料 INDEX RANGE SCAN

LEO1@LEO1> select * from leo1 where object_id>10 and object_id<100;

Execution Plan

----------------------------------------------------------

Plan hash value: 2612250437

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 89 | 18423 | 4 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | LEO1 | 89 | 18423 | 4 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | PK_LEO1 | 89 | | 2 (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID">10 AND "OBJECT_ID"<100) 謂詞條件object_id>10 and object_id<100

Note

-----

- dynamic sampling used for this statement (level=2) 動態取樣用於此語句

資料訪問方式：由於你的where條件是object_id>10 and object_id<100 一個範圍（而索引塊按順序排序的，也是按順序掃描的）因此優化器採用了INDEX RANGE SCAN索引範圍掃描，把符合條件的索引塊拿出來，找到索引鍵值對應的ROWID，再去訪問ROWID所在的資料塊找到需要的記錄。這種方式雖然比索引唯一掃描效率低一點，但大大優於全表掃描。也是推薦的一種資料訪問方法。

5.執行計劃—通過快速索引全掃描訪問資料 INDEX FAST FULL SCAN

原理：把索引鏈切割成很多區域，多索引塊並行掃描，這樣比INDEX FULL SCAN效率要高

LEO1@LEO1> select count(*) from leo1;

Execution Plan

----------------------------------------------------------

Plan hash value: 173418543

-------------------------------------------------------------------------

-------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 46 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FAST FULL SCAN | PK_LEO1 | 83162 | 46 (0)| 00:00:01 |

-------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

資料訪問方式：我們的目的想知道leo1表一共有多少條記錄，我們又知道表上建立了索引，索引的條數和資料行是一一對應的。那麼我們掃描一遍索引塊要比掃描一遍表資料塊是不是要快啊，因為掃描的資料量少對吧，在索引塊裡只需掃描有多少條索引鍵值就知道對應有多少條記錄了，同時又啟動了並行掃描方式，速度的給力是不言而喻的。SORT AGGREGATE 對檢索出來的結果進行統計。

6.執行計劃—通過索引全掃描訪問資料 INDEX FULL SCAN

LEO1@LEO1> select object_id from leo1 order by object_id;

Execution Plan

----------------------------------------------------------

Plan hash value: 1595913726

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 71955 | 351K| 150 (0)| 00:00:02 |

| 1 | INDEX FULL SCAN | PK_LEO1 | 71955 | 351K| 150 (0)| 00:00:02 |

----------------------------------------------------------------------------

資料訪問方式：我們要對記錄進行一次排序，索引塊就是按照順序儲存的，也是按照順序掃描的。排序工作是序列化的，因此不能並行操作（也就不適應INDEX FAST FULL SCAN）所以我們把索引鍵值全部掃描一遍就相當於排好序了，根本用不著去訪問表資料塊。

7.執行計劃—通過索引跳躍掃描訪問資料 INDEX SKIP SCAN

解釋：所謂的索引跳躍掃描，是指跳過前導欄位進行掃描，例如表上有一個複合索引，而在查詢中有除了索引中第一列（前導欄位）的其他列作為條件，並且優化器是CBO，這時候執行計劃就有可能走INDEX SKIP SCAN

LEO1@LEO1> create table leo3 (x number,y varchar2(30),z varchar2(30)); 建立一個表，有三個欄位

Table created.

LEO1@LEO1> create index compound_idx_leo3 on leo3(x,y); 建立一個複合索引

Index created.

LEO1@LEO1> begin 插入10w條記錄

for i in 1..100000 loop

insert into leo3 values(mod(i,30),to_char(i),to_char(i));

end loop;

commit;

end;

/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

LEO1@LEO1> analyze table leo3 compute statistics; 對錶進行整體資料分析

Table analyzed.

LEO1@LEO1> set autotrace trace explain;

LEO1@LEO1> select * from leo3 where y='1000';

Execution Plan

----------------------------------------------------------

Plan hash value: 1334303583

-------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 12 | 32 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| LEO3 | 1 | 12 | 32 (0)| 00:00:01 |

|* 2 | INDEX SKIP SCAN | COMPOUND_IDX_LEO3 | 1 | | 31 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id): 謂詞條件跳過前導欄位（x）進行掃描才成

---------------------------------------------------

2 - access("Y"='1000')

filter("Y"='1000')

資料訪問方式：如果要想使用索引跳躍掃描需要幾個前提條件：

1. 跳過前導欄位

2. optimizer是CBO

3. 對錶資料進行分析，讓CBO優化器瞭解資料的分佈情況

4. 還需要保證第一列的distinct value非常小，表上要有正確的統計資料

有了上述條件，我們在進行資料掃描時就有可能會走INDEX SKIP SCAN

8.執行計劃—資料處理方式 雜湊關聯 HASH JOIN

HASH JOIN特點：沒有索引時hash的效果更好，hash需要一定的計算所以會消耗些cpu資源

LEO1@LEO1> create table leo2 as select * from dba_objects where rownum<20000; 建立leo2表

Table created.

LEO1@LEO1> set autotrace off 關閉執行計劃

LEO1@LEO1> select count(*) from leo2; 表中有20000行資料

COUNT(*)

----------------

20000

LEO1@LEO1> set autotrace trace exp; 啟動執行計劃

LEO1@LEO1> select leo1.* from leo1,leo2 where leo1.object_id=leo2.object_id; HASH JOIN

Execution Plan

----------------------------------------------------------

Plan hash value: 2290691545

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 21772 | 2338K| 367 (1)| 00:00:05 |

|* 1 | HASH JOIN | | 21772 | 2338K| 367 (1)| 00:00:05 |

| 2 | TABLE ACCESS FULL| LEO2 | 21772 | 276K| 79 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| LEO1 | 71955 | 6816K| 287 (1)| 00:00:04 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("LEO1"."OBJECT_ID"="LEO2"."OBJECT_ID") 謂詞條件2個表中object_id相等的行

Note

-----

- dynamic sampling used for this statement (level=2) 動態取樣用於此語句

資料處理方式：查詢2個表中object_id相等的行，HASH JOIN特點先把小表build到記憶體中，再和大表進行精準匹配，select leo1.* from leo2,leo1 where leo1.object_id=leo2.object_id; 不管from leo2,leo1如何排序，都會先掃描小表LEO2（記錄少），在掃描大表LEO1（記錄多），掃描完2個表之後，把leo2build到記憶體中，在和leo1進行hash join。

題外話：說一說“執行計劃的執行順序”

先從開頭一直往右看，一直看到最右邊有並列程式碼部分。如果遇到並列的，就從上往下看。對於並列的步驟，靠上的先執行；對於不併列的步驟，靠右的先執行。

9.執行計劃—資料處理方式 巢狀迴圈關聯 NESTED LOOP JOIN

NESTED LOOP JOIN特點：兩張表最好有索引，通過索引鍵值進行匹配效率較高

LEO1@LEO1> alter table leo2 add constraint pk_leo2 primary key (object_id); leo2表新增主鍵

Table altered.

LEO1@LEO1> select leo1.* from leo1,leo2 where leo1.object_id=leo2.object_id; 關聯匹配

Execution Plan

----------------------------------------------------------

Plan hash value: 1603444237

------------------------------------------------------------------------------

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 21772 | 2338K| 291 (2)| 00:00:04 |

| 1 | NESTED LOOPS | | 21772 | 2338K| 291 (2)| 00:00:04 |

| 2 | TABLE ACCESS FULL | LEO1 | 71955 | 6816K| 287 (1)| 00:00:04 |

|* 3 | INDEX UNIQUE SCAN | PK_LEO2 | 1 | 13 | 0 (0)| 00:00:01 |

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("LEO1"."OBJECT_ID"="LEO2"."OBJECT_ID") 謂詞條件等值匹配

Note

-----

- dynamic sampling used for this statement (level=2)

資料處理方式：從leo1表裡拿出一條記錄到leo2表裡進行匹配（當然是通過索引匹配），遍歷整個leo2表，發現匹配的行就取出來。從leo1表裡拿出幾條記錄，就要遍歷leo2表幾次。所以2張表最好有索引才會走NESTED loop join

10.執行計劃—資料處理方式 合併關聯 MERGE JOIN

LEO1@LEO1> alter table leo1 drop constraint pk_leo1;

Table altered.

LEO1@LEO1> alter table leo2 drop constraint pk_leo2;

Table altered.

刪除leo1 leo2表上的主鍵，我測試了一下，如果不刪除主鍵優化器會走NESTED LOOP JOIN方式

LEO1@LEO1> select l1.* from (select * from leo1 order by object_id) l1,(select * from leo2 order by object_id) l2 where l1.object_id=l2.object_id;

Execution Plan

----------------------------------------------------------

Plan hash value: 463394885

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 21772 | 4507K| | 2067 (1)| 00:00:25 |

| 1 | MERGE JOIN | | 21772 | 4507K| | 2067 (1)| 00:00:25 |

| 2 | VIEW | | 71955 | 13M| | 1882 (1)| 00:00:23 |

| 3 | SORT ORDER BY | | 71955 | 6816K| 9448K| 1882 (1)| 00:00:23 |

| 4 | TABLE ACCESS FULL | LEO1 | 71955 | 6816K| | 287 (1)| 00:00:04 |

|* 5 | SORT JOIN | | 21772 | 276K| 872K| 186 (2)| 00:00:03 |

| 6 | TABLE ACCESS FULL | LEO2 | 21772 | 276K| | 79 (0)| 00:00:01 |

-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

5 - access("L1"."OBJECT_ID"="LEO2"."OBJECT_ID")

filter("L1"."OBJECT_ID"="LEO2"."OBJECT_ID")

Note

-----

- dynamic sampling used for this statement (level=2)

資料處理方式：所謂的MERGE JOIN方式，是先對leo1 leo2表整體排序，在逐條進行匹配。通常MERGE JOIN方式效率不高，因為先要有排序過程。順序： leo1表全表掃描-> SORT ORDER BY排序->VIEW排好序的結果集-> leo2表全表掃描-> SORT JOIN關聯排序-> MERGE JOIN。

Oracle execute plan 原理分析與例項分享.pdf (183.58 KB, 下載次數: 0)

Leonarding
2012.12.12
天津&winter
分享技術~成就夢想
Blog：www.leonarding.com

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26686207/viewspace-751001/，如需轉載，請註明出處，否則將追究法律責任。

【原創】Oracle execute plan 原理分析與例項分享

相關文章