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)主要從事成都做網站、網站建設、網頁設計、企業(yè)做網站、公司建網站等業(yè)務。立足成都服務陜西,10余年網站建設經驗,價格優(yōu)惠、服務專業(yè),歡迎來電咨詢建站服務: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.
# 通常,這個等待事件只是一個現(xiàn)象并不是原因,通常是需要更深層次的優(yōu)化或者已知的其他問題導致。
cursor: pin S , cursor: pin X , cursor: pin S wait on X 這三個等待事件,實際上就是替代了 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 (察看)父游標會發(fā)生父游標的 cursor pin S 。
這里需要強調一下,它們只是替換了訪問 cursor 的 library cache pin ,而對于訪問 procedure 這種實體對象,依然是傳統(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的原因主要有以下幾個方面:
ü shared pool大小是否合適。
如果shared pool大小通常小于負載,則通常表現(xiàn)為Cursor: pin S wait on X.如果啟用了AMM,這通常不是一個問題。
ü 頻繁的硬解析
如果硬解析頻率非常高,通常會發(fā)生這個等待事件以及伴隨cursor mutex X/cursor mutex S/latch: shared pool
ü High Version Counts
當由于某些原因(session參數,數據庫參數,直方圖等)導致SQL版本數量過高,每次執(zhí)行SQL時將要examined(查看)一個非常長的子游標鏈(handle list)將會導致硬解析成本很高以及軟解析成本也很高,導致其他非解析會話產生這個等待事件。
ü 已知的bug導致。
ü 解析失敗,AWR中解析失敗統(tǒng)計會很高。
可以通過查詢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可以幫助捕獲阻塞會話以及定位潛在問題。
(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獲得進程信息,對已經定位的阻塞者會話使用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的會話,不同平臺不同bytes代表了sid,需要轉換成10進制:
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:
查詢造成cursor: 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 機制,這一機制使得在某個父游標下創(chuàng)建子 游標的工作串行化。當獲取Build Lock 時,需要持有Library Cache Lock,所以11.2版本更容易發(fā)生library cache lock。
cursor: mutex S:當一個會話examination(查看)檢索父游標時,需要持有父游標的library cache動態(tài)創(chuàng)建的mutex的S共享模式,此時其他會話也看查看,就會造成cursor: mutex S
library cache lock: 當硬解析時,需要獲得build lock,build lock是排他性的,使在父游標下創(chuàng)建子游標串行化,此時如果其他會話也來創(chuàng)建子游標,則發(fā)生library cache lock等待build lock。
cursor pin S wait on X:當一個會話要共享一個子游標時,其他會話正在解析,則會話需要等待其他會話解析完成,然后共享cursor,此時就會發(fā)生cursor pin S wait on X。
名稱欄目:cursorpinSwaitonX-創(chuàng)新互聯(lián)
標題路徑:http://aaarwkj.com/article12/coccgc.html
成都網站建設公司_創(chuàng)新互聯(lián),為您提供微信公眾號、ChatGPT、網站維護、搜索引擎優(yōu)化、App設計、定制網站
聲明:本網站發(fā)布的內容(圖片、視頻和文字)以用戶投稿、用戶轉載內容為主,如果涉及侵權請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內容未經允許不得轉載,或轉載時需注明來源: 創(chuàng)新互聯(lián)
猜你還喜歡下面的內容