Oracle 統計量NO_INVALIDATE引數配置(下)

531968912發表於2016-07-22

 

本篇我們繼續討論NO_INVALIDATE引數。

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

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

 

4no_invalidate=dbms_stats.auto_invalidate

 

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

Auto_invalidate過程的原則是避免truefalse的極端情況,既要實現新執行計劃的生成,也要避免效能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

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

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

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

|   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

 

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

 

 

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

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

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

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

|   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

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

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

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

|   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工作原理和設計思路。


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

相關文章