游標(biāo)的概念
游標(biāo)是SQL的一個內(nèi)存工作區(qū),由系統(tǒng)或用戶以變量的形式定義。游標(biāo)的作用就是用于臨時存儲從數(shù)據(jù)庫中提取的數(shù)據(jù)塊。
在某些情況下,需要把數(shù)據(jù)從存放在磁盤的表中調(diào)到計算機內(nèi)存中進行處理,最后將處理結(jié)果顯示出來或最終寫回數(shù)據(jù)庫。這樣數(shù)據(jù)處理的速度才會提高,否則頻繁的磁盤數(shù)據(jù)交換會降低效率。
游標(biāo)有兩種類型:顯式游標(biāo)和隱式游標(biāo)。
在前述程序中用到的SELECT...INTO...查詢語句,一次只能從數(shù)據(jù)庫中提取一行數(shù)據(jù),對于這種形式的查詢和DML操作,系統(tǒng)都會使用一個隱式游標(biāo)。
但是如果要提取多行數(shù)據(jù),就要由程序員定義一個顯式游標(biāo),并通過與游標(biāo)有關(guān)的語句進行處理。顯式游標(biāo)對應(yīng)一個返回結(jié)果為多行多列的SELECT語句。
游標(biāo)一旦打開,數(shù)據(jù)就從數(shù)據(jù)庫中傳送到游標(biāo)變量中,然后應(yīng)用程序再從游標(biāo)變量中分解出需要的數(shù)據(jù),并進行處理。
--隱式游標(biāo)
如前所述,DML操作和單行SELECT語句會使用隱式游標(biāo),它們是:
插入操作:INSERT。
更新操作:UPDATE。
刪除操作:DELETE。
單行查詢操作:SELECT ... INTO ...。
當(dāng)系統(tǒng)使用一個隱式游標(biāo)時,可以通過隱式游標(biāo)的屬性來了解操作的狀態(tài)和結(jié)果,進而控制程序的流程。
隱式游標(biāo)可以使用名字SQL來訪問,但要注意,通過SQL游標(biāo)名總是只能訪問前一個DML操作或單行SELECT操作的游標(biāo)屬性。所以通常在剛剛執(zhí)行完操作之后,立即使用SQL游標(biāo)名來訪問屬性。
游標(biāo)的屬性有四種,如下所示:
故障類型及解析
原因分析:
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.(當(dāng)會話請求一個共享互斥鎖引腳,而另一個會話持有同一個游標(biāo)對象上的互斥鎖引腳時,會話等待該事件。)
這個事件的出現(xiàn)受到很多因素的影響,在高并發(fā)的情況下:
sga自動管理,sga的頻繁擴展和收縮。
過渡硬解析,造成library cache中的cursor object被頻繁的reload。
bug。
案例分析一:
數(shù)據(jù)庫bug原因引發(fā)cursor:pin S wait on X等待事件。
1)查看等待事件詳情
--查看系統(tǒng)上現(xiàn)有的快照信息:
SQL> col mintime for a30
SQL> col maxtime for a30
SQL> select min(snap_id) minid, max(snap_id) maxid,
2 to_char(min(begin_interval_time),yyyy-mm-dd hh24:mi:ss) mintime,
3 to_char(max(end_interval_time),yyyy-mm-dd hh24:mi:ss) maxtime
4 from dba_hist_snapshot;
--根據(jù)快照信息,我們來查看一下對應(yīng)的等待事件分類情況:
SQL> 1 select wait_class_id,wait_class, count(*) cnt
2 from dba_hist_active_sess_history
3 where snap_id between 78303 and 78472
4 group by wait_class_id, wait_class
5 * order by 3
WAIT_CLASS_ID WAIT_CLASS CNT
------------- -------------------- ----------
2723168908 Idle 2
4166625743 Administrative 6
2000153315 Nication 829
3290255840 Configuration 4128
4108307767 System I/O 9234
1893977003 Other 11043
3386400367 Commit 26802
1740759767 User I/O 28076
3875070507 Concurrency 888984
--查看具體的等待事件情況:
SQL> select event_id, event, count(*) cnt
2 from dba_hist_active_sess_history
3 where snap_id between 78303 and 78472
4 and wait_class_id=3875070507
5 group by event_id, event
6 order by 3;
EVENT_ID EVENT CNT
---------- -------------------------------- ---------
877525844 cursor: mutex X 1
86156091 os thread startup 6
1242501677 latch: library cache pin 7
1714089451 row cache lock 7
2952162927 library cache load lock 10
2802704141 library cache pin 22
2032051689 latch: library cache lock 45
1117386924 latch: row cache objects 60
1394127552 latch: In memory undo latch 68
2779959231 latch: cache buffers chains 873
2161531084 buffer busy waits 4286
916468430 library cache lock 4549
2696347763 latch: shared pool 12360
589947255 latch: library cache 12718
1729366244 cursor: pin S wait on X 853972
2)查找出pin S wait on X對應(yīng)的SQL
SQL> select sql_id, count(*) cnt
from dba_hist_active_sess_histo 2 ry
where snap_id between 78303 and 3 78472
4 and event_id in (1729366244)
5 group by sql_id
having count(*)> 6 100
order by 7 2 desc;
SQL_ID CNT
------------- ----------
0nuvj12m3ryvy 853880
--接著上面的查詢我們可以從awr歷史信息里找到這些sql語句主要在等待那些對象:
SQL> select owner,current_obj#,object_name,object_type, count(*) cnt
2 from dba_hist_active_sess_history a, dba_objects b
3 where snap_id between 78303 and 78472
4 and event_id in (1729366244)
5 and sql_id in (0nuvj12m3ryvy)
6 and a.current_obj#=b.object_id
7 group by owner,current_obj#,object_name,object_type
8 having count(*) > 10
9 order by 5 desc;
OWNER CURRENT_OBJ# OBJECT_NAME OBJECT_TYPE CNT
---------- ------------ ------------------------------ ------------------- ----------
SETTLE 49326 T_OPERATE_LOG TABLE 654899
SYS 73541 LOG$INFORMATION TABLE 16337
SETTLE 48117 G_MENU_RIGHT TABLE 9684
SETTLE 141993 CONFIG_UNIX INDEX 9567
SETTLE 136520 T_MANAGE_WARN_CONFIG TABLE 9565
SETTLE 51955 T_BILL_LOG TABLE 9520
SETTLE 48128 G_ROLE TABLE 9458
3)下面確認(rèn)等待的數(shù)據(jù)庫是否過于集中,也就是確認(rèn)是否存在熱塊兒問題:
SQL> select current_file#,current_block#, count(*) cnt
2 from dba_hist_active_sess_history
3 where snap_id between 78303 and 78472
4 and event_id in (1729366244)
and sql_id in (0nuvj12m3ryv 5 y)
6 and current_obj# in (49326,48117,141993,136520,51955,48128)
7 group by current_file#, current_block#
8 having count(*)>50
9 order by 3;
CURRENT_FILE# CURRENT_BLOCK# CNT
------------- -------------- ----------
9 4436 9458
276 839623 9500
246 857417 9520
276 839495 9521
2 532140 9565
55 1153960 9567
276 840134 9648
25 739537 9684
...
275 905767 16209
275 909728 16213
275 904723 16262
275 908888 16263
276 844986 16275
276 844862 16347
275 906325 16394
275 904842 16403
275 908197 24737
276 841357 25472
4)排除熱快根據(jù)MOS提示的
How to Determine the Blocking Session for Event: cursor: pin S wait on X (Doc ID 786507.1).
Cursor: pin S wait on X當(dāng)一個session為一個與pin相關(guān)的共享操作(such as executing a cursor)請求一個mutex時,該session會有Cursor: pin S wait on X等待事件。
但是該mutex不能被授權(quán),因為該mutex正在被其他session以排他模式持有(比如 parsing the cursor)。
v$session or v$session_wait中的p2raw列 給出了 cursor: pin S wait on X等待事件的阻塞者session(持有者session)按照MOS文檔方法查看。
SQL> select p2raw from v$session where event = cursor: pin S wait on X;
P2RAW
----------------
000001B200000000
000001B200000000
000001B200000000
...
000001B200000000
--操作系統(tǒng)是64位的,用命令來換算得出。
SQL> select p2raw,to_number(substr(to_char(rawtohex(p2raw)),1,8),XXXXXXXX) sid
2 from v$session
3 where event = cursor: pin S wait on X;
P2RAW SID
---------------- ----------
000001B200000000 434
000001B200000000 434
000001B200000000 434
...
000001B200000000 434
--找到block session會話關(guān)系:
SQL> select p1, p2raw, count(*) from v$session
where event =cursor: pin S 2 wait on X
and wait_time = 0
group by p1, p2raw;
P1 P2RAW COUNT(*)
---------- ---------------- ----------
2788948862 000001B200000000 59
--參數(shù)說明:
p1 = the mutex Id
This has the same definition as v$mutex_sleep_history.mutex_identifier
p2raw = holding Session Id | Ref Count
The most significant bytes always store the Holding Session Id (Holding SId).
The least significant bytes always store the Ref Count.
SQL> select sid,serial#,SQL_ID,BLOCKING_SESSION,BLOCKING_SESSION_STATUS,EVENT
2 from v$session where SID=434;
SID SERIAL# SQL_ID BLOCKING_SESSION BLOCKING_SE EVENT
---------- ---------- ------------- ---------------- -----------
434 34745 0nuvj12m3ryvy UNKNOWN single-task message
SQL> select sid,serial#,SQL_ID,BLOCKING_SESSION,BLOCKING_SESSION_STATUS,EVENT
2 from v$session where event =cursor: pin S wait on X ;
SID SERIAL# SQL_ID BLOCKING_SESSION BLOCKING_SE EVENT
---------- ---------- ------------- ---------------- -----------
332 59875 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X
333 27868 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X
350 54031 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X
365 5053 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X
...
1043 53471 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X
1082 13982 0nuvj12m3ryvy UNKNOWN cursor: pin S wait on X
--查看游標(biāo)數(shù)量因為當(dāng)前系統(tǒng)version count并不高,所以判斷遇到了bug了,。
SQL> select sql_id,version_count from v$sqlarea where version_count> 100 order by 2 desc ;
no rows selected
發(fā)生cursor: pin S wait on X原因:
Frequent Hard Parses If the frequency of Hard Parsing is
extremely high, then contention can occur on this pin.High
Version Counts When Version counts become excessive, a long
chain of versions needs to be examined and this can lead to
contention on this event Known bugs.
Bug 5907779 - Self deadlock hang on "cursor: pin S wait on
X" (typically from DBMS_STATS) [ID 5907779.8]Bug 7568642:
BLOCKING_SESSION EMPTY FOR "CURSOR: PIN S WAIT ON X".
原因分析:
當(dāng)一個session為一個與pin相關(guān)的共享操作(such as executing a cursor)請求一個mutex時,該session會有Cursor: pin S wait on X等待事件。但是該mutex不能被授權(quán),因為該mutex正在被其他session以排他模式持有(比如 parsing the cursor)。
觸發(fā)該事件的情況有:
Mutex持有者得不到CPU。
過多的子游標(biāo) High Version Counts,過多的子游標(biāo)版本Version Count可能導(dǎo)致Mutex 爭用,一般一個SQL的Version Count不要高于500。
更新或構(gòu)件SQL統(tǒng)計信息V$SQLSTATS。
已經(jīng)被KILLED的SESSION仍持有Mutex。
案例分析一:
通過dba_hist_active_sess_history查看當(dāng)時主要的等待事件:(開始出現(xiàn)Cursor:Mute X的時間),找到造成cursor: mutex X的對應(yīng)SQL。
SQL_ID COUNT(*)
------------- ----------
b1gtr4yvjk7uc 149275
--造成Cursor:mute X的主要原因:
select USER_BIND_PEEK_MISMATCH,count(*) from v$sql_shared_cursor where sql_id=b1gtr4yvjk7uc group by USER_BIND_PEEK_MISMATCH;
U COUNT(*)
- ----------
N 4
Y 21
--查看AWR報告中Mutex Sleep Summary:
1)Wait time較長的Mutex type和BUG 28889389相匹配。
原因:
在觀察到多個唯一會話在以下堆棧下的同一游標(biāo)上以獨占模式等待父游標(biāo)互斥鎖后,此錯誤被命中。cursor: mutex X 將被用于父游標(biāo)以找出該特定的匹配子游標(biāo)SQL。在高并發(fā)下,當(dāng)所有會話同時執(zhí)行相同的光標(biāo)導(dǎo)致此互斥爭用, 該BUG沒有workaround只能通過打補丁Patch 28889389來修復(fù)。
2)與本case比較相近的另一個BUG是BUG 32755517 : HIGH VERSION COUNT DUE TO USER_BIND_PEEK_MISMATCH AFTER FLUSH SHARED POOL。
該BUG的觸發(fā)條件時Sql profile且里包含了_optim_peek_user_binds = false。這個問題直到23.1才被修復(fù)。
--Workaround的方法有2種:
3)建議將_cursor_obsolete_threshold降低。
這個參數(shù)可以控制version count,該值目前為12C默認(rèn)值,建議將其設(shè)置成1000(該參數(shù)時靜態(tài)參數(shù),需要重啟生效)。
High Version Counts For SQL Statements (>1024) Post Upgrade To 12.2 and Above Causing Database Slow Performance (Doc ID 2431353.1)
4)故障分析總結(jié)如下:
根據(jù)數(shù)據(jù)庫當(dāng)前已有信息,我們可以推測和上文提到的兩個BUG相似,如果需要進一步匹配,需要通過call stack來進行分析,但當(dāng)前數(shù)據(jù)庫并沒有保留該類信息。
Hanganalyze使用方法如下:
---------單機
SQL> sqlplus -prelim / as sysdba
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug hanganalyze 3
SQL> oradebug hanganalyze 3
SQL> oradebug hanganalyze 3
SQL> oradebug dump systemstate 266 ---266或者258
SQL> oradebug dump systemstate 266
SQL> oradebug dump systemstate 266
SQL> oradebug tracefile_name
------------rac
SQL> sqlplus -prelim / as sysdba
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug -g all hanganalyze 3
SQL> oradebug -g all hanganalyze 3
SQL> oradebug -g all hanganalyze 3
SQL> oradebug -g all dump systemstate 266
SQL> oradebug -g all dump systemstate 266
SQL> oradebug -g all dump systemstate 266
SQL> oradebug tracefile_name
5)處理建議總結(jié):
取消SQL ID為b1gtr4yvjk7uc的sql profile。
當(dāng)問題重現(xiàn)的情況下,請及時使用hanganalyze收集信息,并取出系統(tǒng)留存,以便未來定位BUG。
建議將_cursor_obsolete_threshold降低。
案例分析二:
BIND_EQUIV_FAILURE綁定值的選擇性與現(xiàn)有子游標(biāo)的選擇性不匹配,加上頻次執(zhí)行量較大創(chuàng)建大量子游標(biāo)造成cursor:mutex X等待事件。
1)Sql執(zhí)行頻率:
SNAP_ID NODE BEGIN_INTERVAL_TIME SQL_ID EXECS AVG_ETIME AVG_IO
30615 2 22-JAN-22 08.00.08.843 AM bq31p1f8gz5fg 796 .004 117.893216
30616 2 22-JAN-22 09.00.36.368 AM bq31p1f8gz5fg 6,521 .004 117.042018
30617 1 22-JAN-22 10.00.04.663 AM bq31p1f8gz5fg 9,612,074 14.498 161.605813
30617 2 22-JAN-22 10.00.04.740 AM bq31p1f8gz5fg 10,458 .004 124.729489
2)查看執(zhí)行計劃:
SNAP_ID NODE BEGIN_INTERVAL_TIME SQL_ID PLAN_HASH_VALUE EXECS AVG_ETIME AVG_IO
------ ------ ------------------------------ ------------- --------------- --------- ------------ ----------
30415 2 14-JAN-22 12.00.04.921 AM bq31p1f8gz5fg 212347024 1,695 .006 164.184661
...
30615 2 22-JAN-22 08.00.08.843 AM bq31p1f8gz5fg 796 .004 117.893216
30616 2 22-JAN-22 09.00.36.368 AM bq31p1f8gz5fg 6,521 .004 117.042018
30617 1 22-JAN-22 10.00.04.663 AM bq31p1f8gz5fg 9,612,074 14.498 161.605813
30617 2 22-JAN-22 10.00.04.740 AM bq31p1f8gz5fg 10,458 .004 124.729489
30618 1 22-JAN-22 11.00.33.928 AM bq31p1f8gz5fg 23,631 .004 123.28086
30618 2 22-JAN-22 11.00.34.020 AM bq31p1f8gz5fg 6,177 .004 130.681884
30619 1 22-JAN-22 12.00.01.677 PM bq31p1f8gz5fg 16,435 .004 123.458108
30619 2 22-JAN-22 12.00.01.755 PM bq31p1f8gz5fg 4,803 .004 128.19467
30620 1 22-JAN-22 01.00.19.436 PM bq31p1f8gz5fg 15,061 .004 124.279397
30620 2 22-JAN-22 01.00.19.355 PM bq31p1f8gz5fg 4,322 .004 131.075659
解決方法:刪除不匹配的執(zhí)行計劃,選擇正確執(zhí)行計劃。
文章版權(quán)歸作者所有,未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為,您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請注明本文地址:http://specialneedsforspecialkids.com/yun/129570.html
摘要:本質(zhì)上所有查詢的數(shù)據(jù)都是從游標(biāo)來的。的作用是從游標(biāo)中提取一批數(shù)據(jù),具體提取多少則是由決定。同時注意我們已經(jīng)有了一個游標(biāo)。為了便于理解,我們下面還是稱之為游標(biāo)超時。 前言 聊一聊一個最基本的問題,游標(biāo)的使用。可能你從來沒有注意過它,但其實它在MongoDB的使用中是普遍存在的,也存在一些常見的坑需要引起我們的注意。 在寫這個系列文章時,我會假設(shè)讀者已經(jīng)對MongoDB有了最基礎(chǔ)的了解,因...
摘要:避免頻繁創(chuàng)建和刪除臨時表,以減少系統(tǒng)表資源的消耗。臨時表并不是不可使用,適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行?,例如,?dāng)需要重復(fù)引用大型表或常用表中的某個數(shù)據(jù)集時。 網(wǎng)上關(guān)于SQL優(yōu)化的教程很多,但是比較雜亂。近日有空整理了一下,寫出來跟大家分享一下,其中有錯誤和不足的地方,還請大家糾正補充。 這篇文章我花費了大量的時間查找資料、修改、排版,希望大家閱讀之后,感覺好的話推薦給更多的人,讓更...
閱讀 1347·2023-01-11 13:20
閱讀 1685·2023-01-11 13:20
閱讀 1133·2023-01-11 13:20
閱讀 1860·2023-01-11 13:20
閱讀 4101·2023-01-11 13:20
閱讀 2705·2023-01-11 13:20
閱讀 1386·2023-01-11 13:20
閱讀 3599·2023-01-11 13:20