【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程序员面试笔试宝典》，作者：小麦苗
