awr自動收集指令碼

一.
準備工作

 

      
一般我們都是條用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 NO_OPTIONS   = 0;

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 -

--   
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 echo
off;

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 指定的，可以生成其他名稱。