在Oracle中，请列举一次等待事件的处理案例。

DB宝 2019-09-29

873

♣题目部分在Oracle中，请列举一次等待事件的处理案例。

     
♣答案部分

对于这道面试题而言，每个人遇到的情况不一样，答案也就不一样。只需要列举自己曾经碰到的情况，然后讲述明白即可，下面作者给出自己曾经碰到的一个案例及其处理过程。
开发人员反馈数据库运行很慢，让帮忙查查原因，那首当其冲的就是看主机的情况了。主机是AIX系统，采用topas命令可以查看主机的情况。如下图，从图中可以看出的确有一个Oracle的进程非常占用CPU资源：
根据操作系统的进程号3109012到数据库中查看相关的会话，脚本如下所示：
1SELECT A.INST_ID, A.SQL_ID, A.EVENT, A.PREV_SQL_ID, A.STATUS,A.USERNAME,A.OSUSER
2   FROM GV$SESSION A, GV$PROCESS B
3  WHERE A.PADDR = B.ADDR
4    AND B.SPID = 3109012;

可以看到该会话的等待事件是enq: PS - contention，并且有相关的SQL和OSUSER，可以联系到当时的开发人员，据说由于该SQL已经运行了1个小时了还没有运行结束，所以，就使用了CTRL+C强制停止了任务。先来看看具体的SQL内容：
1SELECT *
2   FROM GV$SQLAREA A
3  WHERE A.SQL_ID = 'cg7q9tn7u5vyx'
4    AND A.INST_ID = 1;


将SQL文本拷贝出来：
 1SELECT T.*, S.SID, S.SERIAL#, S.MACHINE, S.PROGRAM, S.OSUSER
 2  FROM (SELECT B.INST_ID,
 3               C.USERNAME,
 4               A.EVENT,
 5               TO_CHAR(A.CNT) AS SECONDS,
 6               A.SQL_ID,
 7               DBMS_LOB.SUBSTR(B.SQL_FULLTEXT, 100, 1) SQLTEXT
 8          FROM (SELECT ROWNUM RN, T.*
 9                  FROM (SELECT S.INST_ID,
10                               DECODE(S.SESSION_STATE,
11                                      'WAITING',
12                                      S.EVENT,
13                                      'CPU + WAIT FOR CPU') EVENT,
14                               S.SQL_ID,
15                               S.USER_ID,
16                               COUNT(*) CNT
17                          FROM GV$ACTIVE_SESSION_HISTORY S
18                         WHERE SAMPLE_TIME > SYSDATE - 30 / 1440
19                         GROUP BY INST_ID,
20                                  S.USER_ID,
21                                  DECODE(S.SESSION_STATE,
22                                         'WAITING',
23                                         S.EVENT,
24                                         'CPU + WAIT FOR CPU'),
25                                  S.SQL_ID
26                         ORDER BY CNT DESC) T
27                 WHERE ROWNUM < 20) A,
28               GV$SQLAREA B,
29               DBA_USERS C
30         WHERE A.SQL_ID = B.SQL_ID
31           AND A.USER_ID = C.USER_ID
32           AND A.INST_ID = B.INST_ID
33         ORDER BY CNT DESC) T,
34       GV$SESSION S
35 WHERE T.SQL_ID = S.SQL_ID(+)
36   AND T.INST_ID = S.INST_ID(+)
37 ORDER BY T.INST_ID

从文本中可以看出该SQL查询的是数据字典，对于一个有经验的开发者来讲，可以很容易地发现这个SQL中有一个非常特殊的地方DBMS_LOB.SUBSTR(B.SQL_FULLTEXT,100,1)。对CLOB类型数据的处理比较耗费资源，把该句修改为B.SQL_TEXT（SQL_TEXT为字符串类型），满足要求即可，没有必要去查询CLOB字段。
简单修改SQL语句后：
 1SELECT T.*, S.SID, S.SERIAL#, S.MACHINE, S.PROGRAM, S.OSUSER
 2  FROM (SELECT B.INST_ID,
 3               C.USERNAME,
 4               A.EVENT,
 5               TO_CHAR(A.CNT) AS SECONDS,
 6               A.SQL_ID,
 7               --DBMS_LOB.SUBSTR(B.SQL_FULLTEXT, 100, 1) SQLTEXT ,
 8               B.SQL_TEXT
 9          FROM (SELECT ROWNUM RN, T.*
10                  FROM (SELECT S.INST_ID,
11                               DECODE(S.SESSION_STATE,
12                                      'WAITING',
13                                      S.EVENT,
14                                      'CPU + WAIT FOR CPU') EVENT,
15                               S.SQL_ID,
16                               S.USER_ID,
17                               COUNT(*) CNT
18                          FROM GV$ACTIVE_SESSION_HISTORY S
19                         WHERE SAMPLE_TIME > SYSDATE - 30 / 1440
20                         GROUP BY INST_ID,
21                                  S.USER_ID,
22                                  DECODE(S.SESSION_STATE,
23                                         'WAITING',
24                                         S.EVENT,
25                                         'CPU + WAIT FOR CPU'),
26                                  S.SQL_ID
27                         ORDER BY CNT DESC) T
28                 WHERE ROWNUM < 20) A,
29               GV$SQLAREA B,
30               DBA_USERS C
31         WHERE A.SQL_ID = B.SQL_ID
32           AND A.USER_ID = C.USER_ID
33           AND A.INST_ID = B.INST_ID
34         ORDER BY CNT DESC) T,
35       GV$SESSION S
36 WHERE T.SQL_ID = S.SQL_ID(+)
37   AND T.INST_ID = S.INST_ID(+)
38 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程序员面试笔试宝典》，作者：小麦苗