Script—元数据获取load profile报告

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

前言

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

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

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

脚本

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

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
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
-- +----------------------------------------------------------------------------+
-- |                          	  runanyun Mapele                               |
-- |                            wuyu.xiao@gmail.com                             |
-- |             http://mapele.coding.me/ && http://mapele.github.io/           |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle 11g(RAC)                                                 |
-- | FILE     : awr_load_profile.sql                                            |
-- | PURPOSE  : 通过裸数据获取AWR中连续快照间的load profile报告.                   |
-- +----------------------------------------------------------------------------+
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET VER OFF
set LINESIZE 200
SET FEEDBACK OFF
COLUMN INST_NO       FORMAT 99          	    HEADING 'INSNO'
COLUMN SNAP_ID       FORMAT 99999
COLUMN "DB time"     FORMAT 999,999,999,999
COLUMN "DB CPU"      FORMAT 9,999,999,999
COLUMN Redo          FORMAT 999,999,999
COLUMN LogicalR      FORMAT 99,999,999 
COLUMN BlockChs      FORMAT 99,999,999
COLUMN Phyr          FORMAT 99,999 
COLUMN Phyw          FORMAT 9,999,999
COLUMN Calls         FORMAT 9,999,999
COLUMN Parses        FORMAT 999,999
COLUMN HParses       FORMAT 9,999
COLUMN Logons        FORMAT 999,999
COLUMN Execs         FORMAT 9,999,999
COLUMN Rbacks        FORMAT 9,999
COLUMN Trans         FORMAT 999,999
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: "
PROMPT
PROMPT Listing load profile by instance no and snap id ...
SELECT *
  FROM (SELECT TO_CHAR(HS.BEGIN_INTERVAL_TIME, 'mmdd hh24mi') SNAP_TIME
              ,S.*
          FROM (SELECT T.INSTANCE_NUMBER INST_NO
                      ,T.SNAP_ID - 1 SNAP_ID
                      ,STAT_NAME
                      ,NVL(VALUE - LAG(VALUE)
                           OVER(PARTITION BY T.INSTANCE_NUMBER
                               ,STAT_NAME ORDER BY T.INSTANCE_NUMBER
                               ,T.SNAP_ID)
                          ,0) VALUE
                  FROM DBA_HIST_SYS_TIME_MODEL T
                 WHERE INSTANCE_NUMBER= CASE WHEN &INST_NO = 0 THEN INSTANCE_NUMBER ELSE &INST_NO END
				   AND SNAP_ID BETWEEN &BEGIN_SNAP AND &END_SNAP
                   AND STAT_NAME IN ('DB time', 'DB CPU')
                UNION ALL
                SELECT T.INSTANCE_NUMBER INST
                      ,T.SNAP_ID - 1 SNAP_ID
                      ,STAT_NAME
                      ,NVL(VALUE - LAG(VALUE)
                           OVER(PARTITION BY T.INSTANCE_NUMBER
                               ,STAT_NAME ORDER BY T.INSTANCE_NUMBER
                               ,T.SNAP_ID)
                          ,0) VALUE
                  FROM DBA_HIST_SYSSTAT T
                 WHERE INSTANCE_NUMBER= CASE WHEN &INST_NO = 0 THEN INSTANCE_NUMBER ELSE &INST_NO END
				   AND SNAP_ID BETWEEN &BEGIN_SNAP AND &END_SNAP
                   AND STAT_NAME IN ('redo size'
                                    ,'session logical reads'
                                    ,'db block changes'
                                    ,'physical reads'
                                    ,'physical writes'
                                    ,'user calls'
                                    ,'parse count (total)'
                                    ,'parse count (hard)'
                                    ,'logons cumulative'
                                    ,'execute count'
                                    ,'user rollbacks'
                                    ,'user commits')) S
              ,DBA_HIST_SNAPSHOT HS
         WHERE S.SNAP_ID = HS.SNAP_ID
		   AND S.SNAP_ID != &BEGIN_SNAP-1)
PIVOT(SUM(VALUE)
   FOR STAT_NAME IN('DB time' AS "DB time"
                    ,'DB CPU' AS "DB CPU"
                    ,'redo size' AS "Redo"
                    ,'session logical reads' AS "LogicalR"
                    ,'db block changes' AS "BlockChs"
                    ,'physical reads' AS "Phyr"
                    ,'physical writes' AS "Phyw"
                    ,'user calls' AS "Calls"
                    ,'parse count (total)' AS "Parses"
                    ,'parse count (hard)' AS "HParses"
                    ,'logons cumulative' AS "Logons"
                    ,'execute count' AS "Execs"
                    ,'user rollbacks' AS "Rbacks"
                    ,'user commits' AS "Trans"))
 ORDER BY INST_NO
         ,SNAP_TIME;

load profile报告

说明

  • 结果集为快照间load profile 指标间的累积差
  • DB time、DB CPU 单位为微妙
  • Redo、 LogicalR、BlockChs、Phyr、Phyw 单位为块数，计算实际大小时别忘记db_block_size
  • 剩余指标单位皆为次数
  • 脚本已经在oracle 11.2.0.1、11.2.0.4单实例和RAC环境下通过测试

脚本文件

点击下载

参考

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