從10046看Oracle分割槽裁剪

對於分割槽表或者分割槽索引來說，優化器可以自動從from和where中根據分割槽鍵直接提取出需要訪問的分割槽，從而避免掃描所有的分割槽，降低了IO請求。

分割槽表、全域性索引（Global Index）和區域性索引（Local Index）是分割槽裁剪過程中經常涉及的物件內容。本篇採用10046跟蹤事件方法，來判斷資料表分割槽操作行為，從而來研究Oracle分割槽表究竟是怎麼影響執行計劃和操作動作的。

 

一、 普通資料表分割槽裁剪

資料表T_PART只有一個分割槽結構，沒有全域性或者本地索引結構。所以，只有在SQL中包括分割槽鍵owner，才可能使用到分割槽裁剪的特點。

建立資料表T_PART，依據owner建立列表分割槽。

SQL> create table t_part
  2  partition by list(owner)
  3  (
  4     partition t_part_p0 values ('SYS'),
  5     partition t_part_p1 values ('SYSTEM'),
  6     partition t_part_p2 values ('SCOTT'),
  7     partition t_part_p3 values (default)
  8  )
  9  as select * from dba_objects;
Table created

SQL> exec dbms_stats.gather_table_stats(user,'T_PART',cascade => true);
PL/SQL procedure successfully completed 

SQL> col partition_name for a10;
SQL> select PARTITION_NAME, SEGMENT_TYPE, HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS, EXTENTS from dba_segments where owner='SYS' and segment_name='T_PART';

PARTITION_ SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
---------- ------------------ ----------- ------------ ---------- ---------- ----------
T_PART_P3  TABLE PARTITION              1        94848    8388608       1024          1
T_PART_P2  TABLE PARTITION              1        96896    8388608       1024          1
T_PART_P1  TABLE PARTITION              1        95872    8388608       1024          1
T_PART_P0  TABLE PARTITION              1        93824    8388608       1024          1

首先檢視一下執行計劃情況。

SQL> explain plan for select * from t_part where owner='SYS' and object_id=1000;
Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2970683307
--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |    97 |   146   (0)| 00:00:02 |
|   1 |  PARTITION LIST SINGLE|        |     1 |    97 |   146   (0)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL   | T_PART |     1 |    97 |   146   (0)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID"=1000)

14 rows selected

Partition List Single是典型的分割槽裁剪動作，表示的是對資料段分割槽進行“單點”Single定位。之後進行T_PART的全表掃描動作。

下面我們使用10046來進行檢查。首先進行Trace File定位，清空shared pool和buffer cache。

SQL> select value from v$diag_info where name='Default Trace File';

VALUE
---------------------------------------
/u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_1912.trc

SQL> alter system flush shared_pool;
System altered

SQL> alter system flush buffer_cache;
System altered

開啟session級別的trace來進行定位。

SQL> alter session set events='10046 trace name context forever, level 12';
Session altered.

SQL> select * from t_part where owner='SYS' and object_id=1000;

SQL> alter session set events='10046 trace name context off';
Session altered.

在Trace檔案中，定位到Oracle在執行SQL的工作內容。

=====================

PARSING IN CURSOR #3075322416 len=57 dep=0 uid=0 oct=3 lid=0 tim=1403365761462254 hv=919498438 ad='35d5f3cc' sqlid='2q5x87wvcwvq6'

select * from t_part where owner='SYS' and object_id=1000

END OF STMT

PARSE #3075322416:c=373942,e=607225,p=69,cr=911,cu=0,mis=1,r=0,dep=0,og=1,plh=2970683307,tim=1403365761462249

EXEC #3075322416:c=0,e=154,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2970683307,tim=1403365761462630

WAIT #3075322416: nam='SQL*Net message to client' ela= 7 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1403365761462709

WAIT #3075322416: nam='db file sequential read' ela= 182 file#=1 block#=93824 blocks=1 obj#=87165 tim=1403365761480550

WAIT #3075322416: nam='direct path read' ela= 1159 file number=1 first dba=93825 block cnt=31 obj#=87165 tim=1403365761484317

WAIT #3075322416: nam='direct path read' ela= 9508 file number=1 first dba=93856 block cnt=32 obj#=87165 tim=1403365761494179

FETCH #3075322416:c=21997,e=32293,p=64,cr=14,cu=0,mis=0,r=1,dep=0,og=1,plh=2970683307,tim=1403365761495067

WAIT #3075322416: nam='SQL*Net message from client' ela= 1172 driver id=1650815232 #bytes=1 p3=0 obj#=87165 tim=1403365761496361

WAIT #3075322416: nam='direct path read' ela= 1818 file number=1 first dba=93888 block cnt=32 obj#=87165 tim=1403365761498367

WAIT #3075322416: nam='direct path read' ela= 1095 file number=1 first dba=93920 block cnt=32 obj#=87165 tim=1403365761499704

WAIT #3075322416: nam='direct path read' ela= 1306 file number=1 first dba=93952 block cnt=32 obj#=87165 tim=1403365761501523

WAIT #3075322416: nam='direct path read' ela= 1171 file number=1 first dba=93984 block cnt=32 obj#=87165 tim=1403365761502905

WAIT #3075322416: nam='direct path read' ela= 1162 file number=1 first dba=94016 block cnt=32 obj#=87165 tim=1403365761504213

WAIT #3075322416: nam='direct path read' ela= 1747 file number=1 first dba=94048 block cnt=32 obj#=87165 tim=1403365761506143

WAIT #3075322416: nam='direct path read' ela= 1052 file number=1 first dba=94080 block cnt=32 obj#=87165 tim=1403365761507410

WAIT #3075322416: nam='direct path read' ela= 713 file number=1 first dba=94112 block cnt=32 obj#=87165 tim=1403365761508814

WAIT #3075322416: nam='direct path read' ela= 1173 file number=1 first dba=94144 block cnt=32 obj#=87165 tim=1403365761510489

WAIT #3075322416: nam='direct path read' ela= 1293 file number=1 first dba=94176 block cnt=32 obj#=87165 tim=1403365761511965

WAIT #3075322416: nam='direct path read' ela= 2270 file number=1 first dba=94208 block cnt=32 obj#=87165 tim=1403365761514449

WAIT #3075322416: nam='direct path read' ela= 950 file number=1 first dba=94240 block cnt=32 obj#=87165 tim=1403365761515745

WAIT #3075322416: nam='direct path read' ela= 1234 file number=1 first dba=94272 block cnt=32 obj#=87165 tim=1403365761517615

WAIT #3075322416: nam='direct path read' ela= 2607 file number=1 first dba=94304 block cnt=32 obj#=87165 tim=1403365761520537

WAIT #3075322416: nam='direct path read' ela= 958 file number=1 first dba=94336 block cnt=22 obj#=87165 tim=1403365761522069

FETCH #3075322416:c=16998,e=26473,p=470,cr=521,cu=0,mis=0,r=0,dep=0,og=1,plh=2970683307,tim=1403365761522926

STAT #3075322416 id=1 cnt=1 pid=0 pos=1 obj=0 op='PARTITION LIST SINGLE PARTITION: KEY KEY (cr=535 pr=534 pw=0 time=32310 us cost=146 size=97 card=1)'

STAT #3075322416 id=2 cnt=1 pid=1 pos=1 obj=87164 op='TABLE ACCESS FULL T_PART PARTITION: 1 1 (cr=535 pr=534 pw=0 time=32227 us cost=146 size=97 card=1)'

WAIT #3075322416: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=87165 tim=1403365761523508

...

Oracle首先進行了一次“db file sequential read”，這個操作是一個典型的單塊讀動作。後面的blocks=1也證明了這操作單塊性質。這個塊操作的物件object_id= 87165，對應是哪個物件呢？

SQL> select object_name, subobject_name, object_type from dba_objects where object_id=87165;

OBJECT_NAM SUBOBJECT_ OBJECT_TYPE
---------- ---------- -------------------
T_PART     T_PART_P0  TABLE PARTITION

讀取的資料塊file#=1、block#= 93824，正好是分割槽T_PART_P0的段頭塊，之後就進行的在分割槽內的全表掃描。

全表掃描的行為是：首先從資料字典中找到段頭塊的地址（藉助一系列的recursive calls）,第二步是單塊讀進行資料塊讀，拿到其中的Extents Map分佈，知道各個分割槽Extent的情況。最後，依據Extent分割槽進行一系列的多塊讀動作。

SQL語句跟蹤情況中，Oracle直接定位到了分割槽T_PART_P0，也就是owner=’SYS’的分割槽結構，之後訪問這個分割槽的段頭塊。最後依據段頭塊的情況進行一系列的多塊讀動作。在這個語句中，Oracle選擇了“direct path read”這種直接訪問資料檔案、繞開buffer cache的動作。

最後一個問題：Oracle是怎麼知道需要定位到T_PART_P0的段頭塊，而不是每個塊都要“看看”。這就是分割槽表涉及操作中出現的大量recursive call作用。每一個SQL傳送到Oracle SQL引擎中執行，並不是單獨一句SQL，而是會有一系列的輔助SQL發出，用於協助語句執行，這種語句稱之為recursive call。Recursive Call最重要的作用就是訪問資料字典，獲得資料段結構特性資訊。對分割槽表而言，分割槽特性（分割槽鍵和分割槽型別）、以及各個分割槽段結構是recursive call獲取的重要內容。

從Trace File中，我們也的確看到了一系列的分割槽情況查詢語句。由此，我們可以瞭解到Oracle資料段分割槽裁剪的特性。

首先，Oracle如果發現資料表是分割槽表，並且語句中包括分割槽條件，就從資料字典層面，判斷“究竟是哪個分割槽符合SQL條件”。這樣就可以直接定位到特定的資料分割槽段，也就是我們試驗中的T_PART_P0。之後，就可以進行普通的FTS操作。

下面我們看一下如果有索引因素加入，執行計劃和分割槽裁剪行為有什麼變化呢？

 

 

二、 Global Index與表分割槽裁剪

Global Index是分割槽表中使用的預設索引方案，是一個覆蓋所有分割槽的索引樹結構，對應一個段物件。

行業裡面Global Index的“聲譽”並不是很好。一般優化人員對於分割槽表索引，都是推薦使用Local Index，而非Global Index。這主要是針對兩個方面的考量：管理方面和效能優化方面。

管理方面主要是Global Index失效問題。Global Index覆蓋到整個資料表所有分割槽，分割槽表進行move,drop等操作時會導致global index的失效，引起系統效能下降。

在效能優化方面，Global Index和分割槽表是“相斥”的。SQL語句走分割槽裁剪，就不會選擇Global Index。反之選擇了Global Index，直接定位結果集合rowid，也就沒有必要進行資料表分割槽裁剪。作為兩個都需要消耗優化資源的方案，無論哪個路徑，都需要“閒置”一種優化策略，這的確是不能讓優化人員接受的。

SQL> create index idx_t_part_id on t_part(object_id);
Index created

SQL> exec dbms_stats.gather_table_stats(user,'T_PART',cascade => true);
PL/SQL procedure successfully completed

SQL> explain plan for select * from t_part where owner='SYS' and object_id=1000;
Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2004327352
--------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Rows  | Bytes | Cos
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |     1 |    97 |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T_PART        |     1 |    97 |
|*  2 |   INDEX RANGE SCAN                 | IDX_T_PART_ID |     1 |       |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OWNER"='SYS')
   2 - access("OBJECT_ID"=1000)

15 rows selected

索引IDX_T_PART是上面的Global Index，這個過程中沒有進行分割槽裁剪，也就是直接利用Global Index路徑進行檢索。

下面我們通過10046來進行檢查。

SQL> select value from v$diag_info where name='Default Trace File';

VALUE
-----------------------------------------
/u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_2035.trc

SQL> alter system flush shared_pool;
System altered.

SQL> alter system flush buffer_cache;
System altered.

SQL> alter session set events='10046 trace name context forever, level 12';
Session altered.

SQL> select * from t_part where owner='SYS' and object_id=1000;

SQL> alter session set events='10046 trace name context off';
Session altered.

Trace檔案中片段如下：

=====================

PARSING IN CURSOR #3075330936 len=58 dep=0 uid=0 oct=3 lid=0 tim=1403366711465450 hv=2083714074 ad='35d34494' sqlid='2kcw7yjy35x0u'

 select * from t_part where owner='SYS' and object_id=1000

END OF STMT

PARSE #3075330936:c=243962,e=363698,p=33,cr=451,cu=0,mis=1,r=0,dep=0,og=1,plh=2004327352,tim=1403366711465446

EXEC #3075330936:c=0,e=92,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2004327352,tim=1403366711465912

WAIT #3075330936: nam='SQL*Net message to client' ela= 10 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1403366711466062

WAIT #3075330936: nam='db file sequential read' ela= 182 file#=1 block#=93129 blocks=1 obj#=87169 tim=1403366711466584

WAIT #3075330936: nam='db file sequential read' ela= 136 file#=1 block#=93132 blocks=1 obj#=87169 tim=1403366711466948

WAIT #3075330936: nam='db file sequential read' ela= 105 file#=1 block#=93837 blocks=1 obj#=87165 tim=1403366711467256

FETCH #3075330936:c=1000,e=1186,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=2004327352,tim=1403366711467497

WAIT #3075330936: nam='SQL*Net message from client' ela= 716 driver id=1650815232 #bytes=1 p3=0 obj#=87165 tim=1403366711468310

FETCH #3075330936:c=0,e=18,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=2004327352,tim=1403366711468571

STAT #3075330936 id=1 cnt=1 pid=0 pos=1 obj=87164 op='TABLE ACCESS BY GLOBAL INDEX ROWID T_PART PARTITION: 1 1 (cr=4 pr=3 pw=0 time=1235 us cost=2 size=97 card=1)'

STAT #3075330936 id=2 cnt=1 pid=1 pos=1 obj=87169 op='INDEX RANGE SCAN IDX_T_PART_ID (cr=3 pr=2 pw=0 time=771 us cost=1 size=0 card=1)'

WAIT #3075330936: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=87165 tim=1403366711469180

傳統的索引路徑都是從索引段入手，訪問到索引根節點塊，之後逐層向下訪問進行定位，這個過程進行的大都是單塊讀動作。

從Trace File結果，Oracle首先進行了對file#=1 block#=93129 blocks=1，物件編號為87169。

SQL> col object_name for a30;
SQL> select object_name, object_type from dba_objects where object_id=87169;

OBJECT_NAME          OBJECT_TYPE
-------------------  -------------------
IDX_T_PART_ID        INDEX

Oracle對物件87169進行了兩次單塊訪問。一次是段頭塊訪問，另一次是定位到葉子節點。獲取到符合條件的葉子節點rowid之後，就直接回表到資料表指定的塊中。這也就是之後的第三次單塊訪問，編號為87165。

SQL> select object_name, subobject_name, object_type from dba_objects where object_id=87165;

OBJECT_NAME                    SUBOBJECT_ OBJECT_TYPE
------------------------------ ---------- -------------------
T_PART                         T_PART_P0  TABLE PARTITION

在這個過程中，我們沒有看到分割槽裁剪，只看到了普通索引定位檢索過程。我們說：對於全域性索引和分割槽表而言，SQL執行計劃是相斥的。這個實驗中，我們看到了分割槽裁剪被閒置的情況，在另外的一些情況下，是可能出現索引被閒置的情況的。

 

三、 Local Index與表分割槽裁剪

Local Index有兩個特點值得關注。一個是索引段分割槽，從傳統的一個單樹結構，演變到多樹多索引段的結構。另一個是分割槽策略，local索引的分割槽策略（分割槽種類和分割槽鍵）和資料表保持一致。Local Index中除了攜帶的索引欄位資訊外，還包括了分割槽鍵和分割槽條件。Local Index是能夠利用分割槽裁剪和索引雙重特性的解決方案。

SQL> create index idx_t_part_id_local on t_part(object_id) local;
Index created

SQL> explain plan for select * from t_part where owner='SYS' and object_id=1000;
Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2413422785
--------------------------------------------------------------------------------
| Id  | Operation                          | Name                | Rows  | Bytes
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                     |     1 |    97
|   1 |  PARTITION LIST SINGLE             |                     |     1 |    97
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_PART              |     1 |    97
|*  3 |    INDEX RANGE SCAN                | IDX_T_PART_ID_LOCAL |     1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("OBJECT_ID"=1000)

15 rows selected

從PARTITION LIST SINGLE可以看到分割槽裁剪動作，並且執行計劃中也看到了使用索引路徑。

這裡一個問題：資料表和索引都分割槽了，這個裁剪裁剪的是誰？我們通過10046來進行驗證。

SQL> select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------
/u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_2165.trc

SQL> alter system flush shared_pool;
System altered.

SQL> alter system flush buffer_cache;
System altered.

SQL> alter session set events='10046 trace name context forever, level 12';
Session altered.

SQL> select * from t_part where owner='SYS' and object_id=1000;

SQL> alter session set events='10046 trace name context off';
Session altered.

對應的Trace File資訊片段如下：

=====================

PARSING IN CURSOR #3074950008 len=57 dep=0 uid=0 oct=3 lid=0 tim=1403367710144497 hv=919498438 ad='2d9290f8' sqlid='2q5x87wvcwvq6'

select * from t_part where owner='SYS' and object_id=1000

END OF STMT

PARSE #3074950008:c=242963,e=404745,p=37,cr=462,cu=0,mis=1,r=0,dep=0,og=1,plh=2413422785,tim=1403367710144492

EXEC #3074950008:c=0,e=146,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2413422785,tim=1403367710144857

WAIT #3074950008: nam='SQL*Net message to client' ela= 7 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1403367710144940

WAIT #3074950008: nam='db file sequential read' ela= 152 file#=1 block#=93129 blocks=1 obj#=87172 tim=1403367710146094

WAIT #3074950008: nam='db file sequential read' ela= 149 file#=1 block#=93132 blocks=1 obj#=87172 tim=1403367710146520

WAIT #3074950008: nam='db file sequential read' ela= 373 file#=1 block#=93837 blocks=1 obj#=87165 tim=1403367710147080

FETCH #3074950008:c=999,e=1367,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=2413422785,tim=1403367710147177

WAIT #3074950008: nam='SQL*Net message from client' ela= 760 driver id=1650815232 #bytes=1 p3=0 obj#=87165 tim=1403367710148032

FETCH #3074950008:c=0,e=22,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=2413422785,tim=1403367710148339

STAT #3074950008 id=1 cnt=1 pid=0 pos=1 obj=0 op='PARTITION LIST SINGLE PARTITION: KEY KEY (cr=4 pr=3 pw=0 time=1453 us cost=2 size=97 card=1)'

STAT #3074950008 id=2 cnt=1 pid=1 pos=1 obj=87164 op='TABLE ACCESS BY LOCAL INDEX ROWID T_PART PARTITION: 1 1 (cr=4 pr=3 pw=0 time=1422 us cost=2 size=97 card=1)'

STAT #3074950008 id=3 cnt=1 pid=2 pos=1 obj=87171 op='INDEX RANGE SCAN IDX_T_PART_ID_LOCAL PARTITION: 1 1 (cr=3 pr=2 pw=0 time=804 us cost=1 size=0 card=1)'

WAIT #3074950008: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=87165 tim=1403367710148876

 

Oracle直接定位到了object_id=87172，我們來檢視一下這個物件是誰？

SQL> select object_name, subobject_name, object_type from dba_objects where object_id=87172;

OBJECT_NAME                    SUBOBJECT_ OBJECT_TYPE
------------------------------ ---------- -------------------
IDX_T_PART_ID_LOCAL            T_PART_P0  INDEX PARTITION

是索引段的分割槽！說明Oracle進行本地索引分割槽的時候，是進行的索引段的分割槽裁剪。與標準分割槽裁剪相同，Oracle首先利用recursive call獲取到分割槽、分割槽索引的基本後設資料資訊，其中最重要的是分割槽鍵和分割槽資訊。這就提供了Oracle直接就訪問到分割槽索引樹的條件，資料段分割槽也就不需要了。

 

四、結論

分割槽表、Global Index和Local Index，是會影響到管理運維、效能優化的重要組合概念。在SQL層面，儘可能的利用已有的優化策略獲取到最優的效能，是我們決策問題的出發點。所有的優化策略，都需要付出管理、效能的成本付出，以最少的付出，獲取最大的綜合效能，也就是我們優化人員的職分所在。

 

參考

http://blog.itpub.net/17203031/viewspace-1191287/

http://blog.itpub.net/17203031/viewspace-1191341/