AWR 일별 수집

DB CAFE

추천메뉴 바로가기

DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
튜닝 { 오라클 튜닝 목록 }
모델링 { 데이터 모델링 가이드 }

1 AWR 일일 수집 스크립트[편집]

1.1 일별 성능문제 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

원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=AWR_일별_수집&oldid=7877"

Oracle