A cursor wait is associated with parsing in some form. A session may wait for this event when it is trying to get a mutex
創(chuàng)新互聯(lián)主要從事成都做網(wǎng)站、網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計(jì)、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)陜西,10余年網(wǎng)站建設(shè)經(jīng)驗(yàn),價(jià)格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):18980820575pin in Share mode but another session is holding the mutex pin on the same cursor object in exclusive. Frequently, waits
for 'Cursor: pin S wait on X' is a symptom and not the cause. There may be underlying tuning requirements or known
issues.
# 通常,這個(gè)等待事件只是一個(gè)現(xiàn)象并不是原因,通常是需要更深層次的優(yōu)化或者已知的其他問題導(dǎo)致。
cursor: pin S , cursor: pin X , cursor: pin S wait on X 這三個(gè)等待事件,實(shí)際上就是替代了 cursor 的 library cache pin , pin S 代表執(zhí)行( share pin ), pin X 代表解析( exclusive pin ), pin S wait on X 代表執(zhí)行正在等待解析操作, pin S wait on X 一定是等待以修改為目的的 X 排他操作,如果是多版本 examination (察看)父游標(biāo)會(huì)發(fā)生父游標(biāo)的 cursor pin S 。
這里需要強(qiáng)調(diào)一下,它們只是替換了訪問 cursor 的 library cache pin ,而對(duì)于訪問 procedure 這種實(shí)體對(duì)象,依然是傳統(tǒng)的 library cache pin ,所以可以利用這一特性,模擬 library cache pin/lock 。
A session waits for this event when it is requesting a shared mutex pin and another
session is holding an exclusive mutex pin on the same cursor object.
Wait Time: Microseconds
Parameter Description
P1 Hash value of cursor
P2 Mutex value (top 2 bytes contains SID holding mutex in exclusive
mode, and bottom two bytes usually hold the value 0)
P3 Mutex where (an internal code locator) OR'd with Mutex Sleeps
通常造成 Cursor: pin S wait on X的原因主要有以下幾個(gè)方面:
ü shared pool大小是否合適。
如果shared pool大小通常小于負(fù)載,則通常表現(xiàn)為Cursor: pin S wait on X.如果啟用了AMM,這通常不是一個(gè)問題。
ü 頻繁的硬解析
如果硬解析頻率非常高,通常會(huì)發(fā)生這個(gè)等待事件以及伴隨cursor mutex X/cursor mutex S/latch: shared pool
ü High Version Counts
當(dāng)由于某些原因(session參數(shù),數(shù)據(jù)庫參數(shù),直方圖等)導(dǎo)致SQL版本數(shù)量過高,每次執(zhí)行SQL時(shí)將要examined(查看)一個(gè)非常長的子游標(biāo)鏈(handle list)將會(huì)導(dǎo)致硬解析成本很高以及軟解析成本也很高,導(dǎo)致其他非解析會(huì)話產(chǎn)生這個(gè)等待事件。
ü 已知的bug導(dǎo)致。
ü 解析失敗,AWR中解析失敗統(tǒng)計(jì)會(huì)很高。
可以通過查詢x$kglob或者,event 10035找到解析失敗語句。
Document 1353015.1 How to Identify Hard Parse Failures
SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql SQL>@$ORACLE_HOME/rdbms/admin/addmrpt.sql
如果awr以及addm、ash,沒有明顯有問題sql,system state dump可以幫助捕獲阻塞會(huì)話以及定位潛在問題。
(a) Non-Rac sqlplus "/ as sysdba" oradebug setmypid oradebug unlimit oradebug dump systemstate 258 wait 90 seconds oradebug dump systemstate 258 wait 90 seconds oradebug dump systemstate 258 quit (b) RAC $ sqlplus '/ as sysdba' oradebug setmypid oradebug unlimit oradebug setinst all oradebug -g all hanganalyze 4 oradebug -g all dump systemstate 258 quit
可以使用errorstack獲得進(jìn)程信息,對(duì)已經(jīng)定位的阻塞者會(huì)話使用errorstack,幫助定位問題。
$ sqlplus SQL> oradebug setospid <p.spid from above> oradebug dump errorstack 3 << wait 1min>> oradebug dump errorstack 3 << wait 1min>> oradebug dump errorstack 3 exit
v$session或v$session_wait的p2raw給出了造成cursor: pin S wait on X的會(huì)話,不同平臺(tái)不同bytes代表了sid,需要轉(zhuǎn)換成10進(jìn)制:
SQL> select p2raw,to_number(substr(to_char(rawtohex(p2raw)),1,8),'XXXXXXXX') sid from v$session where event = 'cursor: pin S wait on X'; P2RAW SID ---------------- --- 0000001F00000000 31
64 bit platforms
8 bytes are used.
Top 4 bytes hold the session id (if the mutex is held X)
Bottom 4 bytes hold the ref count (if the mutex is held S).
32 bit platforms
4 bytes are used.
Top 2 bytes hold the session id (if the mutex is held X)
Bottom 2 bytes hold the ref count (if the mutex is held S).
SELECT s.sid, t.sql_text FROM v$session s, v$sql t WHERE s.event LIKE '%cursor: pin S wait on X%' AND t.sql_id = s.sql_id
創(chuàng)建表: create table t (id number); session1: select * from v$mystat where statistic#=0; DECLARE a number; BEGIN FOR c IN 1..10000 LOOP EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_cost_adj = '||c; EXECUTE IMMEDIATE 'SELECT count(*) FROM t' into a; END LOOP; END; / session2: select * from v$mystat where statistic#=0; DECLARE a number; BEGIN FOR c IN 1..10000 LOOP EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_cost_adj = '||c; EXECUTE IMMEDIATE 'SELECT count(*) FROM t' into a; END LOOP; END; / session3: col event for a30 col p1 for 999999999999999999999 col p2 for 999999999999999999999 col p3 for 999999999999999999999 col sid for 999 col bs for 99999 select event,p1,p1raw,p2,p2raw,p3,sid,blocking_session bs from v$session where sid in (59,65); EVENT P1 P1RAW P2 P2RAW P3 SID BS ------------------------------ ---------------------- ---------------- ---------------------- ---------------- ---------------------- ---- ------ cursor: mutex S 978779761 000000003A56FE71 279172874241 0000004100000001 150323855360 59 library cache lock 1969550192 000000007564F370 2096862432 000000007CFB94E0 5373955 65 20:09:33 SYS@honor1 > / EVENT P1 P1RAW P2 P2RAW P3 SID BS ------------------------------ ---------------------- ---------------- ---------------------- ---------------- ---------------------- ---- ------ library cache lock 1969550192 000000007564F370 1700133696 000000006555FB40 5373955 59 library cache lock 1969550192 000000007564F370 2096861920 000000007CFB92E0 5373954 65 20:09:34 SYS@honor1 > / EVENT P1 P1RAW P2 P2RAW P3 SID BS ------------------------------ ---------------------- ---------------- ---------------------- ---------------- ---------------------- ---- ------ cursor: mutex S 978779761 000000003A56FE71 279172874240 0000004100000000 150323855360 59 cursor: pin S wait on X 978779761 000000003A56FE71 253403070464 0000003B00000000 21474836480 65
cursor: mutex S:
查詢?cè)斐蒫ursor: mutex S的sql: select sql_id,sql_text,version_count,executions from v$sqlarea where hash_value=&p1; # 查看mutex類型。 select * from x$mutex_sleep_history where mutex_identifier=&p1 and blocking_session=(select to_number('&p2_topbytes','xxxxxxxxxxxxxxx') from dual);
library cache loclk
select kglnaobj,kglnaown,kglhdadr from x$kglob where kglhdadr=’&p1raw’; KGLNAOBJ KGLNAOWN KGLHDADR -------------------- ---------------------------------------------------------------- ---------------- 5cc6ce3e3a56fe71 $BUILD$ 000000007564F370 Oracle 在11.2 版本引入了Cursor Build Lock 機(jī)制,這一機(jī)制使得在某個(gè)父游標(biāo)下創(chuàng)建子 游標(biāo)的工作串行化。當(dāng)獲取Build Lock 時(shí),需要持有Library Cache Lock,所以11.2版本更容易發(fā)生library cache lock。
cursor: mutex S:當(dāng)一個(gè)會(huì)話examination(查看)檢索父游標(biāo)時(shí),需要持有父游標(biāo)的library cache動(dòng)態(tài)創(chuàng)建的mutex的S共享模式,此時(shí)其他會(huì)話也看查看,就會(huì)造成cursor: mutex S
library cache lock: 當(dāng)硬解析時(shí),需要獲得build lock,build lock是排他性的,使在父游標(biāo)下創(chuàng)建子游標(biāo)串行化,此時(shí)如果其他會(huì)話也來創(chuàng)建子游標(biāo),則發(fā)生library cache lock等待build lock。
cursor pin S wait on X:當(dāng)一個(gè)會(huì)話要共享一個(gè)子游標(biāo)時(shí),其他會(huì)話正在解析,則會(huì)話需要等待其他會(huì)話解析完成,然后共享cursor,此時(shí)就會(huì)發(fā)生cursor pin S wait on X。
名稱欄目:cursorpinSwaitonX-創(chuàng)新互聯(lián)
標(biāo)題路徑:http://muchs.cn/article12/coccgc.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供微信公眾號(hào)、ChatGPT、網(wǎng)站維護(hù)、搜索引擎優(yōu)化、App設(shè)計(jì)、定制網(wǎng)站
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場,如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來源: 創(chuàng)新互聯(lián)
猜你還喜歡下面的內(nèi)容