행위

"AWR 일별 수집"의 두 판 사이의 차이

DB CAFE

(새 문서: 파일(F 편집(E) 서식(0) 보기~ 도움말 --awr 수집 sh sqlplus -s pjtdba/qhwkd!001@dussp014 < < EOF > /oracle/work/AWR/log/awr_$1.log set heading off set timing on set linesiz...)
 
 
(같은 사용자의 중간 판 3개는 보이지 않습니다)
1번째 줄: 1번째 줄:
−
파일(F 편집(E) 서식(0) 보기~ 도움말
+
=== AWR 일일 수집 스크립트 ===
 +
==== 일별 성능문제 SQL 목록 ====
 +
<source lang=sql>
 
--awr 수집 sh
 
--awr 수집 sh
−
sqlplus -s pjtdba/qhwkd!001@dussp014 < < EOF > /oracle/work/AWR/log/awr_$1.log set heading off set timing on set linesize 150
+
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 pagesize 0
 
--set feedback off
 
--set feedback off
 
--set termout off
 
--set termout off
−
col db_id new_value db_id; col inst_num new_value inst_num;
+
col db_id new_value db_id;  
−
col bid new_value bid; col eid new_value eid;
+
col inst_num new_value inst_num;
−
col crtrYmd new_value crtrYmd; col stime new_value stime; col etime new_value etime;
+
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 '$1' as crtrYmd from dual;
−
select dbid as db _id from utt$database;
+
 
−
select listagg(instance_number, ') within group(order by instance_number) as inst_num from gut$instance;
+
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
 
select min(snap_id) as bid
−
, max(snap_id) as eid
+
    , max(snap_id) as eid
−
from ba_hist_snapshot
+
  from dba_hist_snapshot
−
where BEGIN_ INTERVAL_TIME between to_date('&crtrYmd 000000' YYYYMMDD HH24MISS') and to_date('&crtrYmd 235959*, YIYMMDD H24MISS")
+
where BEGIN_INTERVAL_TIME
 +
between to_date('&crtrYmd 000000','YYYYMMDD HH24MISS') and to_date('&crtrYmd 235959','YYYYMMDD HH24MISS')
 
--and instance_number = 4
 
--and instance_number = 4
 
order by snap_id
 
order by snap_id
 +
;
 +
 
insert into TB_AWR_HIST
 
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
 +
</source>
 +
 +
[[category:oracle]]

2023년 4월 2일 (일) 13:55 기준 최신판

thumb_up 추천메뉴 바로가기

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"