AWR是oracle 10G以后提供的一个新的工具,Orcacle 建议用户用这个取代Statspack,它采集与性能相关的统计数据,并从那些统计数据中导出性能量度,以跟踪潜在的问题,并自动生成ADDM(自动数据诊断监控),为用户提供数据库性能诊断分析建议。
AWR报告是一个比较实用的工具,对于oracle性能的诊断分析大有好处,生成awr报告的方法有很多,但是操作起来有的比较麻烦,为此,本人特意写了一个生成awr报告的简单方法,与大家共同交流,该方法比较简单。由于能力有限所以可能有做的不到位的地方,请大家多包涵。具体步骤如下:
1、创建保存awr报告的表:
--创建表、记录awr信息
create table awr_report
(
report_name varchar2(60), --报告名称
report_type varchar2(4), --报告类型
user_name varchar2(30), --用户名
created_date date, --创建时间
report_content clob --报告内容
);
2、创建awr报告(直接运行下面的块就可以):
declare
l_report_type varchar2(30) := NULL;
-- Report level (not used yet)
l_rpt_options number := 0;
l_flag number := 0;
l_min_snap_id number := 0;
l_max_snap_id number := 0;
l_dbid number := 0;
l_inst_num number := 0;
l_clob clob := NULL;
l_report_name varchar2(60) := NULL;
TYPE ref_cursor IS REF CURSOR;
l_ref_cursor ref_cursor;
TYPE output_tab IS TABLE OF varchar2(4000);
l_output output_tab;
begin
l_report_type := lower('HTML');
if l_report_type not in ('text', 'html') then
dbms_output.put_line('Please input report type: <html/text>');
return;
end if;
select dbid into l_dbid from v$database;
l_inst_num := 1;
-- select instance_number into l_inst_num from V$instance;
l_min_snap_id := 47835; --快照的id(上个快照)
l_max_snap_id := 47836; --快照的id(下个快照)
if (l_min_snap_id >= l_max_snap_id) then
dbms_output.put_line('Begin Snapid or End Snapid is not correct! ');
return;
end if;
l_flag := 1;
select snap_id into l_flag
from dba_hist_snapshot
where dbid = l_dbid
and instance_number = l_inst_num
and snap_id = l_min_snap_id ;
if (l_flag = 0 ) then
dbms_output.put_line('Bein Snapid is not Exist! ');
return;
end if;
dbms_output.put_line('l_flag== '||to_char(l_flag));
l_flag := 1;
select snap_id into l_flag
from dba_hist_snapshot
where dbid = l_dbid
and instance_number = l_inst_num
and snap_id = l_max_snap_id ;
if (l_flag = 0 ) then
dbms_output.put_line('End Snapid is not Exist! ');
return;
end if;
dbms_output.put_line('l_flag== '||to_char(l_flag));
dbms_output.put_line('l_min_snap_id== '||to_char(l_min_snap_id));
dbms_output.put_line('l_max_snap_id== '||to_char(l_max_snap_id));
select 'AWR_'||l_dbid||'_'||l_inst_num||'_'||to_char(l_min_snap_id)||'_'||l_max_snap_id||'.'||l_report_type into l_report_name from dual;
if l_report_type = 'html' then
open l_ref_cursor for
select output
from table(dbms_workload_repository.awr_report_html(l_dbid,
l_inst_num,
l_min_snap_id,
l_max_snap_id,
l_rpt_options));
fetch l_ref_cursor bulk collect
into l_output;
close l_ref_cursor;
for i in l_output.FIRST .. l_output.LAST loop
if i = l_output.LAST then
l_clob := l_clob || l_output(i);
else
l_clob := l_clob || l_output(i) || chr(10);
end if;
end loop;
insert into awr_report
(report_name, report_type, user_name, created_date, report_content)
values
(l_report_name, upper(l_report_type), user, sysdate, l_clob);
else
open l_ref_cursor for
select output
from table(dbms_workload_repository.awr_report_text(l_dbid,
l_inst_num,
l_min_snap_id,
l_max_snap_id,
l_rpt_options));
fetch l_ref_cursor bulk collect
into l_output;
close l_ref_cursor;
for i in l_output.FIRST .. l_output.LAST loop
if i = l_output.LAST then
l_clob := l_clob || l_output(i);
else
l_clob := l_clob || l_output(i) || chr(10);
end if;
end loop;
insert into awr_report
(report_name, report_type, user_name, created_date, report_content)
values
(l_report_name, upper(l_report_type), user, sysdate, l_clob);
end if;
commit;
end;
l_report_type varchar2(30) := NULL;
-- Report level (not used yet)
l_rpt_options number := 0;
l_flag number := 0;
l_min_snap_id number := 0;
l_max_snap_id number := 0;
l_dbid number := 0;
l_inst_num number := 0;
l_clob clob := NULL;
l_report_name varchar2(60) := NULL;
TYPE ref_cursor IS REF CURSOR;
l_ref_cursor ref_cursor;
TYPE output_tab IS TABLE OF varchar2(4000);
l_output output_tab;
begin
l_report_type := lower('HTML');
if l_report_type not in ('text', 'html') then
dbms_output.put_line('Please input report type: <html/text>');
return;
end if;
select dbid into l_dbid from v$database;
l_inst_num := 1;
-- select instance_number into l_inst_num from V$instance;
l_min_snap_id := 47835; --快照的id(上个快照)
l_max_snap_id := 47836; --快照的id(下个快照)
if (l_min_snap_id >= l_max_snap_id) then
dbms_output.put_line('Begin Snapid or End Snapid is not correct! ');
return;
end if;
l_flag := 1;
select snap_id into l_flag
from dba_hist_snapshot
where dbid = l_dbid
and instance_number = l_inst_num
and snap_id = l_min_snap_id ;
if (l_flag = 0 ) then
dbms_output.put_line('Bein Snapid is not Exist! ');
return;
end if;
dbms_output.put_line('l_flag== '||to_char(l_flag));
l_flag := 1;
select snap_id into l_flag
from dba_hist_snapshot
where dbid = l_dbid
and instance_number = l_inst_num
and snap_id = l_max_snap_id ;
if (l_flag = 0 ) then
dbms_output.put_line('End Snapid is not Exist! ');
return;
end if;
dbms_output.put_line('l_flag== '||to_char(l_flag));
dbms_output.put_line('l_min_snap_id== '||to_char(l_min_snap_id));
dbms_output.put_line('l_max_snap_id== '||to_char(l_max_snap_id));
select 'AWR_'||l_dbid||'_'||l_inst_num||'_'||to_char(l_min_snap_id)||'_'||l_max_snap_id||'.'||l_report_type into l_report_name from dual;
if l_report_type = 'html' then
open l_ref_cursor for
select output
from table(dbms_workload_repository.awr_report_html(l_dbid,
l_inst_num,
l_min_snap_id,
l_max_snap_id,
l_rpt_options));
fetch l_ref_cursor bulk collect
into l_output;
close l_ref_cursor;
for i in l_output.FIRST .. l_output.LAST loop
if i = l_output.LAST then
l_clob := l_clob || l_output(i);
else
l_clob := l_clob || l_output(i) || chr(10);
end if;
end loop;
insert into awr_report
(report_name, report_type, user_name, created_date, report_content)
values
(l_report_name, upper(l_report_type), user, sysdate, l_clob);
else
open l_ref_cursor for
select output
from table(dbms_workload_repository.awr_report_text(l_dbid,
l_inst_num,
l_min_snap_id,
l_max_snap_id,
l_rpt_options));
fetch l_ref_cursor bulk collect
into l_output;
close l_ref_cursor;
for i in l_output.FIRST .. l_output.LAST loop
if i = l_output.LAST then
l_clob := l_clob || l_output(i);
else
l_clob := l_clob || l_output(i) || chr(10);
end if;
end loop;
insert into awr_report
(report_name, report_type, user_name, created_date, report_content)
values
(l_report_name, upper(l_report_type), user, sysdate, l_clob);
end if;
commit;
end;
3、--查询报告结果
注意:要生成awr报告必须先有对应时间的数据库快照,建快照的方法如下:
select * from awr_report;
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(FLUSH_LEVEL=>'ALL');
--查询快照信息(通过时间判断哪个snap_id是我们自己创建的数据库快照id,因为上面要用到我们的snap_id)
SELECT SNAP_ID,
TO_CHAR(STARTUP_TIME, 'yyyy-mm-dd hh24:mi:ss'),
TO_CHAR(END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss')
FROM DBA_HIST_SNAPSHOT
ORDER BY 2 DESC ;
以上就是整个创建awr报告的基本过程,时间仓促写的有点乱。哪里不清楚请留言~