在CBO時代，SQL語句的執行計劃完全依賴於在資料字典中儲存的統計量資訊和最佳化器Optimizer的計算公式引數。從9i開始到現在的11gR2，我們說CBO最佳化器已經很成熟和完善。在通常情況下，我們的SQL都是可以獲取到較好的執行計劃以及執行效率的。

在實際工作中，我們經常會遇到執行計劃低效的情況。但是這種故障根源中，絕大多數的原因在於統計量的錯誤或者失效。錯誤的統計量連帶生成的就是不恰當的執行計劃，以至於低效的執行過程。在9i時代，RBO和CBO混合使用，讓我們經常需要自定義的統計量收集過程。

從10g開始，Oracle引入了自動收集統計量的作業，以保證資料字典中統計量正確反映資料物件狀態。這在很大程度上，緩解了由於資料變化導致的統計量過期問題。但是，我們在實際工作中，還是會發現執行計劃的突然變化。究其原因，就是某個時間點收集的統計量，也許不能反映資料的全貌（如中間表）。

1、統計量Pending

在系統運維中，我們常常希望維持SQL執行計劃的穩定。很多DBA和開發人員對於hint的依賴，很大程度上也是源於對CBO情況下，執行計劃對於統計量過於依賴，容易形成不穩定執行計劃。

那麼，我們SQL語句執行計劃的穩定性，就變成統計量的穩定性問題。更進一步，就是新的統計量更新，無論是否手動收集還是自動收集，能否促進SQL語句生成更高效的執行計劃。

所以，一種思路是：在新的統計量收集生成時，暫時不要生效投入執行計劃生成。等待最後確認統計量正確之後，再投入生產環境。

在Oracle 11g中，推出了統計量管理的一種新技術——Pending Statistic技術，提供了這種功能。

簡單的說，我們可以對一系列的資料表設定pending屬性。設定pending屬性之後，資料的統計量在資料字典中相當於已經鎖定Lock住。但新統計量生成之後，不是直接替換原有的資料，而是存放在pending資料字典中。

在pending字典中的統計量，預設情況下是不會參與SQL執行計劃的生產的。只有在進行SQL測試透過的時候，經過使用者手工的確定，才會將其Publish出來，替換原有的統計量資訊。

這樣，就給我們運維DBA一種維持執行計劃穩定的思路。透過固定統計量，將新統計量pending的方式將原有的統計量固定，從而穩定執行計劃。進而，對pending的統計量進行測試，只有在更好執行計劃的情況下，才會替換原有的方案。

下面，我們透過實驗來驗證pending統計量的使用。

2、實驗環境構建

我們選擇11gR2進行實驗。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

構建資料表T，以及對應的索引。注意，我們首先在資料表中不儲存任何資料。

SQL> create table t as select * from dba_objects where 1=0;

Table created

SQL> create index idx_t_owner on t(owner);

Index created

SQL> create index idx_t_id on t(object_id);

Index created

在不顯式的收集統計量的情況下，是沒有對應的資料表統計量的。

SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name='T';

NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN

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

SQL> select count(*) from user_tab_col_statistics where table_name='T';

COUNT(*)

----------

0

SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR NUM_ROWS from user_ind_statistics where index_name='IDX_T_OWNER';

BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS

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

0 0 0 0

收集統計量，獲取最新的資料分佈狀況。

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

當我們修改資料內容，沒有收集統計量，會存在新舊差異。

SQL> insert into t select * from dba_objects;

72202 rows inserted

SQL> commit;

Commit complete

SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name='T';

NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN

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

0 0 0 0 0

3、Pending Statistics設定

在11g環境中，資料表、Schema都存在一個統計量相關引數PUBLISH，表示當有新統計量的時候，新統計量是否立即被publish出來，作為最新的統計資訊使用。

該引數的預設值為TRUE。

SQL> select dbms_stats.get_prefs(pname => 'PUBLISH',ownname => 'SYS',tabname => 'T') from dual;

DBMS_STATS.GET_PREFS(PNAME=>'P

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

TRUE

--設定資料表的publish引數取值；

SQL> exec dbms_stats.set_table_prefs(user,'T','PUBLISH','false');

PL/SQL procedure successfully completed

SQL> select dbms_stats.get_prefs('PUBLISH',ownname => 'SYS',tabname => 'T') from dual;

DBMS_STATS.GET_PREFS('PUBLISH'

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

FALSE

此時，資料表中已經包括了七萬餘條資料，重新收集統計量。

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name='T';

NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN

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

0 0 0 0 0

當我們將資料表T的PUBLISH引數修改為false之後，我們重新收集統計量，發現原有統計資訊並沒有連帶的更新。

新統計量不是沒有收集，而是被記錄在了pending資訊中。我們可以透過user_ind_pending_stats和user_tab_pending_stats兩個檢視檢視被pending的統計量資訊。

SQL> select NUM_ROWS, BLOCKS, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED from user_tab_pending_stats where table_name='T';

NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED

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

72202 1028 97 72202 2012/6/20 20:

SQL> select index_name, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR,LAST_ANALYZED from user_ind_pending_stats where table_name='T';

INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR LAST_ANALYZED

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

IDX_T_OWNER 293 23 1884 2012/6/20 20:

IDX_T_ID 256 72202 1665 2012/6/20 20:

4、Pending和SQL執行計劃

新的統計量沒有被publish出來。那麼，在一般情況下，我們的SQL執行計劃還是依據正式被publish的統計量生成。

SQL> explain plan for select * from t where wner='SYS';

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1516787156

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

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

| 0 | SELECT STATEMENT | | 1 | 207 | 1 (0)|

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 1 (0)|

|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 1 | | 1 (0)|

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

Predicate Information (identified by operation id):

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

2 - access("OWNER"='SYS')

14 rows selected

實際執行情況；

SQL> select * from t where wner='SYS';

已選擇58799行。

已用時間: 00: 00: 06.19

執行計劃

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

Plan hash value: 1516787156

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

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

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

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

|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("OWNER"='SYS')

統計資訊

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

528 recursive calls

0 db block gets

8962 consistent gets

1108 physical reads

0 redo size

6291375 bytes sent via SQL*Net to client

43520 bytes received via SQL*Net from client

3921 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

58799 rows processed

SQL>

在sys使用者下，行數比例超過了資料表T的絕大多數。按照CBO的原則，走全表掃描可能是較好的方法。但是，由於統計量還是在空表的狀態下，所以，Oracle CBO認為Index路徑會更好。

在Oracle中，存在一個引數optimizer_use_pending_statistics，用來控制當前是否使用pending的統計量來生成執行計劃。作為運維DBA，可以透過這個引數暫時性的啟用pending統計量，觀察一下效能狀況。再決定是否啟用publish這些統計量。

預設情況下，該引數取值為false。我們可以在session級別設定下該引數為true。

SQL> show parameter optimizer_use_pending

NAME TYPE VALUE

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

optimizer_use_pending_statistics boolean FALSE

修改引數為true之後，Oracle CBO在生成執行計劃的時候就會使用Pending的統計量。

SQL> alter session set optimizer_use_pending_statistics=true;

Session altered

SQL> select value from v$parameter where name='optimizer_use_pending_statistics';

VALUE

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

TRUE

SQL> explain plan for select * from t where wner='SYS';

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 58274 | 5463K| 281 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T | 58274 | 5463K| 281 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - filter("OWNER"='SYS')

13 rows selected

SQL> select * from t where wner='SYS';

已選擇58799行。

已用時間: 00: 00: 04.68

執行計劃

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 58274 | 5463K| 281 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T | 58274 | 5463K| 281 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - filter("OWNER"='SYS')

統計資訊

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

7511 recursive calls

50 db block gets

6599 consistent gets

1118 physical reads

0 redo size

2392962 bytes sent via SQL*Net to client

43520 bytes received via SQL*Net from client

3921 SQL*Net roundtrips to/from client

211 sorts (memory)

0 sorts (disk)

58799 rows processed

果然，設定引數後，Oracle生成了FTS路徑，說明更新的統計量起了作用。同時，執行時間減少了近2秒鐘，說明結果上也確實是生成了更好的執行計劃。

5、Pending統計量的後續處理

在對pending統計量進行合理評估之後，DBA是可以做出刪除還是釋出統計量的決定的。具體操作如下：

--刪除pending資訊

SQL> exec dbms_stats.delete_pending_stats(user,'T');

PL/SQL procedure successfully completed

SQL> select count(*) from user_tab_pending_stats;

COUNT(*)

----------

0

--重新收集pending統計量

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name='T';

NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN

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

0 0 0 0 0

--釋出pending統計量

SQL> exec dbms_stats.publish_pending_stats(user,'T');

PL/SQL procedure successfully completed

SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name='T';

NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN

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

72202 1028 0 0 96

單釋出完統計量之後，就可以在正常的情況下使用統計量生成執行計劃了。

SQL> show parameter optimizer_use_pen

NAME TYPE VALUE

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

optimizer_use_pending_statistics boolean FALSE

SQL> alter session set optimizer_use_pending_statistics=false;

會話已更改。

已用時間: 00: 00: 00.01

SQL> select * from t where wner='SYS';

已選擇58799行。

已用時間: 00: 00: 04.33

執行計劃

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 58794 | 5511K| 281 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T | 58794 | 5511K| 281 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - filter("OWNER"='SYS')

統計資訊

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

426 recursive calls

0 db block gets

4975 consistent gets

0 physical reads

0 redo size

2392962 bytes sent via SQL*Net to client

43520 bytes received via SQL*Net from client

3921 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

58799 rows processed

6、結論

在11g中提出的pending statistic的方法，可以在生產運維和穩定最佳化執行計劃方面，給我們提供幫助。

Oracle 11g 資料統計量Pending處理

相關文章