Oracle收集統計資訊之NO_INVALIDATE引數

Oracle統計量對於CBO執行是至關重要的。RBO是建立在資料結構的基礎上的，DDL結構、約束會將SQL語句分為不同的成本結構等級。而CBO是在資料結構的基礎上，加入資料表細粒度資訊，將成本結構細化為成本cost值。

相對於資料表的DDL結構，統計量反映了當下資料表資料分佈情況，可變性更強。我們經常遇到這樣的場景，資料匯入操作之後，原有一個執行良好的作業突然效率低下。當我們手工收集一下統計量之後，作業效率提升。這種現象也就是反映了統計量和執行計劃的關係。

SGA中的shared pool是進行執行計劃快取的位置。Shared Cursor是SQL語句共享的主要物件。一句SQL語句，如果在Shared Pool中有快取的執行計劃。這個時候，有新的統計量收集動作，有新統計量收集到資料字典中，進而以為了新的執行計劃需求。那麼，Oracle是如何進行抉擇呢？

答案就是dbms_stats的no_invalidate引數。通過不同的引數配置，可以實現對Oracle失效共享遊標行為的控制。

1、no_invalidate引數

No_invalidate引數從字面上比較糾結。No和in都是否定含義，“負負得正”。引數含義就是validate，也就是是否有效。它決定了新統計量生成之後，如何處理此時已經生成的執行計劃，也就是在Shared Pool中的執行計劃。

統計量決定SQL執行計劃，是CBO的一個特徵。但是這個過程是針對新生成的執行計劃，也就是新的Parse過程。對於已經生成的執行計劃，Oracle是通過no_invalidate引數來處理shared cursor的失效過程。

一個物件（資料表、索引）新統計量生成之後，最簡單的方法是一次性將在Shared Pool中有依賴關係的shared cursor失效。下一次再進行SQL執行的時候，必然會用新的執行計劃Parse解析過程。另一個極端是無視新統計量的差異，維持現有的Shared Cursor，不會去讓其失效。

從效能角度看，兩個極端都是有其問題的。如果是一次性將其全部失效，會引起後續作業過程的“解析峰值”。因為，如果系統負載比較高，突然間快取的執行計劃全部被失效，Oracle作業必然要進行一些額外的成本進行執行計劃重新生成。這個會體現在系統執行有一個峰值。

如果不將共享遊標失效，那麼新的統計量不會很快體現在更好執行計劃生成的過程。效能提升無從談起。

所以，是否將遊標失效，是一個“左右為難”的問題。

在Oracle中，no_invalidate引數包括三個取值。

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

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

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

-- The procedure invalidates the dependent cursors immediately

-- if set to FALSE.

-- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to

-- invalidate dependend cursors. This is the default. The default

-- can be changed using set_param procedure.

-- When the 'cascade' argument is specified, not pertinent with certain

-- types of indexes described in the gather_index_stats section.

Oracle支援true、false和dbms_stats.auto_invalidate取值。如果取值為true，表示不進行遊標失效動作，原有的shared cursor保持原有狀態。如果取值為false，表示將統計量物件相關的所有cursor全部失效。如果設定為auto_invalidate，根據官方文件，Oracle自己決定shared cursor失效動作。

從10G開始，Oracle就將auto_invalidate作為預設的統計量收集行為。

SQL> select dbms_stats.get_param(pname => 'no_invalidate') from dual;

DBMS_STATS.GET_PARAM(PNAME=>'N

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

DBMS_STATS.AUTO_INVALIDATE

下面，筆者將通過一系列的實驗，來證明no_invalidate引數取值的效果。

2、no_invalidate取值為YES

取值為YES，表示不經心共享遊標失效動作，即使這個過程中，共享的遊標已經不是最優的執行計劃。

我們建立實驗資料表。

SQL> create table t as select * from dba_objects;

Table created

SQL> create index idx_t_id on t(object_id);

Index created

--第一次統計量收集

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

PL/SQL procedure successfully completed

目標SQL語句，注意：出於篇幅原因，筆者將結果遮蔽。

SQL> select /*+demo*/object_id, owner from t where object_id=1000;

統計資訊

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

164 recursive calls

0 db block gets

23 consistent gets

0 physical reads

(有省略……)

1 rows processed

此時shared pool中情況如下，出現第一個執行計劃快取物件。

SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+demo*/%';

SQL_ID EXECUTIONS VERSION_COUNT

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

cnb0ktgvms6vq 1 1

SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'cnb0ktgvms6vq'));

PLAN_TABLE_OUTPUT

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

SQL_ID cnb0ktgvms6vq, child number 0

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

select /*+demo*/object_id, owner from t where object_id=1000

Plan hash value: 514881935

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)|

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 11 | 2 (0)| 00

|* 2 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 (0)| 00

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"=1000)

19 rows selected

此時，最優的執行計劃是索引路徑。在shared pool中有一個父遊標和子游標（version count=1），執行次數為1。

第二次執行之後，Shared Pool中有共享現象。相同的共享遊標執行兩次。

SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+demo*/%';

SQL_ID EXECUTIONS VERSION_COUNT

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

cnb0ktgvms6vq 2 1

之後，我們更新資料，修改資料分佈結構。

SQL> update t set object_id=1000;

72729 rows updated

SQL> commit;

Commit complete

此時，如果執行SQL語句，我們發現依然是使用原有的索引路徑。此時全部T表中object_id都是1000，走索引不是好的選擇。

SQL> select /*+demo*/object_id, owner from t where object_id=1000;

已選擇72729行。

統計資訊

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

0 recursive calls

0 db block gets

11157 consistent gets

0 physical reads

72729 rows processed

SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+demo*/%';

SQL_ID EXECUTIONS VERSION_COUNT

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

cnb0ktgvms6vq 3 1

此時的路徑依然是Index Range Scan。這個明顯是由於統計量的過時，外加遊標共享，引起的錯誤路徑。下面我們重新收集一下統計量，採用no_invaliate為true的情況。

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,no_invalidate => true,method_opt => 'for columns size 10 object_id');

PL/SQL procedure successfully completed

新統計量生成，我們使用explain plan檢視一下，此時SQL應該採用的執行計劃是什麼？

SQL> explain plan for select /*+demo*/object_id, owner from t where object_id=1000;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 72722 | 639K| 266 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T | 72722 | 639K| 266 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_ID"=1000)

13 rows selected

此時，FTS全表掃描是更好的選擇。但是我們檢視一下實際執行時候，路徑情況。

SQL> select /*+demo*/object_id, owner from t where object_id=1000;

已選擇72729行。

統計資訊

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

0 recursive calls

0 db block gets

10907 consistent gets

0 physical reads

0 redo size

72729 rows processed

SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+demo*/%';

SQL_ID EXECUTIONS VERSION_COUNT

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

cnb0ktgvms6vq 4 1

此時，Oracle依然選擇了原來的Index路徑，原有的shared cursor沒有失效！！如果我們此時將shared pool清空，新的FTS執行計劃也就生成。

SQL> alter system flush shared_pool;

System altered

SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+demo*/%';

SQL_ID EXECUTIONS VERSION_COUNT

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

SQL> select /*+demo*/object_id, owner from t where object_id=1000;

已選擇72729行。

統計資訊

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

243 recursive calls

0 db block gets

5855 consistent gets

0 physical reads

72729 rows processed

--新的shared cursor形成

SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+demo*/%';

SQL_ID EXECUTIONS VERSION_COUNT

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

cnb0ktgvms6vq 1 1

--FTS執行計劃

SQL> select * from table(dbms_xplan.display_cursor(sql_id => 'cnb0ktgvms6vq'));

PLAN_TABLE_OUTPUT

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

SQL_ID cnb0ktgvms6vq, child number 0

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

select /*+demo*/object_id, owner from t where object_id=1000

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | | | 266 (100)| |

|* 1 | TABLE ACCESS FULL| T | 72722 | 639K| 266 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_ID"=1000)

18 rows selected

結論：當我們使用no_invalidate為true的時候，原有的shared cursor不會被失效，可以支援共享。只有當被age out或者flush out出shared pool之後，新執行計劃才能生成。

3、no_invalidate=false

下面我們看看取值為false的情況，實驗場景相同。為避免影響，我們重新構建資料表。

SQL> drop table t purge;

Table dropped

SQL> alter system flush shared_pool;

System altered

SQL> create table t as select * from dba_objects;

Table created

SQL> create index idx_t_id on t(object_id);

Index created

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

PL/SQL procedure successfully completed

第一次執行SQL語句，形成Index路徑執行計劃。

SQL> select /*+demo*/object_id, owner from t where object_id=1000;

統計資訊

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

164 recursive calls

0 db block gets

23 consistent gets

1 rows processed

SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+demo*/%';

SQL_ID EXECUTIONS VERSION_COUNT

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

cnb0ktgvms6vq 1 1

SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'cnb0ktgvms6vq'));

PLAN_TABLE_OUTPUT

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

SQL_ID cnb0ktgvms6vq, child number 0

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

select /*+demo*/object_id, owner from t where object_id=1000

Plan hash value: 514881935

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)|

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 11 | 2 (0)| 00

|* 2 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 (0)| 00

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"=1000)

19 rows selected

第二次執行相同SQL，我們可以看到生成的shared cursor進行共享。

SQL> select sql_id, executions, version_count, first_load_time from v$sqlarea where sql_text like 'select /*+demo*/%';

SQL_ID EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME

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

cnb0ktgvms6vq 2 1 2014-01-06/00:04:29

修改資料object_id取值，改變資料分佈。

SQL> update t set object_id=1000;

72729 rows updated

SQL> commit;

Commit complete

第三次執行。

SQL> select /*+demo*/object_id, owner from t where object_id=1000;

已選擇72729行。

統計資訊

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

0 recursive calls

0 db block gets

11157 consistent gets

72729 rows processed

此時shared cursor狀態如下：

SQL> select sql_id, executions, version_count, first_load_time from v$sqlarea where sql_text like 'select /*+demo*/%';

SQL_ID EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME

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

cnb0ktgvms6vq 3 1 2014-01-06/00:04:29

執行計劃是進行Index Range Scan動作。

SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'cnb0ktgvms6vq'));

PLAN_TABLE_OUTPUT

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

SQL_ID cnb0ktgvms6vq, child number 0

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

select /*+demo*/object_id, owner from t where object_id=1000

Plan hash value: 514881935

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)|

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 11 | 2 (0)| 00

|* 2 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 (0)| 00

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"=1000)

19 rows selected

收集統計量，使用no_invalidate為false取值。

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,no_invalidate => false,method_opt => 'for columns size 10 object_id');

PL/SQL procedure successfully completed

第四次執行過程。

SQL> select /*+demo*/object_id, owner from t where object_id=1000;

已選擇72729行。

統計資訊

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

141 recursive calls

0 db block gets

5835 consistent gets

72729 rows processed

SQL> select sql_id, executions, version_count, first_load_time from v$sqlarea where sql_text like 'select /*+demo*/%';

SQL_ID EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME

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

cnb0ktgvms6vq 1 1 2014-01-06/00:04:29

注意：在相同的sql_id情況下，version_count和executions都為1。Executions是不可能減少的。所以，這個父遊標是新生成的！

此時，執行計劃如下：

SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'cnb0ktgvms6vq'));

PLAN_TABLE_OUTPUT

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

SQL_ID cnb0ktgvms6vq, child number 0

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

select /*+demo*/object_id, owner from t where object_id=1000

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | | | 266 (100)| |

|* 1 | TABLE ACCESS FULL| T | 72722 | 639K| 266 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_ID"=1000)

18 rows selected

這也就是說明了，新的執行計劃已經生成了！也就是原有的遊標被廢棄。

結論：當我們收集統計量使用no_invalidate為false的時候，原有的共享遊標被失效，下一次在執行SQL的時候，Oracle會重新為其生成執行計劃，也就是一次hard parse過程。

True和false取值是比較簡單的。我們接下來討論dbms_stats.auto_invalidate取值情況。

從上篇（http://blog.itpub.net/17203031/viewspace-1067312/）討論情況看，無論是取值true還是false，Oracle進行的行為都是缺乏考量的。如果選擇true，表示舊的執行計劃會持續的在shared pool中駐留，新的執行計劃不會生成，如果系統SQL執行比較頻繁、Age Out現象比較少，更好地執行計劃也許不會出現。

另一個極端是false取值，Oracle會將新統計量涉及的所有shared pool一次性設定為失效。這樣的好處是可以保證更好執行計劃的生成，但是也存在一個效能spike現象。通常統計量的收集是一個集中作業過程，也就是說，通常是絕大多數業務資料表同時進行統計量生成過程。如果設定為false，也就意味著在一個短時間內，Oracle Shared Pool中大部分的shared cursor全部失效，又重新生成執行計劃。這樣，從整體上就會有一個hard parse高峰期，嚴重的話會影響到業務執行。

4、no_invalidate=dbms_stats.auto_invalidate

針對這種左右為難的現象，Oracle 10g引入了引數dbms_stats.auto_invalidate作為NO_INVALIDATE的預設值。從官方解釋看，這個引數的作業就是“讓Oracle來決定是不是對shared cursor進行失效動作”。那麼，其中的演算法原則是如何呢？我們本篇來討論這個取值過程。

Auto_invalidate過程的原則是避免true和false的極端情況，既要實現新執行計劃的生成，也要避免效能spike的出現。Oracle選擇的策略是“延時”，就是讓shared pool中的共享遊標不會一次性的失效，而是“慢慢的”、“有差別的”失效。這樣就避免了hard parse過程中出現spike。

在auto_invalidate取值進行統計量收集的情況下，shared cursor失效原則如下：

ü 當新物件的統計量獲得時，與其有依賴關係的shared cursor物件不是一次性的失效，而是被進行標註。在Oracle中，被稱為“Rolling Invalidation”；

ü 當第二次SQL進行解析的時候，會記錄時間戳資訊。這個時間戳會與系統內部隱含引數“_optimizer_invalidation_period”+一個隨機時間秒數進行比較。如果時間差還沒有超過這個設定，第二次SQL就會依然使用之前的舊shared cursor。依然是一個軟解析過程；

ü 當一個SQL解析過程中，設定的時間超過了時間間隔。Oracle會啟動一個硬解析過程，生成一個新的child cusor執行計劃。原有的子游標被標註為roll_invalidate，失效。我們可以通過檢視v$sql_shared_cursor來檢視；

從auto_invalidate的規則看，Oracle不是不進行共享遊標的失效過程，而是將其分散在一個時間範圍內，隱含引數“_optimizer_invalidation_period”來控制時間範圍起點。通過這樣的手段演算法，來緩解硬解析帶來的效能spike現象。

下面我們通過實驗來證明結論。為防止11g的自適應遊標影響，我們選擇簡單的10g版本進行測試。

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 Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

預設的引數取值為dbms_stats.no_invalidate。

SQL> select dbms_stats.get_param('no_invalidate') from dual;

DBMS_STATS.GET_PARAM('NO_INVAL

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

DBMS_STATS.AUTO_INVALIDATE

預設隱含引數取值為18000s，也就是5小時。

SQL> select x.ksppinm name,

2 y.ksppstvl value,

3 y.ksppstdf isdefault,

4 decode(bitand(y.ksppstvf, 7),

5 1,

6 'MODIFIED',

7 4,

8 'SYSTEM_MOD',

9 'FALSE') ismod,

10 decode(bitand(y.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadj

11 from sys.x$ksppi x, sys.x$ksppcv y

12 where x.inst_id = userenv('Instance')

13 and y.inst_id = userenv('Instance')

14 and x.indx = y.indx

15 and x.ksppinm like '_optimizer_invalidation_period';

NAME VALUE ISDEFAULT ISMOD ISADJ

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

_optimizer_invalidation_period 18000 TRUE FALSE FALSE

為了便於實驗，我們將這個時間段設定稍短一些。

SQL> alter system set "_optimizer_invalidation_period"=300;

System altered

建立實驗資料表T，進行相關設定和第一次統計量收集。

SQL> create table t as select * from dba_objects;

Table created

SQL> create index idx_t_id on t(object_id);

Index created

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

PL/SQL procedure successfully completed

SQL> select to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from dba_tables where table_name='T';

TO_CHAR(LAST_ANALYZED,'YYYY-MM

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

2014-01-06 10:13:57

第一次執行SQL語句，我們依然使用autotrace平臺，結果集合有省略。

SQL> set autotrace traceonly stat

SQL> select /*+demo*/* from t where object_id=1000;

統計資訊

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

381 recursive calls

0 db block gets

57 consistent gets

rows processed

Shared Cursor情況如下：

SQL> select sql_id, executions, version_count, first_load_time from v$sqlarea where sql_text like 'select /*+demo*/* from t%';

SQL_ID EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME

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

4rw3pyskdgqtc 1 1 2014-01-06/10:16:55

形成第一個遊標共享，執行一次。第二次執行SQL，遊標有共享情況。

SQL> select sql_id, executions, version_count, first_load_time, to_char(last_load_time,'yyyy-mm-dd hh24:mi:ss') from v$sqlarea where sql_text like 'select /*+demo*/* from t%';

SQL_ID EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME TO_CHAR(LAST_LOAD_TIME,'YYYY-M

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

4rw3pyskdgqtc 2 1 2014-01-06/10:16:55 2014-01-06 10:16:55

此時的執行計劃如下：

SQL> select * from table(dbms_xplan.display_cursor('4rw3pyskdgqtc'));

PLAN_TABLE_OUTPUT

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

SQL_ID 4rw3pyskdgqtc, child number 0

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

select /*+demo*/* from t where object_id=1000

Plan hash value: 514881935

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)|

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 93 | 2 (0)| 00

|* 2 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 (0)| 00

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"=1000)

19 rows selected

執行Index Range Scan路徑。在檢視v$sql_shared_cursor中，有共享資訊。下面修改資料分佈，改變佈局。

SQL> update t set object_id=1000;

49745 rows updated

SQL> commit;

Commit complete

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for columns size 10 object_id');

PL/SQL procedure successfully completed

預設引數就是auto_invalidate。從經驗看，Oracle只有選擇FTS才是最優路徑。第三次執行SQL語句。

SQL> select /*+demo*/* from t where object_id=1000;

已選擇49745行。

統計資訊

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

0 recursive calls

0 db block gets

7441 consistent gets

49745 rows processed

此時shared cursor情況如下：

SQL> select sql_id, executions, version_count, first_load_time, to_char(last_load_time,'yyyy-mm-dd hh24:mi:ss') from v$sqlarea where sql_text like 'select /*+demo*/* from t%';

SQL_ID EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME TO_CHAR(LAST_LOAD_TIME,'YYYY-M

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

4rw3pyskdgqtc 3 1 2014-01-06/10:16:55 2014-01-06 10:16:55

第三次執行依然使用了原有的Index Range Scan執行計劃，沒有新的父子游標物件生成，執行次數上增加了一次。

過一會進行第四次執行。

10:23:44 SQL> select /*+demo*/* from t where object_id=1000;

已選擇49745行。

統計資訊

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

0 recursive calls

0 db block gets

7441 consistent gets

0 physical reads

49745 rows processed

SQL> select sql_id, executions, version_count, first_load_time, to_char(last_load_time,'yyyy-mm-dd hh24:mi:ss') from v$sqlarea where sql_text like 'select /*+demo*/* from t%';

SQL_ID EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME TO_CHAR(LAST_LOAD_TIME,'YYYY-M

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

4rw3pyskdgqtc 4 1 2014-01-06/10:16:55 2014-01-06 10:16:55

第四次執行之後，Oracle依然沒有讓遊標失效。經過三四分鐘之後，執行不同的效果。

10:23:51 SQL> select /*+demo*/* from t where object_id=1000;

已選擇49745行。

統計資訊

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

173 recursive calls

0 db block gets

3987 consistent gets

49745 rows processed

10:27:16 SQL>

遊標共享情況如下：

SQL> select sql_id, executions, version_count, first_load_time, to_char(last_load_time,'yyyy-mm-dd hh24:mi:ss') from v$sqlarea where sql_text like 'select /*+demo*/* from t%';

SQL_ID EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME TO_CHAR(LAST_LOAD_TIME,'YYYY-M

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

4rw3pyskdgqtc 5 2 2014-01-06/10:16:55 2014-01-06 10:27:11

形成了一個新的子游標物件，有新的解析動作發生。檢視v$sql_shared_cursor檢視，可以看到變化。

SQL> select sql_id, child_number,ROLL_INVALID_MISMATCH from v$sql_shared_cursor where sql_id='4rw3pyskdgqtc';

SQL_ID CHILD_NUMBER ROLL_INVALID_MISMATCH

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

4rw3pyskdgqtc 0 N

4rw3pyskdgqtc 1 Y

Child cursor 0號由於Roll Invalidate原因被拒絕共享。遊標1資訊如下：

SQL> select child_number, executions, first_load_time, last_load_time from v$sql where sql_id='4rw3pyskdgqtc';

CHILD_NUMBER EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME

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

0 4 2014-01-06/10:16:55 2014-01-06/10:16:55

1 1 2014-01-06/10:16:55 2014-01-06/10:27:11

子游標1和0分別代表了不同的執行計劃。

SQL> select * from table(dbms_xplan.display_cursor('4rw3pyskdgqtc','1'));

PLAN_TABLE_OUTPUT

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

SQL_ID 4rw3pyskdgqtc, child number 1

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

select /*+demo*/* from t where object_id=1000

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | | | 155 (100)| |

|* 1 | TABLE ACCESS FULL| T | 49740 | 4420K| 155 (3)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_ID"=1000)

18 rows selected

SQL> select * from table(dbms_xplan.display_cursor('4rw3pyskdgqtc','0'));

PLAN_TABLE_OUTPUT

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

SQL_ID 4rw3pyskdgqtc, child number 0

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

select /*+demo*/* from t where object_id=1000

Plan hash value: 514881935

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)|

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 93 | 2 (0)| 00

|* 2 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 (0)| 00

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"=1000)

19 rows selected

從上面實驗，我們可以得到結論：當統計量收集採用no_invalidate=dbms_stats.auto_invalidate的時候，已經存在的共享遊標會在一個時間段之後被失效。這樣的策略避免了集中hard sparse出現，保證了系統效能平穩化過程。

5、結論

Oracle統計量對於執行計劃至關重要，理解no_invalidate引數含義和設定，可以幫助我們更好地理解Oracle工作原理和設計思路。

About Me

...............................................................................................................................

● 本文整理自網路，http://blog.itpub.net/17203031/viewspace-1067620/

● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599 微信群：私聊

● 聯絡我請加QQ好友(646634621)，註明新增緣由

● 於 2017-05-09 09:00 ~ 2017-05-30 22:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

...............................................................................................................................

拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2139300/，如需轉載，請註明出處，否則將追究法律責任。

Oracle收集統計資訊之NO_INVALIDATE引數

Oracle收集統計資訊之NO_INVALIDATE引數

相關文章