【DB筆試面試653】在Oracle中，請列舉一次等待事件的處理案例。
- 2019 年 10 月 10 日
- 筆記
題目部分
在Oracle中，請列舉一次等待事件的處理案例。
♣
答案部分
對於這道面試題而言，每個人遇到的情況不一樣，答案也就不一樣。只需要列舉自己曾經碰到的情況，然後講述明白即可，下面作者給出自己曾經碰到的一個案例及其處理過程。
開發人員回饋資料庫運行很慢，讓幫忙查查原因，那首當其衝的就是看主機的情況了。主機是AIX系統，採用topas命令可以查看主機的情況。如下圖，從圖中可以看出的確有一個Oracle的進程非常佔用CPU資源：
根據作業系統的進程號3109012到資料庫中查看相關的會話，腳本如下所示：
SELECT A.INST_ID, A.SQL_ID, A.EVENT, A.PREV_SQL_ID, A.STATUS,A.USERNAME,A.OSUSER FROM GV$SESSION A, GV$PROCESS B WHERE A.PADDR = B.ADDR AND B.SPID = 3109012;
可以看到該會話的等待事件是enq: PS – contention，並且有相關的SQL和OSUSER，可以聯繫到當時的開發人員，據說由於該SQL已經運行了1個小時了還沒有運行結束，所以，就使用了CTRL+C強制停止了任務。先來看看具體的SQL內容：
SELECT * FROM GV$SQLAREA A WHERE A.SQL_ID = 'cg7q9tn7u5vyx' AND A.INST_ID = 1;
將SQL文本拷貝出來：
SELECT T.*, S.SID, S.SERIAL#, S.MACHINE, S.PROGRAM, S.OSUSER FROM (SELECT B.INST_ID, C.USERNAME, A.EVENT, TO_CHAR(A.CNT) AS SECONDS, A.SQL_ID, DBMS_LOB.SUBSTR(B.SQL_FULLTEXT, 100, 1) SQLTEXT FROM (SELECT ROWNUM RN, T.* FROM (SELECT S.INST_ID, DECODE(S.SESSION_STATE, 'WAITING', S.EVENT, 'CPU + WAIT FOR CPU') EVENT, S.SQL_ID, S.USER_ID, COUNT(*) CNT FROM GV$ACTIVE_SESSION_HISTORY S WHERE SAMPLE_TIME > SYSDATE - 30 / 1440 GROUP BY INST_ID, S.USER_ID, DECODE(S.SESSION_STATE, 'WAITING', S.EVENT, 'CPU + WAIT FOR CPU'), S.SQL_ID ORDER BY CNT DESC) T WHERE ROWNUM < 20) A, GV$SQLAREA B, DBA_USERS C WHERE A.SQL_ID = B.SQL_ID AND A.USER_ID = C.USER_ID AND A.INST_ID = B.INST_ID ORDER BY CNT DESC) T, GV$SESSION S WHERE T.SQL_ID = S.SQL_ID(+) AND T.INST_ID = S.INST_ID(+) ORDER BY T.INST_ID
從文本中可以看出該SQL查詢的是數據字典，對於一個有經驗的開發者來講，可以很容易地發現這個SQL中有一個非常特殊的地方DBMS_LOB.SUBSTR(B.SQL_FULLTEXT,100,1)。對CLOB類型數據的處理比較耗費資源，把該句修改為B.SQL_TEXT（SQL_TEXT為字元串類型），滿足要求即可，沒有必要去查詢CLOB欄位。
簡單修改SQL語句後：
SELECT T.*, S.SID, S.SERIAL#, S.MACHINE, S.PROGRAM, S.OSUSER FROM (SELECT B.INST_ID, C.USERNAME, A.EVENT, TO_CHAR(A.CNT) AS SECONDS, A.SQL_ID, --DBMS_LOB.SUBSTR(B.SQL_FULLTEXT, 100, 1) SQLTEXT , B.SQL_TEXT FROM (SELECT ROWNUM RN, T.* FROM (SELECT S.INST_ID, DECODE(S.SESSION_STATE, 'WAITING', S.EVENT, 'CPU + WAIT FOR CPU') EVENT, S.SQL_ID, S.USER_ID, COUNT(*) CNT FROM GV$ACTIVE_SESSION_HISTORY S WHERE SAMPLE_TIME > SYSDATE - 30 / 1440 GROUP BY INST_ID, S.USER_ID, DECODE(S.SESSION_STATE, 'WAITING', S.EVENT, 'CPU + WAIT FOR CPU'), S.SQL_ID ORDER BY CNT DESC) T WHERE ROWNUM < 20) A, GV$SQLAREA B, DBA_USERS C WHERE A.SQL_ID = B.SQL_ID AND A.USER_ID = C.USER_ID AND A.INST_ID = B.INST_ID ORDER BY CNT DESC) T, GV$SESSION S WHERE T.SQL_ID = S.SQL_ID(+) AND T.INST_ID = S.INST_ID(+) ORDER BY T.INST_ID;
執行了一下大約花了6秒就出結果了。通過這個小的改動，效率有明顯的提升，原來花費1小時都沒有運行出結果，而通過優化後，6秒就得到了運行結果。
查詢MOS文檔，可知該等待事件是由BUG引起，最好的辦法就是優化SQL。
& 說明：
有關等待事件的更多處理案例可以參考作者BLOG：http://blog.itpub.net/26736162/viewspace-2123996/、http://blog.itpub.net/26736162/viewspace-2124767/、http://blog.itpub.net/26736162/viewspace-2124771/、http://blog.itpub.net/26736162/viewspace-2124369/、http://blog.itpub.net/26736162/viewspace-2124735/、http://blog.itpub.net/26736162/viewspace-1985380/
本文選自《Oracle程式設計師面試筆試寶典》，作者：小麥苗
