题目部分
【DB笔试面试823】在Oracle中,如何查看过去某一段时间数据库系统的会话是否有问题?
♣
答案部分
可以通过DBA_HIST_ACTIVE_SESS_HISTORY视图来进行查询,首先查询指定时间段的等待事件,下例中的SQL语句查询的是2016年5月10号下午17点30分到19点30分这段时间内数据库的等待事件和SQL的执行情况,其中,COUNTS列的值比较大的就是SQL执行时间较长的,需要特别关注:
SELECT D.EVENT, D.SQL_ID, COUNT(1) COUNTS
FROM DBA_HIST_ACTIVE_SESS_HISTORY D
WHERE D.SAMPLE_TIME >= TO_DATE('2016-05-10 17:30:00', 'YYYY-MM-DD HH24:MI:SS')
AND D.SAMPLE_TIME <= TO_DATE('2016-05-10 19:30:00', 'YYYY-MM-DD HH24:MI:SS')
GROUP BY D.EVENT,D.SQL_ID;
下面的SQL语句可以查询到具体SQL的扫描操作,初步预估SQL问题:
SELECT TO_CHAR(D.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') SAMPLE_TIME,
D.SQL_ID,
D.SQL_PLAN_HASH_VALUE,
D.SQL_PLAN_OPERATION,
D.SQL_PLAN_OPTIONS,
D.EVENT
FROM DBA_HIST_ACTIVE_SESS_HISTORY D
WHERE D.SAMPLE_TIME >= TO_DATE('2016-05-10 17:30:00', 'YYYY-MM-DD HH24:MI:SS')
AND D.SAMPLE_TIME <= TO_DATE('2016-05-10 19:30:00', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY D.SNAP_ID;
根据以上的SQL语句可以知道,对表做的是否是全表扫描,以及当时会话的等待事件是什么,然后就可以根据等待事件进行SQL分析了。
如下的SQL语句可以查询某一段时间内,会话所持有的锁信息:
SELECT D.SQL_ID,
CHR(BITAND(P1, -16777216) / 16777215) ||CHR(BITAND(P1, 16711680) / 65535) "Lock",
BITAND(P1, 65535) "Mode",
COUNT(1) COUNTS,
COUNT(DISTINCT D.SESSION_ID) COUNTS1
FROM DBA_HIST_ACTIVE_SESS_HISTORY D
WHERE D.SAMPLE_TIME >= TO_DATE('2016-05-10 17:30:00', 'YYYY-MM-DD HH24:MI:SS')
AND D.SAMPLE_TIME <= TO_DATE('2016-05-10 19:30:00', 'YYYY-MM-DD HH24:MI:SS')
AND D.EVENT = 'enq: TX - row lock contention'
GROUP BY D.SQL_ID,
(CHR(BITAND(P1, -16777216) / 16777215) ||
CHR(BITAND(P1, 16711680) / 65535)),
(BITAND(P1, 65535));
如下的SQL语句可以查询系统问题时间段内的会话详情:
SELECT D.CURRENT_OBJ#,
D.CURRENT_FILE#,
D.CURRENT_BLOCK#,
D.CURRENT_ROW#,
D.EVENT,
D.P1TEXT,
D.P1,
D.P2TEXT,
D.P2,
CHR(BITAND(P1, -16777216) / 16777215) ||CHR(BITAND(P1, 16711680) / 65535) "Lock",
BITAND(P1, 65535) "Mode",
D.BLOCKING_SESSION,
D.BLOCKING_SESSION_STATUS,
D.BLOCKING_SESSION_SERIAL#,
D.SQL_ID,
TO_CHAR(D.SAMPLE_TIME, 'YYYYMMDDHH24MISS') SAMPLE_TIME
FROM DBA_HIST_ACTIVE_SESS_HISTORY D
WHERE D.SAMPLE_TIME >= TO_DATE('2016-05-10 17:30:00', 'YYYY-MM-DD HH24:MI:SS')
AND D.SAMPLE_TIME <= TO_DATE('2016-05-10 19:30:00', 'YYYY-MM-DD HH24:MI:SS')
AND D.EVENT = 'enq: TX - row lock contention';
& 说明:
有关一些具体的分析过程可以参考我的BLOG案例:http://blog.itpub.net/26736162/viewspace-2123996/
本文选自《Oracle程序员面试笔试宝典》,作者:小麦苗