ORACLE統計資訊與執行計劃

10G之後統計資訊收集後為什麼執行計劃不會被立馬淘汰

在10G之前，使用DBMS_STATS收集統計資訊将會導緻與此對象相關的遊标失效，下次執行此

的時候将會進行HARD PARSE，除非收集的時候NO_INVALIDATE設定為TRUE。

由于硬解析會消耗大量的CPU，還會導緻大量的library cache 和 shared pool 的LATCH競争，是以

如果由于統計資訊收集導緻大量的的遊标失效，可能會帶來HARD PARSE風暴，造成系統的負擔。

但是如果采用NO_INVALIDATE=TRUE的方法，由于遊标不失效，遊标無法利用到新的統計資訊，

除非下一次進行HARD PARSE，譬如CURSOR RELOAD,手工FLUSH SHARED POOL，cursor aged out.

從10G開始，DBMS_STATS.GATHER_TABLE_STATS過程 NO_INVALIDATE 參數提供了一個AUTO_INVALIDATE選項，這個參數讓使用者

在統計資訊收集後，控制什麼時候遊标失效。

NO_INVALIDATE 這個參數有一下3個選項：

TRUE: does not invalidate the dependent cursors

FALSE: invalidates the dependent cursors immediately

AUTO_INVALIDATE (default): have Oracle decide when to invalidate dependent cursors

AUTO_INVALIDATE選項使得遊标失效的時間得以控制，進而避免了HARD PARSE的風暴。

有了這個選項，當統計資訊收集後，遊标按照如下的方式進行何時失效：

1、當對象的統計資訊被修改後，依賴于此對象的目前CACHED CURSORS被标記為rolling invalidation，此時假設時間為T1.

2、下一次，當SESSION進行PARSE上面被标記為rolling invalidation的CURSOR的時候，記錄時間戳T2，這個時間戳加上參數

_optimizer_invalidation_period(以秒為機關，預設是18000秒，5個小時)的值， 就作為此遊标的失效時刻TMAX。這次PARSE還是會共享遊标，

進行SOFT PARSE，不會利用到新的統計資訊。

3、在随後的遊标PARSE時，ORACLE會檢查目前的時刻是否超出了TMAX時間。如果沒有，還會利用原來的遊标，如果超出了，

ORACLE會進行HARD PARSE，利用最新的統計資訊産生一個子遊标，同時在V$SQL_SHARED_CURSOR記錄不能共享的原因，即ROLL_INVALID_MISMATCH

被設定為YES.

從上面也可以看出：

如果一個遊标在被标記為rolling invalidation,後面再也沒有進行過PARSE，那麼這個遊标也不會被invalidated，當然可以手工的FLUSH出去，

或者記憶體不夠的時候,通過LRU算法淘汰出去。

如果一個遊标在被标記為rolling invalidation，僅僅進行過一次PARSE，那麼這個遊标也不會被invalidated,因為第一次僅僅記錄一個時間戳。

遊标需要在第二次或者第N次的時候去進行判斷遊标是否被invalidated。

說了這麼多，看下面一個示範：

Microsoft Windows XP [版本 5.1.2600]

(C) 版權所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\htaix>SQLPLUS PLSQL/PLSQL

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 1月 10 11:16:43 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

連接配接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> SET TIME ON

11:17:20 SQL>

11:17:21 SQL> SELECT COUNT(1) FROM T;

 COUNT(1)

----------

    49777

11:19:13 SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

會話已更改。

11:19:27 SQL> SELECT EXECUTIONS,OBJECT_STATUS,INVALIDATIONS,LAST_ACTIVE_TIME

11:19:32   2  FROM V$SQL WHERE SQL_TEXT='SELECT COUNT(1) FROM T';

EXECUTIONS OBJECT_STATUS       INVALIDATIONS LAST_ACTIVE_TIME

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

        1 VALID                           0 2013-01-10 11:17:26

11:19:33 SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL,'T',NO_INVALIDATE=>FALSE);

PL/SQL 過程已成功完成。

11:20:24 SQL> SELECT EXECUTIONS,OBJECT_STATUS,INVALIDATIONS,LAST_ACTIVE_TIME

11:20:27   2  FROM V$SQL WHERE SQL_TEXT='SELECT COUNT(1) FROM T';

未標明行

11:20:27 SQL>

NO_INVALIDATE=>FALSE方式會導緻遊标立馬失效，這也是9I的預設行為。

11:28:27 SQL> ALTER SYSTEM FLUSH SHARED_POOL;

系統已更改。

11:29:02 SQL> alter system set "_optimizer_invalidation_period"=120;

11:29:06 SQL> select last_analyzed from user_tables where table_name='T';

LAST_ANALYZED

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

2013-01-10 11:25:48

11:29:34 SQL> select count(1) from t;

11:29:47 SQL> select sql_id from v$sql where sql_text='select count(1) from t';

SQL_ID

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

1pvh3df63vc4h

11:30:05 SQL> select * from v$sql_shared_cursor where sql_id='1pvh3df63vc4h';

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L

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

1pvh3df63vc4h 6A4879E8 692CCD1C            0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

11:30:40 SQL> select child_number,parse_calls,executions,first_load_time,

11:31:22   2  last_load_time,last_active_time from v$sql where sql_id='1pvh3df63vc4h';

CHILD_NUMBER PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME                        LAST_LOAD_TIME                 LAST_ACTIVE_TIME

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

          0           1          1 2013-01-10/11:29:45                    2013-01-10/11:29:45                2013-01-10 11:29:44

我們看到一個子遊标在11:29:44被執行。

11:31:23 SQL> select count(1) from t;

11:33:34 SQL> select child_number,parse_calls,executions,first_load_time,

11:33:50   2  last_load_time,last_active_time from v$sql where sql_id='1pvh3df63vc4h';

          0           2          2 2013-01-10/11:29:45                    2013-01-10/11:29:45                2013-01-10 11:33:32

這次我們看到子遊标執行了2次，同時最後更新時間為11:33:32.

下面我們進行統計資訊收集， 預設的選項就是AUTO_INVALIDATE。

11:33:51 SQL> exec dbms_stats.gather_table_stats(null,'T');

11:35:39 SQL> select last_analyzed from user_tables where table_name='T';

2013-01-10 11:35:19

11:36:05 SQL> select * from v$sql_shared_cursor where sql_id='1pvh3df63vc4h'

11:36:16   2  ;

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F

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

1pvh3df63vc4h 6A4879E8 692CCD1C            0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

11:36:34 SQL> select child_number,parse_calls,executions,first_load_time,

11:36:41   2  last_load_time,last_active_time from v$sql where sql_id='1pvh3df63vc4h';

11:36:41 SQL> select count(1) from t;

11:37:13 SQL> select * from v$sql_shared_cursor where sql_id='1pvh3df63vc4h';

11:37:32 SQL> select child_number,parse_calls,executions,first_load_time,

11:37:38   2  last_load_time,last_active_time from v$sql where sql_id='1pvh3df63vc4h';

          0           3          3 2013-01-10/11:29:45                    2013-01-10/11:29:45                2013-01-10 11:37:11

統計資訊被更新後，上面的select count(1) from t會進行一次SOFT PARSE，我們在PARSE_CALLS列可以看到，EXECUTIONS和LAST_ACTIVE_TIME

都被更新，同時遊标會被标記為rolling invalidation，這個時候即使時間超出了_optimizer_invalidation_period設定的值也不會導緻遊标

失效，統計資訊收集後的第一次PARSE僅僅是記錄時間戳。

我們大約稍等2分鐘。

在等待了2分鐘後，我們重新執行select count(1) from t。

我們先查詢一下目前遊标情況：

11:42:54 SQL> select * from v$sql_shared_cursor where sql_id='1pvh3df63vc4h';

11:43:03 SQL> select child_number,parse_calls,executions,first_load_time,

11:43:08   2  last_load_time,last_active_time from v$sql where sql_id='1pvh3df63vc4h';

11:43:09 SQL> select count(1) from t;  --這個SQL将導緻遊标失效

這次SQL的執行将會進行檢查時間是否超出了_optimizer_invalidation_period設定的值，如果超出了就會進行HARD PARSE，否則還是SOFT PARSE。

11:43:32 SQL> select * from v$sql_shared_cursor where sql_id='1pvh3df63vc4h';

1pvh3df63vc4h 6A4879E8 6B3A363C            1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N

11:43:42 SQL> select child_number,parse_calls,executions,first_load_time,

11:43:48   2  last_load_time,last_active_time from v$sql where sql_id='1pvh3df63vc4h';

          1           1          1 2013-01-10/11:29:45                    2013-01-10/11:43:21                2013-01-10 11:43:20

11:43:49 SQL>

我們可以看到一個新的子遊标表被建立，并且被執行了一次，而老的遊标執行了3次，先前的遊标已經不能再被共享。

v$sql_shared_cursor同時記錄了不能共享的原因，後續的SQL将會共享新的遊标，如下：

11:43:49 SQL> select count(1) from t;

12:52:55 SQL> select * from v$sql_shared_cursor where sql_id='1pvh3df63vc4h';

12:53:06 SQL> select child_number,parse_calls,executions,first_load_time,

12:53:08   2  last_load_time,last_active_time from v$sql where sql_id='1pvh3df63vc4h';

          1           2          2 2013-01-10/11:29:45                    2013-01-10/11:43:21                2013-01-10 12:52:54

上面的測試環境是WINDOWS 10.2.0.1單執行個體。

SQL> SELECT * FROM V$VERSION;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

上面的情況有個例外，如果SQL采用了并行并且跨了RAC的多個執行個體，那麼統計資訊收集後，遊标立即失效。

以下是官方說法：

parallel SQL are immediately invalidated in order to ensure consistency between execution plans of slaves and Query Coordinator

across multiple RAC instances. This is not a problem as parallel SQL are usually heavy and therefore hard-parse resources

are insignificant to their total resource usage.

簡單總結以下：從10G開始，統計資訊收集的時候如果NO_INVALIDATE采用的AUTO_INVALIDATE(預設情況下就是AUTO_INVALIDATE)，

那麼統計資訊收集後，與此對象相關的遊标不會裡面失效。下一次PARSE的時候将會重用這個遊标，并且記錄一個時間點T1，

後續的PARSE的時候将會對比目前時間戳T2和T1的間隔是否超出了_optimizer_invalidation_period設定的值，

如果沒有超出将會進行SOFT PARSE，後面的PARSE繼續檢驗是否超出了_optimizer_invalidation_period設定的值

如果超出了就會進行HARD PARSE，否則将還會進行SOFT PARSE，一直循環下去，直到遊标失效或者被AGED OUT出去。

PS：一旦執行了DBMS_STATS.GATHER_TABLE_STATS 如果NO_INVALIDATE=>FALSE 那麼以前的子CURSOR會立即被重新整理出SHARE POOL。不管統計資料發不發生變化，下次執行時都會産生一次hard parse、

而使用DBMS_STATS.AUTO_INVAILDATE也一樣不管統計資料發不發生變化，下次執行時都會産生一次hard parse。這時會顯示V$SQL中該語句會有2條記錄。如果資料不變那麼他的PLAN_HASH_VALUE不會發生改變(執行機會不變），但CHILD_ADDRESS會變化（可見生成了新的CURSOR，發生了硬解析）。如果資料變化則二者都會變化。