AWR 일일 수집 스크립트
일별 성능문제 SQL 목록
--awr 수집 sh
sqlplus -s dbadmin/pwd001@db01 < < EOF > /oracle/work/AWR/log/awr_$1.log
set heading off
set timing on
set linesize 150
set pagesize 0
--set feedback off
--set termout off
col db_id new_value db_id;
col inst_num new_value inst_num;
col bid new_value bid;
col eid new_value eid;
col crtrYmd new_value crtrYmd;
col stime new_value stime;
col etime new_value etime;
select '$1' as crtrYmd from dual;
select dbid as db _id from v\$database;
select listagg(instance_number, ',') within group(order by instance_number) as inst_num from gv\$instance;
select min(snap_id) as bid
, max(snap_id) as eid
from dba_hist_snapshot
where BEGIN_INTERVAL_TIME
between to_date('&crtrYmd 000000','YYYYMMDD HH24MISS') and to_date('&crtrYmd 235959','YYYYMMDD HH24MISS')
--and instance_number = 4
order by snap_id
;
insert into TB_AWR_HIST
( crtr_ymd
, from_snap_id
, to_snap_id
, line
, html_rpt_cn
)
select b.crtr_ymd
, b.bid
, b.eid
, a.line
, a.output
from ( select output,rownum line
from table(dbms_workload_repository.awr_global_report_html
l_dbid = > &db_id
, l_inst_num => '&inst_num'
, l_bid => &bid
, l_eid => &eid
) a
, (select &crtrYmd crtrymd
&bid bid
&eid eid
from dual
) b
where 1 =1