Script—元数据获取AWR top timed events报告

发表于 | 分类于 ， ， | | 阅读量
字数统计: 783 | 阅读时长 ≈ 4

前言

DBA往往需要在数据库性能问题的时候快速获取数据库负载信息，而AWR此时通常作为最有效的工具之一，虽然AWR很优秀，但是众所周知也存在一些缺陷，主要体现在如下两方面：

  • 内容繁多，用时较长
  • AWR统计的是begin_snap、end_snap两个快照间间累积差异，在这之间每个快照间的变化无法体现

大家都知道AWR报告也都来源于数据库相关视图，因此只要我们弄清楚这些元数据的关系，就能按照我们自己的意愿组织我们自己所最关心的报告数据。

脚本

如下，这是自己参考相关资料总结的top timed events报告脚本，如果有需要可以通过下文的链接下载

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
-- +----------------------------------------------------------------------------+
-- |                          	  runanyun Mapele                               |
-- |                            wuyu.xiao@gmail.com                             |
-- |             http://mapele.coding.me/ && http://mapele.github.io/           |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle 11g(RAC)                                                 |
-- | FILE     : awr_top_timed_events.sql                                        |
-- | PURPOSE  : 通过裸数据获取AWR中连续快照间的Top Timed Events报告.               |
-- +----------------------------------------------------------------------------+
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET VER OFF
SET FEEDBACK OFF
COLUMN PCT           FORMAT a15              HEADING 'PCT of DB TIMES'
COLUMN EVENT         FORMAT a40              HEADING 'EVENT'
COLUMN TIMES         FORMAT 999,999,990.99   HEADING 'TIMES(S)'
COLUMN INST_NO       FORMAT 99          	 HEADING 'INSNO'
COLUMN SNAP_ID       FORMAT 9999999      	 HEADING 'SNAP_ID'
PROMPT
BREAK ON INST_NO skip page
ACCEPT DAYS 		 number PROMPT "ENTER latest snap days: " default 7
SELECT INSTANCE_NUMBER INST_NO,
       SNAP_ID, 
	   TO_CHAR(BEGIN_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS') BEGIN_INTERVAL_TIME
  FROM DBA_HIST_SNAPSHOT
 WHERE BEGIN_INTERVAL_TIME > TRUNC(SYSDATE) - &DAYS
 ORDER BY INSTANCE_NUMBER,BEGIN_INTERVAL_TIME;
PROMPT 
ACCEPT INST_NO 		   number PROMPT "ENTER INSTANCE_NUMBER: " default 0
ACCEPT BEGIN_SNAP 	   number PROMPT "ENTER BEGIN_SNAP: "
ACCEPT END_SNAP   	   number PROMPT "ENTER END_SNAP: "
COMPUTE sum of TIMES on SNAP_ID
PROMPT
PROMPT Listing top timed events by instance no and snap id ...
BREAK ON INST_NO ON SNAP_ID skip page
SELECT A.INST_NO
      ,TO_CHAR(B.BEGIN_INTERVAL_TIME, 'mmdd hh24mi') SNAP_TIME
      ,A.SNAP_ID
      ,A.EVENT
      ,A.PCT || '%' PCT
      ,A.TIMES
  FROM (SELECT INSTANCE_NUMBER INST_NO
              ,SNAP_ID-1 SNAP_ID
              ,EVENT
              ,ROUND(TIMES,2) TIMES
              ,ROUND(RATIO_TO_REPORT(TIMES) OVER(PARTITION BY SNAP_ID) * 100,2) PCT
          FROM (SELECT INSTANCE_NUMBER
                      ,'DB CPU' EVENT
                      ,SNAP_ID
                      ,VALUE / 1E6 - LAG(VALUE) OVER(PARTITION BY INSTANCE_NUMBER, STAT_NAME ORDER BY SNAP_ID) / 1E6 TIMES
                  FROM DBA_HIST_SYS_TIME_MODEL
                 WHERE STAT_NAME = 'DB CPU'
				   AND INSTANCE_NUMBER= CASE WHEN &INST_NO = 0 THEN INSTANCE_NUMBER ELSE &INST_NO END
                   AND SNAP_ID BETWEEN &BEGIN_SNAP AND &END_SNAP
                UNION ALL
                SELECT INSTANCE_NUMBER
                      ,'cpu on queue time' EVENT
                      ,SNAP_ID
                      ,NVL(VALUE, 0) / 100 VALUE
                  FROM DBA_HIST_OSSTAT T
                 WHERE STAT_NAME = 'RSRC_MGR_CPU_WAIT_TIME'
                   AND INSTANCE_NUMBER= CASE WHEN &INST_NO = 0 THEN INSTANCE_NUMBER ELSE &INST_NO END
                   AND SNAP_ID BETWEEN &BEGIN_SNAP AND &END_SNAP
                UNION ALL
                SELECT INSTANCE_NUMBER
                      ,EVENT_NAME
                      ,SNAP_ID
                      ,TIME_WAITED_MICRO_FG / 1E6 -
                       LAG(TIME_WAITED_MICRO_FG) OVER(PARTITION BY INSTANCE_NUMBER, EVENT_NAME ORDER BY SNAP_ID) / 1E6
                  FROM DBA_HIST_SYSTEM_EVENT
                 WHERE WAIT_CLASS != 'Idle'
                   AND INSTANCE_NUMBER= CASE WHEN &INST_NO = 0 THEN INSTANCE_NUMBER ELSE &INST_NO END
                   AND SNAP_ID BETWEEN &BEGIN_SNAP AND &END_SNAP)
         WHERE TIMES > 0) A
      ,DBA_HIST_SNAPSHOT B
 WHERE PCT > 1
   AND A.SNAP_ID = B.SNAP_ID
   AND A.INST_NO = B.INSTANCE_NUMBER
   AND A.SNAP_ID != &BEGIN_SNAP-1
   AND event != 'cpu on queue time'
 ORDER BY A.INST_NO
         ,SNAP_ID
         ,TIMES DESC;

AWR top timed events报告

说明

  • 大家都知道：DB time = DB CPU +non idle wait time+ os cpu run queue time + truer gap time，os cpu run queue time 在os层是一个较难准确把握的指标，有兴趣的同学可以参考这篇博文对它的研究：How OS CPU Run Queue Time Relates To Oracle Database Time 这里我直接使用DBA_HIST_OSSTAT中的RSRC_MGR_CPU_WAIT_TIME统计值。关于该值的说明可以参考V$OSSTAT。
  • 为了保持与AWR报告中展示一致性，已在最终结果中过滤掉“cpu on queue time”，如果有需要请注释掉“AND event != ‘cpu on queue time’”
  • 脚本已经在oracle 11.2.0.1、11.2.0.4单实例和RAC环境下通过测试

脚本文件

点击下载

参考

SQL玩转AWR裸数据-云和恩墨
循序渐进解读Oracle AWR性能分析报告