【DB笔试面试653】在Oracle中，请列举一次等待事件的处理案例。

小麦苗DBA宝典

发布于 2019-09-29 16:34:36

8510

发布于 2019-09-29 16:34:36

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

♣

题目部分

在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/