Script—元数据获取load profile PCT报告

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

前言

当我们使用前面介绍的Script—元数据获取load profile报告获得load profile各指标变化数据后，想简单的从数据集直接一眼看出指标的变化趋势还是相当不容易，此时我们会立马想到把数据集导入到excel中利用数据透视图展示数据，如下：

  • 原始load profile 数据集

原始load profile 数据集

  • 原始load profile 数据集产生的透视图

原始load profile 数据集产生的透视图

问题

如上图看到，因为不同指标数量值范围落差很大，导致部分指标值被挤压无法在透视图中提现变化趋势。

脚本

我们可以在前面介绍的Script—元数据获取load profile报告上，通过RATIO_TO_REPORT分析函数，计算结果集中每个值在同一指标种占比，这样就可以将数据统一起来，从而能很简单的从数据中判断出数据的变化趋势，也更方便在同一图上展示；脚本如下

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
114
-- +----------------------------------------------------------------------------+
-- |                          	  runanyun Mapele                               |
-- |                            wuyu.xiao@gmail.com                             |
-- |             http://mapele.coding.me/ && http://mapele.github.io/           |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle 11g(RAC)                                                 |
-- | FILE     : awr_load_profile_pct.sql                                        |
-- | PURPOSE  : 通过裸数据获取AWR中连续快照间的load profile pct报告.               |
-- +----------------------------------------------------------------------------+
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 9999
COLUMN "DB CPU"      FORMAT 9999
COLUMN Redo          FORMAT 9999
COLUMN LogicalR      FORMAT 9999
COLUMN BlockChs      FORMAT 9999
COLUMN Phyr          FORMAT 9999
COLUMN Phyw          FORMAT 9999
COLUMN Calls         FORMAT 9999
COLUMN Parses        FORMAT 9999
COLUMN HParses       FORMAT 9999
COLUMN Logons        FORMAT 9999
COLUMN Execs         FORMAT 9999
COLUMN Rbacks        FORMAT 9999
COLUMN Trans         FORMAT 9999
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 pct by instance no and snap id ...
SELECT *
  FROM (SELECT INST_NO
              ,SNAP_ID
              ,SNAP_TIME
              ,STAT_NAME
              ,ROUND(RATIO_TO_REPORT(VALUE) OVER(PARTITION BY STAT_NAME) * 100,2) VALUE
          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_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_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 pct 数据集

load profile pct 数据集

  • load profile pct 数据集产生的透视图

load profile pct 数据集产生的透视图

说明

  • 结果集为快照间load profile 指标间累积差的占比(百分比)
  • 脚本已经在oracle 11.2.0.1、11.2.0.4单实例和RAC环境下通过测试

脚本文件

点击下载

参考

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