一.
准备工作
一般我们都是条用awrrpt.sql 来创建我们的AWR报告。
我们先看下这个脚本的具体内容:
[oracle@rac1 admin]$ cat awrrpt.sql | grep -v 'Rem'|grep -v '^--'
set echo off heading on underline
on;
column inst_num heading "Inst Num" new_value inst_num format 99999;
column inst_name heading
"Instance" new_value inst_name format
a12;
column db_name heading "DB Name" new_value db_name format a12;
column dbid heading "DB Id" new_value dbid format 9999999999 just
c;
prompt
prompt Current
Instance
prompt
~~~~~~~~~~~~~~~~
select d.dbid dbid
, d.name db_name
, i.instance_number
inst_num
, i.instance_name inst_name
from v$database d,
v$instance i;
@@awrrpti
undefine num_days;
undefine report_type;
undefine report_name;
undefine begin_snap;
undefine end_snap;
在以上的脚本里,我们发现它只是生成了一些变量,然后把这些变量传给了另一个脚本:awrrpti.sql。 我们看下awrrpti.sql 脚本的具体内容:
[oracle@rac1 admin]$ cat awrrpti.sql | grep -v 'Rem'|grep -v '^--'
set echo off;
set veri off;
set feedback off;
variable rpt_options
number;
define ENABLE_ADDM = 8;
begin
:rpt_options :=
&NO_OPTIONS;
end;
/
prompt
prompt Specify the Report
Type
prompt
~~~~~~~~~~~~~~~~~~~~~~~
prompt Would you like an HTML report,
or a plain text report?
prompt Enter 'html' for an HTML report,
or 'text' for plain text
prompt
Defaults to 'html'
column report_type new_value
report_type;
set heading off;
select 'Type Specified:
',lower(nvl('&&report_type','html')) report_type from
dual;
set heading on;
set termout off;
column ext new_value
ext;
select '.html' ext from dual where
lower('&&report_type') <> 'text';
select '.txt' ext from dual where
lower('&&report_type') = 'text';
set termout on;
@@awrinput.sql
-- 这个脚本主要是确定SNAP的。
@@awrinpnm.sql 'awrrpt_'
&&ext
-- 这个脚本主要是确定AWR 文件名称的
set termout off;
column fn_name new_value fn_name
noprint;
select 'awr_report_text' fn_name from
dual where lower('&report_type') = 'text';
select 'awr_report_html' fn_name from
dual where lower('&report_type') <> 'text';
column lnsz new_value lnsz
noprint;
select '80' lnsz from dual where
lower('&report_type') = 'text';
select '1500' lnsz from dual where
lower('&report_type') <> 'text';
set linesize
&lnsz;
set termout on;
spool
&report_name;
select output from
table(dbms_workload_repository.&fn_name(
:dbid,
:inst_num,
:bid,
:eid,
:rpt_options
));
spool off;
prompt Report written to
&report_name.
set termout off;
clear columns sql;
ttitle off;
btitle off;
repfooter off;
set linesize 78 termout on feedback 6
heading on;
undefine report_name
undefine report_type
undefine ext
undefine fn_name
undefine lnsz
undefine NO_OPTIONS
undefine ENABLE_ADDM
undefine top_n_events
undefine num_days
undefine top_n_sql
undefine top_pct_sql
undefine
sh_mem_threshold
undefine
top_n_segstat
whenever sqlerror
continue;
[oracle@rac1 admin]$
这个脚本才是我们真正生成AWR的脚本。
在这个脚本里面,提示我们选择AWR报告的类型。
通过上面的2个脚本,我们将AWR报告简化一下:
select output from
table(dbms_workload_repository.&fn_name(:dbid,
:inst_num,:bid, :eid,:rpt_options ));
这条语句就是整个AWR报告的核心:
(1)&fn_name :决定AWR报告的类型,有2个值:awr_report_html和awr_report_text。
(2)dbid,inst_num,bid,eid 可以通过dba_hist_snapshot查询. bid 指的是begin snap_id, eid 指的是end snap_id.
SQL> select * from (select
snap_id,dbid,instance_number from dba_hist_snapshot order by snap_id) where
rownum<10;
SNAP_ID DBID
INSTANCE_NUMBER
---------- ----------
---------------
184
809910293
2
184
809910293
1
185
809910293
2
185
809910293
1
186
809910293
2
186
809910293
1
187
809910293
2
187
809910293
1
188
809910293
2
9 rows selected.
我这里是个RAC 环境,
通过这个可以看出在每个节点上都保存着AWR的信息。
(3)rpt_options:该参数控制是否显示ADDM的。
--
No options. Setting this will not show the ADDM
--
specific portions of the report.
-- This is the
default setting.
--
-- ENABLE_ADDM
-
--
Show the ADDM specific portions of the report.
--
These sections include the Buffer Pool Advice,
--
Shared Pool Advice, PGA Target Advice, and
--
Wait Class sections.
define NO_OPTIONS = 0;
define ENABLE_ADDM = 8;
有了上面的数据之后,我们就可以使用如下SQL直接生成AWR报告了。
SQL>select output from
table(dbms_workload_repository.awr_report_html(809910293,
2,220,230,0));
SQL>select output from
table(dbms_workload_repository.awr_report_text(809910293, 2,220,230,0));
二.
生成AWR报告 SQL脚本
以上写了这么多,就是为了一个脚本:myawrrpt.sql. 这个脚本就是自动的去收集信息。
因为如果我们是调用awrrpt.sql的话,需要输入一些参数。
我们修改一下脚本,让它根据我们的需求来收集信息,这样就不用输入参数了。
[oracle@rac1 admin]$ cat myawrrpt.sql
conn / as sysdba;
set veri
off;
set feedback
off;
set termout
on;
set heading
off;
variable rpt_options
number;
define NO_OPTIONS =
0;
define ENABLE_ADDM =
8;
-- according to your needs, the value
can be 'text' or 'html'
define
report_type='html';
begin
:rpt_options :=
&NO_OPTIONS;
end;
/
variable dbid
number;
variable inst_num
number;
variable bid
number;
variable eid
number;
begin
select max(snap_id)-48
into :bid from dba_hist_snapshot;
select max(snap_id) into :eid from
dba_hist_snapshot;
select dbid into :dbid from
v$database;
select instance_number into :inst_num
from v$instance;
end;
/
column ext new_value ext
noprint
column fn_name new_value fn_name
noprint;
column lnsz new_value lnsz
noprint;
--select 'txt' ext from dual where
lower('&report_type') = 'text';
select 'html' ext from dual where
lower('&report_type') = 'html';
--select 'awr_report_text' fn_name from
dual where lower('&report_type') = 'text';
select 'awr_report_html' fn_name from
dual where lower('&report_type') = 'html';
--select '80' lnsz from dual where
lower('&report_type') = 'text';
select '1500' lnsz from dual where
lower('&report_type') = 'html';
set linesize
&lnsz;
-- print the AWR results into the
report_name file using the spool command:
column report_name new_value
report_name noprint;
select 'awr'||'.'||'&ext'
report_name from dual;
set termout
off;
spool
&report_name;
select output from
table(dbms_workload_repository.&fn_name(:dbid, :inst_num,:bid,
:eid,:rpt_options ));
spool
off;
set termout
on;
clear columns
sql;
ttitle
off;
btitle
off;
repfooter
off;
undefine
report_name
undefine
report_type
undefine
fn_name
undefine
lnsz
undefine
NO_OPTIONS
exit
[oracle@rac1 admin]$
这个脚本是收集过去48个小时的snap 来生成AWR。
生成的文件名称是awr .html,这个也是spool 指定的,可以生成其他名称。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29065182/viewspace-1126530/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29065182/viewspace-1126530/