sql version count引發cursor:pin s wait x及library cache latch library cache lock
背景
某Q友在其生產系統AWR發現library cache latch及library cache lock及cursor:pin s wait x等待事件非常嚴重，經排查發生某些底層SQL的多版本現象非常嚴重，最高達到4000多左右結論
1,oracle為了控制某個SQL的子游標個數不止過高過多，採用繫結變數分級機制，即繫結變數的長度（位元組）在一個範圍內，就隸屬一個子遊標，否則就會新建立一個子遊標
2,可以透過v$sql_bind_metadata獲取上述繫結變數分量的具體分界點
3,v$sql_shared_cursor可以分析某SQL產生多個子遊標的原因，然後進行不同的原因進行分析
4,進一步基於第3步分析（這裡子游標不能共享的原因是bind_mismatch）,在MOS查詢相關文件 ，發現
High Version Count Due To BIND_MISMATCH [ID 336268.1]
5，基於第4步，我們已經知道多版本的產生原因，現在要解決這個問題，因為是生產系統， 可以採用10503事件進行暫時控制
但不能成功
6，基於第5步，就要分析為何事件配置不成功，繼續檢視MOS，以關鍵字進行查詢，event 10503
發現文章：
Bug 10274265 - Event 10503 does not work at session level [ID 10274265.8]
7，基於第6步，要思考下，即便配置10503事件成功了，畢竟是生產系統，會不會引發其它的一些問題或BUG呢，還是老辦法，檢視MOS
event可能導致ORA-600: [kxspoac : EXL 1]錯誤
ORA-600: [kxspoac : EXL 1] Occuring During Query If Event 10503 Is Set [ID 810194.1]
8，由上述引發一些思考：
--由此可見ORACLE知識聯絡是非常複雜的，要調整一個引數，必須要從全方位考慮與分析
1，配置引數的命令是什麼
2，如果配置引數不成功，它為何不成功，可能也有相關的MOS文章進行介紹
3，如果配置引數成功，會引發什麼其它的問題，也要進行全面考慮，否則解決舊問題，引發新問題
引申一下，可見應用人員設計表以及開發人員，在編寫程式碼時，也要考慮到這點，比如：不要在宣告變數長度時
過分宣告，比如本來20位元組就夠了，你宣告瞭1000個位元組，而且每個開發人員宣告長度不同
，這樣就會引發本文的子游標不能共共享的問題
4，基於關鎮字檢視是否是相關的BUG
5，進一步引證了TOP 5等待事件有時未必是因，很可能它只是一個果，而且它的原因不在本時間點取樣的AWR（或者說不能很明白得看出來）
也就是說需要你檢視此時間點之前的AWR進行分析
分析
SQL> select sql_id,sql_text from v$sql where lower(sql_text) like '%select pos#,intcol#%';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
1qb4fr2qc8jsu select sql_id,sql_text from v$sql where lower(sql_text) like '%select pos#,intcol#%'
6769wyy3yf66f select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1
SQL> select sql_id,count(*) from v$sql where sql_id='6769wyy3yf66f' group by sql_id;
SQL_ID COUNT(*)
------------- ----------
6769wyy3yf66f 1
SQL> select sid,event,prev_sql_id,sql_id from v$session where nvl(prev_sql_id,sql_id)='6769wyy3yf66f';
no rows selected
SQL> select count(*) from v$session where nvl(prev_sql_id,sql_id)='6769wyy3yf66f';
COUNT(*)
----------
0
SQL> desc icol$;
Name Null? Type
----------------- -------- ------------
OBJ# NOT NULL NUMBER
BO# NOT NULL NUMBER
COL# NOT NULL NUMBER
POS# NOT NULL NUMBER
SEGCOL# NOT NULL NUMBER
SEGCOLLENGTH NOT NULL NUMBER
OFFSET NOT NULL NUMBER
INTCOL# NOT NULL NUMBER
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 NUMBER
SPARE4 VARCHAR2(100
0)
SPARE5 VARCHAR2(100
0)
SPARE6 DATE
SQL> select count(*) from icol$;
COUNT(*)
----------
2416
SQL> select obj#,bo#,col#,pos#,segcol#,offset,spare1,spare2 from icol$ where rownum<=10 order by 1;
OBJ# BO# COL# POS# SEGCOL# OFFSET SPARE1 SPARE2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
3 2 1 1 0 0 0 0
7 6 1 1 0 0 0 0
9 8 1 1 0 0 0 0
9 8 2 2 0 0 0 0
9 8 3 3 0 0 0 0
11 10 1 1 0 0 0 0
24 23 1 1 0 0 0 0
24 23 2 2 0 0 0 0
26 25 1 1 0 0 0 0
26 25 2 2 0 0 0 0
10 rows selected.
create table icol$ /* index column table */
( obj# number not null, /* index object number */
bo# number not null, /* base object number */
col# number not null, /* column number */
pos# number not null, /* column position number as created */
segcol# number not null, /* column number in segment */
segcollength number not null, /* length of the segment column */
offset number not null, /* offset of column */
intcol# number not null, /* internal column number */
spare1 number, /* flag */
/* 0x01: this is an expression */
/* 0x02: desc index column */
/* 0x04: filter by col for dom idx */
/* 0x08: order by col for dom idx */
spare2 number, /* dimension table internal column number */
spare3 number, /* pos# of col in order by list of dom idx */
spare4 varchar2(1000),
spare5 varchar2(1000),
spare6 date
)
cluster c_obj#(bo#)
SQL> create table t_bind(a varchar2(100));
Table created.
SQL> insert into t_bind values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> var x varchar2(10);
SQL> select a from t_bind where a=:x;
no rows selected
SQL> var x varchar2(100);
SQL> select a from t_bind where a=:x;
no rows selected
SQL> select sql_id,sql_text from v$sql where sql_text like '%select a from t_bind where a=%';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
3swjj5kc9kfkp select a from t_bind where a=:x
3swjj5kc9kfkp select a from t_bind where a=:x
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0 0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8 1 Y
SQL> var x varchar2(38);
SQL> select a from t_bind where a=:x;
no rows selected
---發現子游標沒有繼續增加
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0 0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8 1 Y
SQL> var x varchar2(50);
SQL> select a from t_bind where a=:x;
no rows selected
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0 0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8 1 Y
---會不會變數長度，也有個分級，不同分級的長度，會產生新的子游標呢
SQL> alter table t_bind modify a varchar2(1000);
Table altered.
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
no rows selected
SQL> var x varchar2(10);
SQL> select a from t_bind where a=:x;
no rows selected
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0 0 N
SQL> var x varchar2(100);
SQL> select a from t_bind where a=:x;
no rows selected
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0 0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8 1 Y
SQL> var x varchar2(200);
SQL> select a from t_bind where a=:x;
no rows selected
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0 0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8 1 N
SQL> var x varchar2(500);
SQL> select a from t_bind where a=:x;
no rows selected
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0 0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8 1 N
SQL> var x varchar2(1000);
SQL> select a from t_bind where a=:x;
no rows selected
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0 0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8 1 N
---可見重新整理共享池只會儲存
SQL> alter system flush shared_pool;
System altered.
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8 1 N
---繼續分析下到底繫結變數分級，到度在哪些值範圍內，隸屬一個子遊標，否則會新生一個子遊標
經查MOS，發現文章：High Version Count Due To BIND_MISMATCH [ID 336268.1]
對於繫結變數,ORACLE根據變數長度進行了分級，對於VARCHAR2型別共有如下4級：
第一級：1-32
第二級：33-128
第三級：129-2000
第四級：2000+
SQL> alter table t_bind modify a varchar2(300);
Table altered.
SQL> var x varchar2(50);
SQL> select a from t_bind where a=:x;
no rows selected
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0 0 N
SQL> var x varchar2(129);
SQL> select a from t_bind where a=:x;
no rows selected
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0 0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8 1 Y
SQL> var x varchar2(2001);
SQL> select a from t_bind where a=:x;
no rows selected
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0 0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8 1 Y
3swjj5kc9kfkp 0000000064964090 0000000066EA5F40 2 Y
SQL> var x varchar2(15);
SQL> select a from t_bind where a=:x;
no rows selected
---mos說，可以用10503事件暫時解決上述版本過高問題,好像10503事件沒起作用
SQL> alter session set events '10503 trace name context forever ,level 4096';
Session altered.
SQL> alter table t_bind modify a varchar2(300);
Table altered.
SQL> var x varchar2(15);
SQL> select a from t_bind where a=:x;
no rows selected
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0 0 N
SQL> var x varchar2(120);
SQL> select a from t_bind where a=:x;
no rows selected
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0 0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8 1 Y
SQL> var x varchar2(600);
SQL> select a from t_bind where a=:x;
no rows selected
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0 0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8 1 Y
SQL> var x varchar2(2600);
SQL> select a from t_bind where a=:x;
no rows selected
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0 0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8 1 Y
3swjj5kc9kfkp 0000000064964090 0000000066EA5F40 2 Y
----繼續分析10503事件為何不生效,看來不是105032命令的次序問題
SQL> alter session set events '10503 trace name context level 4096,forever';
Session altered.
SQL> alter table t_bind modify a varchar2(300);
Table altered.
SQL> var x varchar2(12);
SQL> select a from t_bind where a=:x;
no rows selected
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0 0 N
SQL> var x varchar2(100);
SQL> select a from t_bind where a=:x;
no rows selected
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0 0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8 1 Y
SQL> var x varchar2(250);
SQL> select a from t_bind where a=:x;
no rows selected
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0 0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8 1 Y
SQL> var x varchar2(2500);
SQL> select a from t_bind where a=:x;
no rows selected
SQL> select sql_id,address,child_address,child_number,bind_mismatch from v$sql_shared_cursor where sql_id='3swjj5kc9kfkp';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B
------------- ---------------- ---------------- ------------ -
3swjj5kc9kfkp 0000000064964090 0000000064961FC0 0 N
3swjj5kc9kfkp 0000000064964090 00000000649A1FA8 1 Y
3swjj5kc9kfkp 0000000064964090 0000000066EA5F40 2 Y
---繼續經查MOS，發現文章
Bug 10274265 - Event 10503 does not work at session level [ID 10274265.8]
可見即使在會話層面配置了10503事件，仍然不會生效
---而且即使設定10503事件成功，可能會引發ORA-600報錯
event可能導致ORA-600: [kxspoac : EXL 1]錯誤
ORA-600: [kxspoac : EXL 1] Occuring During Query If Event 10503 Is Set [ID 810194.1]
--由此可見ORACLE知識聯絡是非常複雜的，要調整一個引數，必須要從全方位考慮與分析
1，配置引數的命令是什麼
2，如果配置引數不成功，它為何不成功，可能也有相關的MOS文章進行介紹
3，如果配置引數成功，會引發什麼其它的問題，也要進行全面考慮，否則解決舊問題，引發新問題
引申一下，可見應用人員設計表以及開發人員，在編寫程式碼時，也要考慮到這點，比如：不要在宣告變數長度時
過分宣告，比如本來20位元組就夠了，你宣告瞭1000個位元組，而且每個開發人員宣告長度不同
，這樣就會引發本文的子游標不能共共享的問題
基於關鎮字檢視是否是相關的BUG
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25462274/viewspace-1873611/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- library cache pin和library cache lock(一)
- library cache pin和library cache lock (zt)
- library cache pin和library cache lock(二)
- latch:library cache lock等待事件事件
- Library Cache最佳化篇（一）降低library cache lock和library cache pin的方法
- [20240920]跟蹤library cache lock library cache pin使用gdb.txt
- [20240824]跟蹤library cache lock library cache pin使用gdb.txt
- library cache pin(轉)
- [20241105]跟蹤library cache lock library cache pin使用gdb(11g)2.txt
- [20241108]跟蹤library cache lock library cache pin使用gdb(11g)4.txt
- [20241108]跟蹤library cache lock library cache pin使用gdb(11g)3.txt
- library cache lock和library cache bin實驗_2.0
- 【等待事件】library cache pin事件
- Library Cache 診斷：Lock, Pin 以及 Load Lock (文件 ID 1548524.1)
- 【ASK_ORACLE】Library cache pin 與 library load lock的關係和區別Oracle
- 【ASK_ORACLE】Library Cache概念篇（二）之Library Cache Pin的定義Oracle
- cursor pin S wait on XAI
- cursor: pin S wait on XAI
- DBA手記（學習）-library cache pin
- Oracle Library cacheOracle
- 【TUNE_ORACLE】等待事件之“library cache lock”Oracle事件
- 一次library cache lock 問題分析
- [20190319]shared pool latch與library cache latch的簡單探究.txt
- [20210512]shared pool latch與library cache latch的簡單探究.txt
- 【ASM_ORACLE】Library Cache最佳化篇（二）Library cache load lock的概念和解決辦法ASMOracle
- cursor:pin S wait on X故障診分析AI
- oracle等待事件之cursor：pin S wait on XOracle事件AI
- [20201203]探究library cache mutex X 3.txtMutex
- [20190402]Library Cache mutex.txtMutex
- [20210507]dump library_cache.txt
- [20210507]分析library cache轉儲.txt
- 徹底搞清楚library cache lock的成因和解決方法(轉)
- [20210520]11g shared pool latch與library cache mutex的簡單探究.txtMutex
- [20210507]dump library_cache 2.txt
- Oracle 11g 密碼延遲認證與 library cache lock 等待Oracle密碼
- [20240827]分析為什麼出現library cache lock等待事件2.txt事件
- [20240828]分析為什麼出現library cache lock等待事件5.txt事件
- 故障：核心表業務高峰期授權導致library cache lock和mutex x競爭Mutex
- ORACLE LOCK,LATCH,PINOracle