AWR历史数据报表工具AHR
1,背景
AWR报告只能分析指定时段统计数据，无法获取统计数据随时间的变化。
为了便于以时序方式分析多个AWR快照中统计数据变化,特开发AWR历史数据分析工具AHR(Awr History Report)。
1.1功能
使用AWR快照数据实时生成系统报表（系统负载、运行效率，统计项类似AWR报告），以便监控数据库运行状态。
比如，可以如下方式指定AWR快照集，并对所指定快照集生成报表(结果见附件1)：
*系统最近按小时统计数据变化，以便实时监控系统运行情况
*系统最近若干天指定时点统计数据变化，以便监控高峰时段运行情况（缺省按小时采集数据）
1.2实现方式,权限要求
为便于使用,使用PL/SQL实现,使用sqlplus创建报表包，然后使用匿名包调用。
报表信息来自动态视图和AWR历史数据，需要SELECT ANY DICTIONARY系统权限。
1.3版本0.1功能
系统统计信息报表，参见附件2源码。
1.4后续版本功能
*指定sql/高负载sql相关报表
*segment相关报表
*file io相关报表
*SGA 相关报表
*PGA 相关报表
*RAC 相关报表
*等待事件相关报表
1.5功能扩展
*图形化展示(采用nmon采集数据并通过EXCEL展示类似方式)
*历史数据保存,阈值设置与告警
*结合AWR资料的导出／导入功能，可以订制分析更长时间系统变化报表
2,系统统计信息报表操作参考
2.1设置数据库实例
通过设置dbid,inst_num,可以生成其他实例报告,缺省时生成当前连接数据库／实例统计信息
awr_hist_rpt.set_dbid_inst(null,null);
2.2设置AWR快照集
2.2.1设置最近按小时采集的AWR快照集
awr_hist_rpt.init_snap_lastest;
2.2.2设置最近若干天指定时点AWR快照集
awr_hist_rpt.init_snap_same_hour;
2.2.3使用游标设置AWR快照集
awr_hist_rpt.init_snap_by_cursor(l_cursor);
2.3获取统计信息
2.3.1获取实例主要统计信息
awr_hist_rpt.get_sysstat;
2.3.2获取实例指定统计项
--获取实例指定统计项,如"user calls"
awr_hist_rpt.get_sysstat('user calls');
2.4,例子
2.4.1使用系统最近AWR快照集
set serveroutput on
begin
--设置dbid,inst_num
awr_hist_rpt.set_dbid_inst(null,null);
--分析最近按小时采集的快照
awr_hist_rpt.init_snap_lastest;
--获取实例主要统计信息
awr_hist_rpt.get_sysstat;
end;
/
2.4.1使用游标设置AWR快照集
set serveroutput on
DECLARE
l_cursor awr_hist_rpt.cursor_ref;
BEGIN
--初始化，通过设置dbid,inst_num,可以生成其他实例报告,缺省时生成当前连接数据库／实例统计信息
awr_hist_rpt.set_dbid_inst(NULL, NULL);
OPEN l_cursor FOR
SELECT *
FROM (SELECT s1.snap_id snap_id1,
s1.end_interval_time snap_time1,
s2.snap_id snap_id2,
s2.end_interval_time snap_time2
FROM dba_hist_snapshot s1, dba_hist_snapshot s2
WHERE s1.dbid = s2.dbid
AND s1.instance_number = s2.instance_number
AND s1.startup_time = s2.startup_time
AND s1.end_interval_time = s2.begin_interval_time
ORDER BY snap_id1 DESC)
WHERE rownum <= 6
ORDER BY snap_id1;
--分析最近按小时采集的快照
awr_hist_rpt.init_snap_by_cursor(l_cursor);
--获取实例主要统计信息
awr_hist_rpt.get_sysstat;
END;
/
附测试结果：
DB TIME
snap_id Db time/Ela diff sec
--------------------------------------------
673 .9 3313 3580
674 .9 3378 3633
675 .9 3312 3577
676 .9 3312 3582
677 .9 3375 3630
678 .9 3253 3569
LOAD PROFILE
redo size
snap_id diff/sec diff sec
--------------------------------------------
673 1231.7 4409632 3580
674 1028.4 3736448 3633
675 402.6 1440404 3577
676 460.2 1648712 3582
677 1436.3 5213804 3630
678 1075.3 3837900 3569
session logical reads
snap_id diff/sec diff sec
--------------------------------------------
673 67.4 241394 3580
674 23.1 84149 3633
675 7.3 26266 3577
676 7.9 28474 3582
677 38.7 140685 3630
678 15.2 54548 3569
db block changes
snap_id diff/sec diff sec
--------------------------------------------
673 6.7 24262 3580
674 5.0 18448 3633
675 1.6 5715 3577
676 1.8 6637 3582
677 7.6 27908 3630
678 5.6 20117 3569
physical reads
snap_id diff/sec diff sec
--------------------------------------------
673 .0 22 3580
674 .0 5 3633
675 .0 1 3577
676 .0 2 3582
677 .0 5 3630
678 .0 17 3569
physical writes
snap_id diff/sec diff sec
--------------------------------------------
673 .3 1154 3580
674 .3 1086 3633
675 .2 821 3577
676 .2 847 3582
677 .3 1248 3630
678 .2 1028 3569
user calls
snap_id diff/sec diff sec
--------------------------------------------
673 .8 3040 3580
674 1.3 4780 3633
675 .1 355 3577
676 .0 338 3582
677 2.3 8418 3630
678 .3 1343 3569
parse count (total)
snap_id diff/sec diff sec
--------------------------------------------
673 1.7 6073 3580
674 1.4 5117 3633
675 .6 2391 3577
676 .6 2350 3582
677 2.0 7341 3630
678 1.0 3602 3569
parse count (total)
snap_id diff/sec diff sec
--------------------------------------------
673 1.7 6073 3580
674 1.4 5117 3633
675 .6 2391 3577
676 .6 2350 3582
677 2.0 7341 3630
678 1.0 3602 3569
sorts (memory)
snap_id diff/sec diff sec
--------------------------------------------
673 .5 2094 3580
674 .5 1965 3633
675 .5 1778 3577
676 .5 1789 3582
677 .5 1984 3630
678 .5 1836 3569
logons cumulative
snap_id diff/sec diff sec
--------------------------------------------
673 .0 116 3580
674 .0 113 3633
675 .0 114 3577
676 .0 113 3582
677 .0 113 3630
678 .0 113 3569
execute count
snap_id diff/sec diff sec
--------------------------------------------
673 3.4 12246 3580
674 3.0 11040 3633
675 1.6 5834 3577
676 1.6 5753 3582
677 4.8 17556 3630
678 2.1 7739 3569
附件2
CREATE OR REPLACE PACKAGE awr_hist_rpt IS
TYPE snap_pair_type IS RECORD(
snap_id1 INT,
snap_time1 TIMESTAMP,
snap_id2 INT,
snap_time2 TIMESTAMP);
TYPE snap_pair_tab IS TABLE OF snap_pair_type INDEX BY PLS_INTEGER;
g_snap_pair_tab snap_pair_tab;
TYPE cursor_ref IS REF CURSOR;
g_dbid INT := 0;
g_inst_num INT := 0;
g_max_snap_id INT := 0;
g_inst_startup TIMESTAMP;
g_snap_range_delt INT := 1 / 24 / 20;
FUNCTION snap_pair(snap_id1 IN INT,
snap_time1 IN DATE,
snap_id2 IN INT,
snap_time2 IN DATE) RETURN snap_pair_type;
PROCEDURE log(p_txt IN VARCHAR2);
PROCEDURE set_dbid_inst(p_dbid IN INT DEFAULT NULL,
p_inst_num IN INT DEFAULT NULL);
PROCEDURE init_snap_lastest(p_snap_top_n IN INT DEFAULT 24);
PROCEDURE init_snap_by_cursor(p_cursor IN cursor_ref);
PROCEDURE init_snap_same_hour(p_hh24 IN VARCHAR2 DEFAULT '15',
p_snap_top_n IN INT DEFAULT 24);
PROCEDURE get_env;
PROCEDURE get_sysstat(p_statname IN VARCHAR2,
p_title IN VARCHAR2 DEFAULT NULL);
PROCEDURE get_sysstat;
END awr_hist_rpt;
/
CREATE OR REPLACE PACKAGE BODY awr_hist_rpt AS
FUNCTION snap_pair(snap_id1 IN INT,
snap_time1 IN DATE,
snap_id2 IN INT,
snap_time2 IN DATE) RETURN snap_pair_type IS
l_snap_pair snap_pair_type;
BEGIN
l_snap_pair.snap_id1 := snap_id1;
l_snap_pair.snap_time1 := snap_time1;
l_snap_pair.snap_id2 := snap_id2;
l_snap_pair.snap_time2 := snap_time2;
RETURN l_snap_pair;
END;
PROCEDURE log(p_txt IN VARCHAR2) IS
BEGIN
dbms_output.put_line(p_txt);
END log;
PROCEDURE log_blank_line IS
BEGIN
dbms_output.put_line(' ');
END log_blank_line;
PROCEDURE log_sep_line IS
BEGIN
dbms_output.put_line('--------------------------------------------');
END log_sep_line;
PROCEDURE log_title(p_txt IN VARCHAR2) IS
BEGIN
dbms_output.put_line(lpad(' ', 10, ' ') || p_txt);
END log_title;
PROCEDURE set_dbid_inst(p_dbid IN INT DEFAULT NULL,
p_inst_num IN INT DEFAULT NULL) IS
BEGIN
dbms_output.enable(NULL);
--dbid
IF p_dbid IS NULL THEN
SELECT dbid INTO g_dbid FROM v$database;
ELSE
g_dbid := p_dbid;
END IF;
--instance_number
IF p_inst_num IS NULL THEN
SELECT instance_number INTO g_inst_num FROM v$instance;
ELSE
g_inst_num := p_inst_num;
END IF;
--g_max_snap_id
SELECT MAX(snap_id)
INTO g_max_snap_id
FROM dba_hist_snapshot
WHERE dbid = g_dbid
AND instance_number = g_inst_num;
--startup_time
SELECT startup_time
INTO g_inst_startup
FROM dba_hist_snapshot
WHERE dbid = g_dbid
AND instance_number = g_inst_num
AND snap_id = g_max_snap_id;
END set_dbid_inst;
PROCEDURE init_snap_lastest(p_snap_top_n IN INT DEFAULT 24) IS
l_snap_cnt INT := 0;
BEGIN
FOR s2 IN (SELECT snap_id,
begin_interval_time,
end_interval_time,
dbid,
instance_number,
startup_time
FROM dba_hist_snapshot
WHERE dbid = g_dbid
AND instance_number = g_inst_num
AND startup_time = g_inst_startup
AND rownum <= p_snap_top_n
ORDER BY snap_id) LOOP
FOR s1 IN (SELECT snap_id
FROM dba_hist_snapshot
WHERE dbid = s2.dbid
AND instance_number = s2.instance_number
AND startup_time = s2.startup_time
AND end_interval_time = s2.begin_interval_time) LOOP
g_snap_pair_tab(l_snap_cnt) := snap_pair(s1.snap_id,
s2.begin_interval_time,
s2.snap_id,
s2.end_interval_time);
l_snap_cnt := l_snap_cnt + 1;
END LOOP;
END LOOP;
--log('====>' || l_snap_cnt);
END init_snap_lastest;
PROCEDURE init_snap_by_cursor(p_cursor IN cursor_ref) IS
l_snap_cnt INT := 0;
l_snap_pair snap_pair_type;
BEGIN
LOOP
FETCH p_cursor
INTO l_snap_pair;
EXIT WHEN p_cursor%NOTFOUND;
g_snap_pair_tab(l_snap_cnt) := l_snap_pair;
l_snap_cnt := l_snap_cnt + 1;
END LOOP;
CLOSE p_cursor;
--log('====>' || l_snap_cnt);
EXCEPTION
WHEN OTHERS THEN
CLOSE p_cursor;
END init_snap_by_cursor;
PROCEDURE init_snap_same_hour(p_hh24 IN VARCHAR2 DEFAULT '15',
p_snap_top_n IN INT DEFAULT 24) IS
l_snap_cnt INT := 0;
BEGIN
FOR s2 IN (SELECT snap_id,
begin_interval_time,
end_interval_time,
dbid,
instance_number,
startup_time
FROM dba_hist_snapshot
WHERE dbid = g_dbid
AND instance_number = g_inst_num
AND startup_time = g_inst_startup
AND to_char(end_interval_time, 'HH24') = p_hh24
AND end_interval_time BETWEEN
begin_interval_time + 1 / 24 - g_snap_range_delt AND
begin_interval_time + 1 / 24 + g_snap_range_delt
AND rownum <= p_snap_top_n
ORDER BY snap_id) LOOP
FOR s1 IN (SELECT snap_id
FROM dba_hist_snapshot
WHERE dbid = s2.dbid
AND instance_number = s2.instance_number
AND startup_time = s2.startup_time
AND end_interval_time = s2.begin_interval_time) LOOP
g_snap_pair_tab(l_snap_cnt) := snap_pair(s1.snap_id,
s2.begin_interval_time,
s2.snap_id,
s2.end_interval_time);
l_snap_cnt := l_snap_cnt + 1;
END LOOP;
END LOOP;
--log('====>' || l_snap_cnt);
END init_snap_same_hour;
PROCEDURE get_env IS
BEGIN
log('-----------------------');
log('g_dbid:' || g_dbid);
log('g_inst_num:' || g_inst_num);
log('g_max_snap_id:' || g_max_snap_id);
log('g_inst_startup:' || to_char(g_inst_startup, 'YYYYMMDD:HH24MISS'));
log('-----------------------');
IF g_snap_pair_tab.COUNT > 0 THEN
FOR i IN g_snap_pair_tab.FIRST .. g_snap_pair_tab.LAST LOOP
log('snap_id:' || g_snap_pair_tab(i)
.snap_id1 || '->' || g_snap_pair_tab(i)
.snap_id2 || ' timestamp:' ||
to_char(g_snap_pair_tab(i).snap_time1, 'YYYYMMDD:HH24MISS') || '->' ||
to_char(g_snap_pair_tab(i).snap_time2, 'YYYYMMDD:HH24MISS'));
END LOOP;
ELSE
log('--not found useable snap_id');
END IF;
END get_env;
PROCEDURE get_sysstat(p_statname IN VARCHAR2,
p_title IN VARCHAR2 DEFAULT NULL) IS
v1 NUMBER;
v2 NUMBER;
t1 DATE;
t2 DATE;
delt_t NUMBER(22, 2);
l_statname VARCHAR2(30) := p_statname;
l_title VARCHAR2(30) := nvl(p_title, p_statname);
BEGIN
log_blank_line;
log_title(l_title);
log(lpad('snap_id', 10) || lpad('diff/sec', 13) || lpad('diff', 12) ||
lpad('sec', 8));
log_sep_line;
IF g_snap_pair_tab.COUNT > 0 THEN
FOR i IN g_snap_pair_tab.FIRST .. g_snap_pair_tab.LAST LOOP
t1 := g_snap_pair_tab(i).snap_time1;
t2 := g_snap_pair_tab(i).snap_time2;
delt_t := (t2 - t1) * 86400.0;
BEGIN
SELECT VALUE
INTO v1
FROM dba_hist_sysstat
WHERE dbid = g_dbid
AND instance_number = g_inst_num
AND stat_name = l_statname
AND snap_id = g_snap_pair_tab(i).snap_id1;
SELECT VALUE
INTO v2
FROM dba_hist_sysstat
WHERE dbid = g_dbid
AND instance_number = g_inst_num
AND stat_name = l_statname
AND snap_id = g_snap_pair_tab(i).snap_id2;
log(lpad(g_snap_pair_tab(i).snap_id2, 10) ||
lpad(to_char((v2 - v1) / delt_t, '9999999999d99'), 13) ||
lpad((v2 - v1), 12) || lpad(round(delt_t), 8));
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
ELSE
log('--not found useable snap_id');
END IF;
END get_sysstat;
PROCEDURE get_dbtime IS
v1 NUMBER;
v2 NUMBER;
t1 DATE;
t2 DATE;
delt_t NUMBER(22, 2);
l_statname VARCHAR2(30);
BEGIN
log_blank_line;
log_title('DB TIME');
log(lpad('snap_id', 10) || lpad('Db time/Ela', 13) || lpad('diff', 12) ||
lpad('sec', 8));
log_sep_line;
l_statname := 'DB time';
IF g_snap_pair_tab.COUNT > 0 THEN
FOR i IN g_snap_pair_tab.FIRST .. g_snap_pair_tab.LAST LOOP
t1 := g_snap_pair_tab(i).snap_time1;
t2 := g_snap_pair_tab(i).snap_time2;
delt_t := (t2 - t1) * 86400.0;
BEGIN
SELECT VALUE
INTO v1
FROM dba_hist_sysstat
WHERE dbid = g_dbid
AND instance_number = g_inst_num
AND stat_name = l_statname
AND snap_id = g_snap_pair_tab(i).snap_id1;
SELECT VALUE
INTO v2
FROM dba_hist_sysstat
WHERE dbid = g_dbid
AND instance_number = g_inst_num
AND stat_name = l_statname
AND snap_id = g_snap_pair_tab(i).snap_id2;
log(lpad(g_snap_pair_tab(i).snap_id2, 10) ||
lpad(to_char((v2 - v1) / 100 / delt_t, '9999999999d99'), 13) ||
lpad(round((v2 - v1) / 100), 12) || lpad(round(delt_t), 8));
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
ELSE
log('--not found useable snap_id');
END IF;
END get_dbtime;
PROCEDURE get_sysstat IS
BEGIN
get_dbtime;
log_blank_line;
log_title('LOAD PROFILE');
--Redo size
get_sysstat('redo size');
--Logical reads:
--session logical reads
--db block gets
--consistent gets
get_sysstat('session logical reads');
--Block changes:
--db block changes
--consistent changes
get_sysstat('db block changes');
--Physical reads:
--physical reads
get_sysstat('physical reads');
--Physical writes:
--physical writes
get_sysstat('physical writes');
--User calls:
--user calls
get_sysstat('user calls');
--Parses:
--parse count (total)
get_sysstat('parse count (total)');
--Hard parses:
--parse count (hard)
get_sysstat('parse count (total)');
--Sorts:
--sorts (memory)
--sorts (disk)
get_sysstat('sorts (memory)');
--Logons:
--logons cumulative
--logons current
get_sysstat('logons cumulative');
--Executes:
--execute count
get_sysstat('execute count');
--Transactions:
--user commits
--user rollbacks
--% Blocks changed per Read:
--db block changes/physical reads
--Recursive Call %:
--recursive calls/user calls
--Rollback per transaction %:
--(user rollbacks)/(user commits+user rollbacks)
--Rows per Sort: 98.78
--sorts (rows)/(sorts (memory)+sorts (disk))
--Instance Efficiency Percentages (Target 100%)
--Buffer Nowait %:
--Redo NoWait %:
--Buffer Hit %:
--?
--In-memory Sort %:
--sorts (memory)/(sorts (disk)+sorts (memory))
--Library Hit %:
--?
--Soft Parse %:
--1-parse count (hard)/parse count (total)
--Execute to Parse %:
--1-parse count (total)/execute count
--Latch Hit %:
--?
--Parse CPU to Parse Elapsd %:
--parse time cpu/parse time elapsed
--% Non-Parse CPU:
--parse time cpu\??
END get_sysstat;
END awr_hist_rpt;
/
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-700596/，如需转载，请注明出处，否则将追究法律责任。
