[20120206]Cursor Invalidation與分析表.txt
在分析表的是否有一個引數no_invalidate:預設值是DBMS_STATS.AUTO_INVALIDATE.AUTO_INVALIDATE。
10g中預設是AUTO_INVALIDATE,就是說分析表後,遊標不會馬上invalidate,已經存在的SQL的執行計劃不會受新的統計資訊影響。可以手工DDL
invalidate遊標。又或者等待隱藏引數_optimizer_invalidation_period(time window for invalidation of cursors of analyzed objects)秒後,
Oracle自動invalidate遊標並使SQL能夠讀取新的統計資訊產生新的執行計劃。
如果想要dbms_stats分析立馬見效,需要使用no_invalidate=false option或者DBA自己手工invalidate遊標。
--說明一下,我個人感覺這個引數理解起來很煩,validate表示有效,no_invalidate反了2次,也是表示有效的意思。
dbms_stats收集統計資訊時候no_invalidate引數
用於是否與收集相關object的cursor失效,defalut(9i false, 10g dbms_stats.auto_invalidate(既null))
true:當收集完統計資訊後,收集物件的cursor不會失效(不會產生新的執行計劃,子游標)
false:當收集完統計資訊後,收集物件的cursor會立即失效(新的執行計劃,新的子游標)
no_invalidate=>DBMS_STATS.AUTO_INVALIDATE,分析表後,遊標不會馬上invalidate,已經存在的SQL的執行計劃不會受新的統計資訊影響。可以手工
DDL invalidate遊標。又或者等待隱藏引數_optimizer_invalidation_period(time window for invalidation of cursors of analyzed objects)秒後,
Oracle自動invalidate遊標並使SQL能夠讀取新的統計資訊產生新的執行計劃。
1.建立測試環境:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit 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
SQL> create table t as select rownum id , 'test' name from dual connect by level<=64;
SQL> exec dbms_stats.gather_table_stats(null,'t',no_invalidate => DBMS_STATS.AUTO_INVALIDATE);
SQL> select count(*) from t;
COUNT(*)
----------
64
sqlid='cyzznbykb509s'
2.測試1(no_invalidate => false):
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 1 1 1 0
SQL> exec dbms_stats.gather_table_stats(null,'t',no_invalidate => false);
PL/SQL procedure successfully completed.
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 1 1 1 1
--分析後no_invalidate => false,v$sql 的INVALIDATIONS=1.游標失效。
SQL> select count(*) from t;
COUNT(*)
----------
64
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 1 1 2 1
3.測試2(no_invalidate => true):
SQL> exec dbms_stats.gather_table_stats(null,'t',no_invalidate => true);
PL/SQL procedure successfully completed.
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 1 1 2 1
--分析後no_invalidate => true,v$sql 的INVALIDATIONS=1(沒有變化與上次一樣).說明游標沒有失效。
SQL> select count(*) from t;
COUNT(*)
----------
64
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 2 2 2 1
--再次執行查詢,發現PARSE_CALLS增加了1次,loads沒有變化。
4.測試3(no_invalidate => DBMS_STATS.AUTO_INVALIDATE):
預設隱藏引數_optimizer_invalidation_period設定的時間太長=18000(5個小時),我縮短一些。
SQL> alter system set "_optimizer_invalidation_period" = 300 scope=memory;
System altered.
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 2 2 2 1
--馬上執行,select count(*) from t;
SQL> exec dbms_stats.gather_table_stats(null,'t',no_invalidate => DBMS_STATS.AUTO_INVALIDATE);
PL/SQL procedure successfully completed.
SQL> select count(*) from t;
COUNT(*)
----------
64
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 3 3 2 1
--可以發現v$sql 的INVALIDATIONS=1(沒有變化與上次).說明游標沒有失效。執行計劃以及使用原來的游標。
--等一段時間300秒,再測試:
SQL> host sleep 300
SQL> select count(*) from t;
COUNT(*)
----------
64
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 3 3 2 1
cyzznbykb509s 1 1 1 1 0
--可以發現原來的游標無效,生成新的子游標。看看為什麼不能共享?
SQL> @share cyzznbykb509s
old 15: and q.sql_id like ''&1''',
new 15: and q.sql_id like ''cyzznbykb509s''',
SQL_TEXT = select count(*) from t
SQL_ID = cyzznbykb509s
ADDRESS = 000000009353C428
CHILD_ADDRESS = 0000000093623B88
CHILD_NUMBER = 0
--------------------------------------------------
SQL_TEXT = select count(*) from t
SQL_ID = cyzznbykb509s
ADDRESS = 000000009353C428
CHILD_ADDRESS = 000000009362C7E0
CHILD_NUMBER = 1
ROLL_INVALID_MISMATCH = Y
--------------------------------------------------
PL/SQL procedure successfully completed.
--原來的游標無效,是由於ROLL_INVALID_MISMATCH。最後修改隱含引數回來。
SQL> alter system set "_optimizer_invalidation_period" = 18000 scope=memory;
System altered.
總結:
預設分析DBMS_STATS.AUTO_INVALIDATE,如果處理不好,一些效能問題會延遲出現,在最佳化時注意。
share指令碼如下:
SET serveroutput on size 100000;
DECLARE
c NUMBER;
col_cnt NUMBER;
col_rec DBMS_SQL.desc_tab;
col_value VARCHAR2 (4000);
ret_val NUMBER;
BEGIN
c := DBMS_SQL.open_cursor;
DBMS_SQL.parse
(c,
'select q.sql_text, s.*
from v$sql_shared_cursor s, v$sql q
where s.sql_id = q.sql_id
and s.child_number = q.child_number
and q.sql_id like ''&1''',
DBMS_SQL.native
);
DBMS_SQL.describe_columns (c, col_cnt, col_rec);
FOR idx IN 1 .. col_cnt
LOOP
DBMS_SQL.define_column (c, idx, col_value, 4000);
END LOOP;
ret_val := DBMS_SQL.EXECUTE (c);
WHILE (DBMS_SQL.fetch_rows (c) > 0)
LOOP
FOR idx IN 1 .. col_cnt
LOOP
DBMS_SQL.COLUMN_VALUE (c, idx, col_value);
IF col_rec (idx).col_name IN
('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS', 'CHILD_NUMBER',
'SQL_TEXT')
THEN
DBMS_OUTPUT.put_line ( RPAD (col_rec (idx).col_name, 30)
|| ' = '
|| col_value
);
ELSIF col_value = 'Y'
THEN
DBMS_OUTPUT.put_line ( RPAD (col_rec (idx).col_name, 30)
|| ' = '
|| col_value
);
END IF;
END LOOP;
DBMS_OUTPUT.put_line
('--------------------------------------------------');
END LOOP;
DBMS_SQL.close_cursor (c);
END;
/
SET serveroutput off;
10g中預設是AUTO_INVALIDATE,就是說分析表後,遊標不會馬上invalidate,已經存在的SQL的執行計劃不會受新的統計資訊影響。可以手工DDL
invalidate遊標。又或者等待隱藏引數_optimizer_invalidation_period(time window for invalidation of cursors of analyzed objects)秒後,
Oracle自動invalidate遊標並使SQL能夠讀取新的統計資訊產生新的執行計劃。
如果想要dbms_stats分析立馬見效,需要使用no_invalidate=false option或者DBA自己手工invalidate遊標。
--說明一下,我個人感覺這個引數理解起來很煩,validate表示有效,no_invalidate反了2次,也是表示有效的意思。
dbms_stats收集統計資訊時候no_invalidate引數
用於是否與收集相關object的cursor失效,defalut(9i false, 10g dbms_stats.auto_invalidate(既null))
true:當收集完統計資訊後,收集物件的cursor不會失效(不會產生新的執行計劃,子游標)
false:當收集完統計資訊後,收集物件的cursor會立即失效(新的執行計劃,新的子游標)
no_invalidate=>DBMS_STATS.AUTO_INVALIDATE,分析表後,遊標不會馬上invalidate,已經存在的SQL的執行計劃不會受新的統計資訊影響。可以手工
DDL invalidate遊標。又或者等待隱藏引數_optimizer_invalidation_period(time window for invalidation of cursors of analyzed objects)秒後,
Oracle自動invalidate遊標並使SQL能夠讀取新的統計資訊產生新的執行計劃。
1.建立測試環境:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit 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
SQL> create table t as select rownum id , 'test' name from dual connect by level<=64;
SQL> exec dbms_stats.gather_table_stats(null,'t',no_invalidate => DBMS_STATS.AUTO_INVALIDATE);
SQL> select count(*) from t;
COUNT(*)
----------
64
--獲取sql_id
SQL> @dpc
PLAN_TABLE_OUTPUT
---------------------------------------------------------
SQL_ID cyzznbykb509s, child number 0
-------------------------------------
select count(*) from t
Plan hash value: 2966233522
---------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS FULL| T | 64 | 3 (0)|
---------------------------------------------------------
sqlid='cyzznbykb509s'
2.測試1(no_invalidate => false):
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 1 1 1 0
SQL> exec dbms_stats.gather_table_stats(null,'t',no_invalidate => false);
PL/SQL procedure successfully completed.
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 1 1 1 1
--分析後no_invalidate => false,v$sql 的INVALIDATIONS=1.游標失效。
SQL> select count(*) from t;
COUNT(*)
----------
64
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 1 1 2 1
3.測試2(no_invalidate => true):
SQL> exec dbms_stats.gather_table_stats(null,'t',no_invalidate => true);
PL/SQL procedure successfully completed.
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 1 1 2 1
--分析後no_invalidate => true,v$sql 的INVALIDATIONS=1(沒有變化與上次一樣).說明游標沒有失效。
SQL> select count(*) from t;
COUNT(*)
----------
64
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 2 2 2 1
--再次執行查詢,發現PARSE_CALLS增加了1次,loads沒有變化。
4.測試3(no_invalidate => DBMS_STATS.AUTO_INVALIDATE):
預設隱藏引數_optimizer_invalidation_period設定的時間太長=18000(5個小時),我縮短一些。
SQL> alter system set "_optimizer_invalidation_period" = 300 scope=memory;
System altered.
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 2 2 2 1
--馬上執行,select count(*) from t;
SQL> exec dbms_stats.gather_table_stats(null,'t',no_invalidate => DBMS_STATS.AUTO_INVALIDATE);
PL/SQL procedure successfully completed.
SQL> select count(*) from t;
COUNT(*)
----------
64
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 3 3 2 1
--可以發現v$sql 的INVALIDATIONS=1(沒有變化與上次).說明游標沒有失效。執行計劃以及使用原來的游標。
--等一段時間300秒,再測試:
SQL> host sleep 300
SQL> select count(*) from t;
COUNT(*)
----------
64
SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id = 'cyzznbykb509s';
SQL_ID CHILD_NUMBER EXECUTIONS PARSE_CALLS LOADS INVALIDATIONS
------------- ------------ ---------- ----------- ---------- -------------
cyzznbykb509s 0 3 3 2 1
cyzznbykb509s 1 1 1 1 0
--可以發現原來的游標無效,生成新的子游標。看看為什麼不能共享?
SQL> @share cyzznbykb509s
old 15: and q.sql_id like ''&1''',
new 15: and q.sql_id like ''cyzznbykb509s''',
SQL_TEXT = select count(*) from t
SQL_ID = cyzznbykb509s
ADDRESS = 000000009353C428
CHILD_ADDRESS = 0000000093623B88
CHILD_NUMBER = 0
--------------------------------------------------
SQL_TEXT = select count(*) from t
SQL_ID = cyzznbykb509s
ADDRESS = 000000009353C428
CHILD_ADDRESS = 000000009362C7E0
CHILD_NUMBER = 1
ROLL_INVALID_MISMATCH = Y
--------------------------------------------------
PL/SQL procedure successfully completed.
--原來的游標無效,是由於ROLL_INVALID_MISMATCH。最後修改隱含引數回來。
SQL> alter system set "_optimizer_invalidation_period" = 18000 scope=memory;
System altered.
總結:
預設分析DBMS_STATS.AUTO_INVALIDATE,如果處理不好,一些效能問題會延遲出現,在最佳化時注意。
share指令碼如下:
SET serveroutput on size 100000;
DECLARE
c NUMBER;
col_cnt NUMBER;
col_rec DBMS_SQL.desc_tab;
col_value VARCHAR2 (4000);
ret_val NUMBER;
BEGIN
c := DBMS_SQL.open_cursor;
DBMS_SQL.parse
(c,
'select q.sql_text, s.*
from v$sql_shared_cursor s, v$sql q
where s.sql_id = q.sql_id
and s.child_number = q.child_number
and q.sql_id like ''&1''',
DBMS_SQL.native
);
DBMS_SQL.describe_columns (c, col_cnt, col_rec);
FOR idx IN 1 .. col_cnt
LOOP
DBMS_SQL.define_column (c, idx, col_value, 4000);
END LOOP;
ret_val := DBMS_SQL.EXECUTE (c);
WHILE (DBMS_SQL.fetch_rows (c) > 0)
LOOP
FOR idx IN 1 .. col_cnt
LOOP
DBMS_SQL.COLUMN_VALUE (c, idx, col_value);
IF col_rec (idx).col_name IN
('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS', 'CHILD_NUMBER',
'SQL_TEXT')
THEN
DBMS_OUTPUT.put_line ( RPAD (col_rec (idx).col_name, 30)
|| ' = '
|| col_value
);
ELSIF col_value = 'Y'
THEN
DBMS_OUTPUT.put_line ( RPAD (col_rec (idx).col_name, 30)
|| ' = '
|| col_value
);
END IF;
END LOOP;
DBMS_OUTPUT.put_line
('--------------------------------------------------');
END LOOP;
DBMS_SQL.close_cursor (c);
END;
/
SET serveroutput off;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-715663/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- AWR報告分析之三:cursor: pin S 的原理與案例分析
- AWR報告分析之三:cursor: pin S 的原理與案例分析-eygle
- [20241012]cursor_sharing=force與函式索引.txt函式索引
- [20120829]分析表與no_invalidate=AUTO_INVALIDATE.txt
- [20170621]Session Cursor Caching 2.txtSession
- [20170621]Session Cursor Caching 4.txtSession
- [20120220]Adaptive Cursor Sharing 與hints.txtAPT
- [20141203]分析語句導致阻塞分析表.txt
- 遊標指令碼效能問題解決與分析 (4) - Cursor Performance Analysis指令碼ORM
- 遊標指令碼效能問題解決與分析 (2) - Cursor Performance Analysis指令碼ORM
- [20140802]cursor_sharing=similar.txtMILA
- DBMS_STATS分析表與備份分析資訊
- dbms_stats與執行計劃的合理性plan__optimizer_invalidation_period
- MySQL的多層SP中Cursor的m_max_cursor_index相關BUG分析MySqlIndex
- cursor:pin S wait on X故障診分析AI
- 實驗:cursor count超高的問題分析
- [20160329]表空間與資料檔案.txt
- cursor: pin S模擬與處理
- cursor_sharing與v$sqlarea_v$sql_v$sql_shared_cursorSQL
- [20220414]Function based indexes and cursor sharing2.txtFunctionIndex
- [20201117]解析cursor pin S等待事件.txt事件
- [20202117]Function based indexes and cursor sharing.txtFunctionIndex
- [20221227]Adaptive Cursor Sharing & 直方圖.txtAPT直方圖
- 學習動態效能表(13)--V$OPEN_CURSOR
- cursor_sharing=similar 與 直方圖MILA直方圖
- Oracle表與索引的分析及索引重建Oracle索引
- shared SQL,parent cursor,child cursorSQL
- [20211206]分析表取樣最小大小是多少.txt
- 多版本cursor,與session_cached_cursors關係Session
- 【CURSOR】Oracle 遊標 (cursor)知識梳理Oracle
- [20151201]統計分析與GRD.txt
- [20201126]使用cursor_sharing_exact與給sql打補丁2.txtSQL
- [20201126]使用cursor_sharing_exact與給sql打補丁3.txtSQL
- Oracle表與索引的分析及索引重建(轉)Oracle索引
- ORACLE表連線方式的分析與優化Oracle優化
- [20180301]模擬cursor pin S wait on X.txtAI
- Oracle CursorOracle
- Cursor使用