分區掃描執行計劃分析簡介 - langtianya－IT工程師數位筆記本

文章出處

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> create table tab01 partition by range(object_id)
2 (partition p1 values less than(10000),
3 partition p2 values less than(20000),
4 partition p3 values less than(30000),
5 partition p_max values less than(maxvalue))
6 as select * from dba_objects;

SQL> create index index_tab01_name on tab01(object_name) local;

PARTITION RANGE ALL是所有分區掃描，比如需要掃描所有的分區才能滿足查詢
SQL> select * from tab01 where object_name='TT';

Execution Plan
----------------------------------------------------------
Plan hash value: 3187188282

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 258 | 8 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 3 | 258 | 8 (0)| 00:00:01 | 1 | 4 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB01 | 3 | 258 | 8 (0)| 00:00:01 | 1 | 4 |
|* 3 | INDEX RANGE SCAN | INDEX_TAB01_NAME | 3 | | 5 (0)| 00:00:01 | 1 | 4 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("OBJECT_NAME"='TT')

我們觀察到執行計劃中跟非分區表多出了pstart和pstop這兩項，這個表示掃描該表的所有分區，也就是分區：1到分區：4.

Cbo下的分區還有一個特別重要的特性：cbo會根據where過濾條件中的分區鍵值判斷只掃描需要訪問的分區，對于不需要的分區不會訪問，這個特性叫做partition purging
PARTITION RANGE SINGLE是單個分區掃描，比如查詢中可以看見很清楚的過濾條件表示只需要掃描某一個分區即可
SQL> select * from tab01 where object_id=5000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2393410319

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 57 (2)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 79 | 57 (2)| 00:00:01 | 2 | 2 |
|* 2 | TABLE ACCESS FULL | TAB01 | 1 | 79 | 57 (2)| 00:00:01 | 2 | 2 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID"=5000)

SQL> select * from tab01 where object_id=5000 and object_name='OP';

Execution Plan
----------------------------------------------------------
Plan hash value: 3913001591

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 79 | 2 (0)| 00:00:01 | 2 | 2 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TAB01 | 1 | 79 | 2 (0)| 00:00:01 | 2 | 2 |
|* 3 | INDEX RANGE SCAN | INDEX_TAB01_NAME | 2 | | 1 (0)| 00:00:01 | 2 | 2 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID"=5000)
3 - access("OBJECT_NAME"='OP')

這里的pstart和pstop都是2，表示只掃描了一個分區。

PARTITION RANGE INLIST：當分區關鍵字存在多個可選值時，比較明顯的是查詢中有in（n1，n2 。。。nmax）和par_key=n1 or par_key=n2，注意單個可選值并不會出現PARTITION RANGE INLIST而是出現partition range single
SQL> select * from tab01 where object_id in (5000,5500);

Execution Plan
----------------------------------------------------------
Plan hash value: 2325940683

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 172 | 57 (2)| 00:00:01 | | |
| 1 | PARTITION RANGE INLIST| | 2 | 172 | 57 (2)| 00:00:01 |KEY(I) |KEY(I) |
|* 2 | TABLE ACCESS FULL | TAB01 | 2 | 172 | 57 (2)| 00:00:01 |KEY(I) |KEY(I) |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID"=5000 OR "OBJECT_ID"=5500)

注意這里的執行計劃是partition range inlist，而且pstart和pstop都是用的key（I），不過這個并不代表只掃描了一個分區，如下：
SQL> select * from tab01 where object_id in (5000,20000);

Execution Plan
----------------------------------------------------------
Plan hash value: 2325940683

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 172 | 88 (2)| 00:00:02 | | |
| 1 | PARTITION RANGE INLIST| | 2 | 172 | 88 (2)| 00:00:02 |KEY(I) |KEY(I) |
|* 2 | TABLE ACCESS FULL | TAB01 | 2 | 172 | 88 (2)| 00:00:02 |KEY(I) |KEY(I) |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID"=5000 OR "OBJECT_ID"=20000)

該查詢會去掃描兩個分區，而這里還是partition range inlist ，pstart和pstop都是KEY(I)，所以這里并不表示只掃描了一個分區，至于這個key（I）在官檔中并沒有找到一個確切的說明，小魚覺得這個應該是個變量的形式，所以訪問的分區當然也可能出現多個。

partition by iterator：分區迭代掃描，當cbo需要掃描多個連續的分區時就會出現partition by iterator分區迭代，pstart和pstop為需要掃描的分區
SQL> select * from tab01 where object_id>10000;

Execution Plan
----------------------------------------------------------
Plan hash value: 232993693

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40772 | 3424K| 153 (2)| 00:00:02 | | |
| 1 | PARTITION RANGE ITERATOR| | 40772 | 3424K| 153 (2)| 00:00:02 | 2 | 4 |
|* 2 | TABLE ACCESS FULL | TAB01 | 40772 | 3424K| 153 (2)| 00:00:02 | 2 | 4 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID">10000)

初步來看partition range iterator應該是多個分區的迭代掃描，而上述測試用例也是如此，掃描的分區為pstart：2到pstop ：4，一共三個分區。

但是小魚發現優化器在下列查詢時也會采取partition range iterator，請看下例：
SQL> select * from tab01 where object_id<5000;

Execution Plan
----------------------------------------------------------
Plan hash value: 232993693

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4660 | 391K| 60 (2)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 4660 | 391K| 60 (2)| 00:00:01 | 1 | 2 |
|* 2 | TABLE ACCESS FULL | TAB01 | 4660 | 391K| 60 (2)| 00:00:01 | 1 | 2 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID"<5000)

按理說object_id<5000只需要掃描分區p1即可，而這里cbo確給出了partition range iterator執行計劃，并且pstart為1，pstop為2，cbo根據統計信息得出需要掃描partition ：1和：2兩個分區。

這里小魚想的是否能夠通過動態采樣讓cbo選擇只掃描一個分區，從而出現partition range single的執行計劃，不過這里小魚即使采取動態采樣level 10，依然cbo給出的執行計劃依然是partition range iterator，從這里看出cbo并不是絕對的智能，當統計信息越準確它給出的執行計劃就越準，但是還是可能出現不完全執行計劃。
SQL> select /*+dynamic_sampling(tab01 10)*/* from tab01 where object_id<2000;

Execution Plan
----------------------------------------------------------
Plan hash value: 232993693

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1953 | 164K| 60 (2)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 1953 | 164K| 60 (2)| 00:00:01 | 1 | 2 |
|* 2 | TABLE ACCESS FULL | TAB01 | 1953 | 164K| 60 (2)| 00:00:01 | 1 | 2 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID"<2000)

Note
-----
- dynamic sampling used for this statement

但是如果我們縮小查詢的范圍，果然就出現了我們想要的partition range single執行計劃，而且pstart和pstop都是1，這里表示優化器確實只準備掃描一個分區。
SQL> select * from tab01 where object_id<200;

Execution Plan
----------------------------------------------------------
Plan hash value: 2393410319

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 189 | 11529 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 189 | 11529 | 4 (0)| 00:00:01 | 1 | 1 |
|* 2 | TABLE ACCESS FULL | TAB01 | 189 | 11529 | 4 (0)| 00:00:01 | 1 | 1 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID"<200)

PARTITION RANGE EMPTY這個表示cbo根據統計信息和sql語句，得出該表所對應的分區不存在任何符合查詢的結果集，就會顯示partition range empty。

例如sql業務邏輯存在問題：
SQL> select * from tab01 where object_id<2000 and object_id>20000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2001509460

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 0 (0)| | | |
|* 1 | FILTER | | | | | | | |
| 2 | PARTITION RANGE EMPTY| | 1 | 86 | 2 (0)| 00:00:01 |INVALID|INVALID|
|* 3 | TABLE ACCESS FULL | TAB01 | 1 | 86 | 2 (0)| 00:00:01 |INVALID|INVALID|
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(NULL IS NOT NULL)
3 - filter("OBJECT_ID"<2000 AND "OBJECT_ID">20000)

再例如指定的查詢根據表的定義確定沒有滿足查詢的分區：
SQL> create table tab02 partition by range(object_id)
2 (partition p1 values less than(10000),
3 partition p2 values less than(20000),
4 partition p3 values less than(30000))
5 as select * from dba_objects where object_id<30000;

Table created.

SQL> select * from tab02 where object_id>30000;

Execution Plan
----------------------------------------------------------
Plan hash value: 559767399

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 177 | 28 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE EMPTY| | 1 | 177 | 28 (0)| 00:00:01 |INVALID|INVALID|
|* 2 | TABLE ACCESS FULL | TAB02 | 1 | 177 | 28 (0)| 00:00:01 |INVALID|INVALID|
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID">30000)

Note
-----
- dynamic sampling used for this statement

看出這里的pstart和pstop都是invalid的。

Partition range or：表示where條件中存在了or運算，并且cbo得出這些運算需要跨越多個不連續的分區時
SQL> select * from tab01 where object_id<5000 or (object_id>5000 and object_id<8000);

Execution Plan
----------------------------------------------------------
Plan hash value: 1971037408

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7196 | 604K| 60 (2)| 00:00:01 | | |
| 1 | PARTITION RANGE OR| | 7196 | 604K| 60 (2)| 00:00:01 |KEY(OR)|KEY(OR)|
|* 2 | TABLE ACCESS FULL| TAB01 | 7196 | 604K| 60 (2)| 00:00:01 |KEY(OR)|KEY(OR)|
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID"<5000 OR "OBJECT_ID"<8000 AND "OBJECT_ID">5000)

可能有些朋友覺得這個其實只需要掃描一個分區即可，因為這個where object_id<5000 or (object_id>5000 and object_id<8000)過濾條件，但是cbo并不能如此的智能,此時cbo根據其統計信息和sql語句得出其需要計算掃描時可能需要跨越不連續的多個分區，當然也可能不需要，所以這里也自然也就出現了PARTITION RANGE OR了，而這個key（OR）應該跟partition range inlist中的key（I）一樣，也是一個變化的值，所以這里掃描的分區數量具有不確切性。

同樣動態采樣也是如此：
SQL> select /*+dynamic_sampling(tab01 10)*/* from tab01 where object_id<5000 or (object_id>5000 and object_id<8000);

Execution Plan
----------------------------------------------------------
Plan hash value: 1971037408

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7772 | 652K| 60 (2)| 00:00:01 | | |
| 1 | PARTITION RANGE OR| | 7772 | 652K| 60 (2)| 00:00:01 |KEY(OR)|KEY(OR)|
|* 2 | TABLE ACCESS FULL| TAB01 | 7772 | 652K| 60 (2)| 00:00:01 |KEY(OR)|KEY(OR)|
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID"<5000 OR "OBJECT_ID"<8000 AND "OBJECT_ID">5000)

Note
-----
- dynamic sampling used for this statement

上面小魚簡單介紹了range分區掃描的幾種執行路徑，有些oracle的官檔中都沒有確切的說明，當然隨著我們對cbo的認識，有些問題會隨著反復學習和接觸新的東西慢慢解開。

新年上班已經一周多了，漸漸也要抓緊了，這年任務還是很大的：ocm考試、深入oracle和常用的高可用、mysql和一些nosql、os的東西等，當然還有最頭疼的english！

原文：http://www.dbaxiaoyu.com/archives/1780

文章列表