Oracle 統計量NO_INVALIDATE引數配置(下)
本篇我們繼續討論NO_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
--------------------------------------------------------------------------------
| 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
子游標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
--------------------------------------------------------------------------
| 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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle 統計量NO_INVALIDATE引數配置(上)Oracle
- Oracle收集統計資訊之NO_INVALIDATE引數Oracle
- oracle dbms_stats(no_invalidate引數)Oracle
- oracle引數配置Oracle
- Oracle rman 配置引數Oracle
- ORACLE 配置event引數Oracle
- SAP配置系統引數
- AIX 系統引數配置AI
- oracle RMAN引數配置詳解Oracle
- MySQL 配置InnoDB配置非持久優化器統計資訊引數MySql優化
- AIX 系統引數配置 -- 轉AI
- ORACLE初始化引數的配置Oracle
- linux 下的訊號量引數Linux
- linux系統基於oracle的核心引數配置說明LinuxOracle
- SciTech-Mathmatics-Probability+Statistics-Population:Region-統計量與引數估計
- oracle11g引數檔案配置Oracle
- 部分Oracle 配置檔案引數含義Oracle
- 更改oracle系統引數例子Oracle
- 數理統計基礎 統計量
- Oracle group by與case when統一單位後統計數量Oracle
- 常用的jvm配置引數 :永久區引數配置JVM
- Oracle:歸檔量統計Oracle
- AIX作業系統核心引數配置AI作業系統
- oracle sga配置相關的os 核心引數Oracle
- 幾個引數配置的計算公式公式
- Oracle引數-隱藏引數Oracle
- jvm引數配置JVM
- JavaWeb引數配置JavaWeb
- Laravel 數量統計優化Laravel優化
- ArcGIS工具 - 統計工具數量
- oracle 引數Oracle
- 卷積神經網路的引數量和計算量卷積神經網路
- 【統計】能夠在session級別和system級別修改的oracle引數統計SessionOracle
- ORACLE安裝核心引數配置_linux平臺OracleLinux
- ORACLE RAC GUARD配置引數——RAC GUARD概念和管理Oracle
- 留存統計 引數聚合函式函式
- 如何計算PHP函式中傳遞的引數數量PHP函式
- Ceph配置引數分析