---

---

11gr2開始，可以使用下面型別的操作來收集優化器統計資訊：
1.             自動釋出收集的統計資訊在收集操作結束以後（預設選項publish）
2.             儲存新的統計資訊，並且待定（暫不釋出pending）
這個特性可以將新收集的統計資訊置為待定狀態，所以可以先驗證新統計資訊的有效性然後再發布。
可以使用下面的命令來檢視是否預設釋出新的統計資訊。
sys@DAVID> SELECTDBMS_STATS.GET_PREFS('PUBLISH') PUBLISH FROM DUAL;


PUBLISH
----------------------------------------------------------------------------------------------------
TRUE


返回為true或者false。True表示新的統計資訊收集後即釋出，也就是說優化器會使用新的統計資訊來生查詢計劃，False表示收集的統計資訊會被放入USER_TAB_PENDING_STATS和 USER_IND_PENDING_STATS，並且不會立刻被優化器使用，為待定狀態。


可以使用下面的包來改變各個級別(global,schema,table)的預設publish選項。
Global
exec Dbms_stats.set_global_prefs(pname =>'PUBLISH' ,pvalue=> 'FALSE') ;


Schema
exec dbms_stats.set_schema_prefs(ownname => 'DEXTER',pname=>'PUBLISH' ,pvalue => 'TRUE') ;


table
Exec dbms_stats.set_table_prefs('DEXTER', 'PUBLISH_TEST','PUBLISH', 'false');


假設你執行了上面的關於table的操作，那麼關於schema dexter 上publish_test表的統計資訊收集以後就不會立刻應用於優化器上面，而是先置於USER_TAB_PENDING_STATS表裡面為待定狀態。
設定好預設的publish選項之後，就可以開始驗證新統計資訊了。
預設的優化器會使用已經發布的存放在資料字典裡面的統計資訊，可以通過更改初始化引數OPTIMIZER_USE_PENDING_STATISTICS來設定優化器使用哪一種型別的統計資訊（published or pending），比如使用下面的操作來更改session級別的優化器統計資訊來源（不要寫成alter system了）。
alter session set optimizer_use_pending_statistics = TRUE;


這樣在session級別內就可以使用待定的統計資訊來編譯sql語句並且生成查詢計劃，如果新的統計資訊已經被驗證，那麼可以使用下面的語句釋出統計資訊。
Execdbms_stats.publish_pending_stats('DEXTER','PUBLISH_TEST');
如果不想使用新的統計資訊，那麼可以使用下面的語句去刪除。
Execdbms_stats.delete_pending_stats('DEXTER','PUBLISH_TEST');


也可以使用dbms_stats.export_pending_stats將待定的統計資訊匯出，並且匯入到測試系統上面執行一個全面的負載測試，以確定問題的根源。
下面是一個完整的示例：
建立測試表

_dexter@DAVID> createtable publish_test (id number , name varchar2(20) ) ;

Table created.

插入資料

_dexter@DAVID> insertinto publish_test select level , 'name' || level from dual connect by level<= 10000 ;

10000 rows created.

_dexter@DAVID> commit ;

Commit complete.

建立索引

_dexter@DAVID> createindex idx_publish_test_id on publish_test(id) ;

Index created.

收集統計資訊

_dexter@DAVID> execdbms_stats.gather_table_stats('DEXTER','PUBLISH_TEST') ;

PL/SQL procedure successfully completed.

檢視一下歷史統計資訊（這個表中只顯示已經發布過的統計資訊）

_dexter@DAVID> selecth.table_name, to_char(h.STATS_UPDATE_TIME, 'yyyymmddhh24miss')

  2   from user_TAB_STATS_HISTORY h

  3  where h.table_name = 'PUBLISH_TEST';

TABLE_NAME                    TO_CHAR(H.STAT

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

PUBLISH_TEST                  20121120161308

進行一個簡單查詢，可以看到，走索引的效率還是比較高的

_dexter@DAVID> set autotrace on

_dexter@DAVID> select p.id,p.name from publish_test p whereid=1 ;

        ID NAME

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

         1 name1

Execution Plan

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

Plan hash value: 1085097009

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

| Id  | Operation                   | Name                | Rows | Bytes | Cost (%CPU)| Time       |

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

|   0 | SELECTSTATEMENT            |                     |     1 |   13 |     2   (0)| 00:00:01 |

|   1 | TABLE ACCESS BY INDEX ROWID| PUBLISH_TEST        |    1 |    13 |     2  (0)| 00:00:01 |

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

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

Predicate Information (identified by operation id):

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

   2 -access("ID"=1)

Statistics

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

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

        596  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

設定一下表的publish選項

_dexter@DAVID>  Exec dbms_stats.set_table_prefs('DEXTER','PUBLISH_TEST', 'PUBLISH', 'false');

PL/SQL procedure successfully completed.

_dexter@DAVID> selectdbms_stats.get_prefs('PUBLISH','DEXTER','PUBLISH_TEST') FROM DUAL ;

DBMS_STATS.GET_PREFS('PUBLISH','DEXTER','PUBLISH_TEST')

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

FALSE

再次向表中插入資料

_dexter@DAVID> insertinto publish_test(id,name) select 1, 'name' || level from dual connect by level<= 10000 ;

10000 rows created.

_dexter@DAVID> commit ;

Commit complete.

在沒有再次收集統計資訊之前檢視一下執行計劃，可以看到，依舊使用舊的統計資訊

_dexter@DAVID> select p.id,p.name from publish_test p whereid=1 ;

Execution Plan

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

Plan hash value: 1085097009

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

| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time        |

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

|   0 | SELECTSTATEMENT            |                     |     1 |   13 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID|PUBLISH_TEST        |     1 |   13 |     2   (0)| 00:00:01 |

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

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

Predicate Information (identified by operation id):

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

   2 -access("ID"=1)

Statistics

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

          0  recursive calls

          0  db block gets

       1424  consistent gets

          0 physical reads

       2644  redo size

     293416  bytes sent via SQL*Net to client

       7850  bytes received via SQL*Net from client

        668  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      10001  rows processed

再次收集一下統計資訊，這個時候收集的統計資訊不會立刻被優化器使用

_dexter@DAVID>  execdbms_stats.gather_table_stats('DEXTER','PUBLISH_TEST') ;

PL/SQL procedure successfully completed.

如所料，這裡還是使用舊的統計資訊，依舊使用index rangescan 代價比較高

_dexter@DAVID> select p.id,p.name from publish_test p whereid=1 ;

Execution Plan

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

Plan hash value: 1085097009

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

| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time        |

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

|   0 | SELECTSTATEMENT            |                     |     1 |   13 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID|PUBLISH_TEST        |     1 |   13 |     2   (0)| 00:00:01 |

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

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

Predicate Information (identified by operation id):

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

   2 -access("ID"=1)

Statistics

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

          0  recursive calls

          0  db block gets

       1391  consistent gets

          0  physical reads

          0  redo size

     293416  bytes sent via SQL*Net to client

       7850  bytes received via SQL*Net from client

        668  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      10001  rows processed

看一下統計資訊的情況，已經發布的統計資訊還是比較老的，而如下所示pending表裡面的統計資訊表示新收集的待定的統計資訊

_dexter@DAVID> select 'publish' as stat,t.NUM_ROWS,t.BLOCKS,to_char(t.LAST_ANALYZED,'yyyymmddhh24miss')  from USER_TAB_STATISTICS t  where table_name='PUBLISH_TEST'

  2  union

  3  select 'pending' as stat,s.num_rows,s.blocks,to_char(s.LAST_ANALYZED,'yyyymmddhh24miss') fromUSER_TAB_PENDING_STATS s where table_name='PUBLISH_TEST'

  4  ;

STAT      NUM_ROWS     BLOCKS TO_CHAR(T.LAST

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

pending      20000         50 20121120162534

publish      10000         28 20121120161308

下面我們來驗證一下新的統計資訊是否有助於改善sql語句的執行

_dexter@DAVID>  alter session setoptimizer_use_pending_statistics = TRUE;

Session altered.

可以看到，使用優化器使用待定的統計資訊生成的查詢計劃使用的是全表掃描，更加有效率

_dexter@DAVID> select p.id,p.name from publish_test p whereid=1 ;

Execution Plan

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

Plan hash value: 3346034967

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

| Id  | Operation         | Name         | Rows | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECTSTATEMENT  |              | 9921 |   116K|    15  (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| PUBLISH_TEST |  9921 |  116K|    15   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 -filter("ID"=1)

Statistics

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

        148  recursive calls

          0  db block gets

        750  consistent gets

          0  physical reads

          0  redo size

     261413  bytes sent via SQL*Net to client

       7850  bytes received via SQL*Net from client

        668  SQL*Net roundtrips to/from client

          3  sorts (memory)

          0  sorts (disk)

      10001  rows processed

驗證結束，無誤，可以釋出新的統計資訊了

_dexter@DAVID> Execdbms_stats.publish_pending_stats('DEXTER','PUBLISH_TEST');

PL/SQL procedure successfully completed.

_dexter@DAVID> altersession set optimizer_use_pending_statistics = false;

Session altered.

可以看到pending的統計資訊已經發布並且從user_tab_pending_stats表中刪除，user_tab_statistics表中的last_analyzed時間顯示的是統計資訊收集的時間

_dexter@DAVID> select 'publish' as stat ,t.NUM_ROWS,t.BLOCKS,to_char(t.LAST_ANALYZED,'yyyymmddhh24miss')  from USER_TAB_STATISTICS t  where table_name='PUBLISH_TEST'

  2  union

  3  select 'pending' as stat,s.num_rows,s.blocks,to_char(s.LAST_ANALYZED,'yyyymmddhh24miss') fromUSER_TAB_PENDING_STATS s where table_name='PUBLISH_TEST'

  4  ;

STAT      NUM_ROWS     BLOCKS TO_CHAR(T.LAST

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

publish      20000         50 20121120162534

可以看到user_tab_stats_history表中的stats_update_time收集的是統計資訊釋出的時間

_dexter@DAVID> select h.table_name,to_char(h.STATS_UPDATE_TIME, 'yyyymmddhh24miss')

  2   from user_TAB_STATS_HISTORYh

  3   where h.table_name = 'PUBLISH_TEST';

TABLE_NAME                    TO_CHAR(H.STAT

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

PUBLISH_TEST                   20121120161308

PUBLISH_TEST                  20121120163017

好驗證結束

如果已經發布了統計資訊，想要恢復從前的統計資訊，可以根據user_TAB_STATS_HISTORY中的stats_update_time，來確定timestamp，執行下面的操作，最後一個引數as_of_timestamp指的是恢復在這個時間點生效的統計資訊，所以不能寫20121120161308因為在這個時間點內它的統計資訊是空的

SQL> execdbms_stats.restore_table_stats(ownname => 'DEXTER',tabname =>'PUBLISH_TEST',as_of_timestamp => to_date('20121120161309','yyyymmddhh24miss'));

PL/SQL procedure successfully completed

_dexter@DAVID> select 'publish' as stat,t.NUM_ROWS,t.BLOCKS,to_char(t.LAST_ANALYZED,'yyyymmddhh24miss')  from USER_TAB_STATISTICS t  where table_name='PUBLISH_TEST'

  2  union

  3  select 'pending' as stat,s.num_rows,s.blocks,to_char(s.LAST_ANALYZED,'yyyymmddhh24miss') fromUSER_TAB_PENDING_STATS s where table_name='PUBLISH_TEST' ;

STAT      NUM_ROWS     BLOCKS TO_CHAR(T.LAST

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

publish      10000         28 20121120161308

_dexter@DAVID> select h.table_name,to_char(h.STATS_UPDATE_TIME, 'yyyymmddhh24miss')

  2    from user_TAB_STATS_HISTORY h

  3   where h.table_name = 'PUBLISH_TEST';

TABLE_NAME                    TO_CHAR(H.STAT

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

PUBLISH_TEST                  20121120161308

PUBLISH_TEST                  20121120163017

PUBLISH_TEST                  20121120165341

附錄

dbms_stats.restore_table_stats引數說明

--

-- This procedure enables the user to restore statisticsof a table as of

-- a specified timestamp (as_of_timestamp). The procedurewill restore

-- statistics of associated indexes and columns as well.If the table

-- statistics were locked at the specified timestamp theprocedure will

-- lock the statistics.

-- Note:

--   The proceduremay not restore statistics correctly if analyze interface

--   is used forcomputing/deleting statistics.

--   Old statisticsversions are not saved when SYSAUX tablespace is

--   offline, thisaffects restore functionality.

--   The proceduremay not restore statistics if the table defn is

--   changed (eg:column added/deleted, partition exchanged etc).

--   Also it willnot restore stats if the object is created after

--   the specifiedtimestamp.

--   The procedurewill not restore user defined statistics.

-- Input arguments:

--   ownname  - schema of table for which statistics to berestored

--   tabname  - table name

--   as_of_timestamp- statistics as of this timestamp will be restored.

--  restore_cluster_index - If the table is part of a cluster,

--     restorestatistics of the cluster index if set to TRUE.

--   force -restore statistics even if the table statistics are locked.

--           if thetable statistics were not locked at the specified

--          timestamp, it will unlock the statistics

--   no_invalidate- Do not invalide the dependent cursors if set to TRUE.

--      Theprocedure invalidates the dependent cursors immediately

--      if set toFALSE.

--      Theprocedure invalidates the dependent cursors immediately

--      if set toFALSE.

--      UseDBMS_STATS.AUTO_INVALIDATE to have oracle decide when to

--      invalidatedependend cursors. This is the default. The default

--      can bechanged using set_param procedure.

--

-- Exceptions:

--   ORA-20000:Object does not exist or insufficient privileges

--   ORA-20001:Invalid or inconsistent values

--   ORA-20006: Unable to restorestatistics , statistics history not available

---

---

在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
------------------------------------------------------------------------------
| Id | Operation                  | Name       | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|  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
-------------------------------------------------------------------------------
| Id | Operation                  | Name       | Rows | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------
|  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
--------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|  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
--------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|  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
--------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|  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的方法，可以在生產運維和穩定優化執行計劃方面，給我們提供幫助。