"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...) |
|||
1번째 줄: | 1번째 줄: | ||
− | + | === 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 pjtdba/qhwkd!001@dussp014 < < 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 | + | |
− | select listagg(instance_number, ') within group(order by instance_number) as inst_num from | + | 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 | + | from dba_hist_snapshot |
− | where | + | 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> |
2023년 2월 10일 (금) 15:14 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
AWR 일일 수집 스크립트[편집]
- 일별로 성능문제가 발생된 SQL 목록
--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
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