| 구분 | Statspack | AWR |
|---|---|---|
| 조회방식 | SQL을 이용한 딕셔너리 조회 | DMA(Direct Memory Access)방식으로 SGA를 직접 액세스하기 때문에 좀 더 빠르게 정보수집가능 |
| 자료수집 | 정보수집의 부하 때문에 자주 스냅샷 수행 어려움. 그래서 사용자가 수동으로 statspack.snap 명령을 날리거나 정해진 기간동안만 JOB에 등록해 수집 | 자동으로 성능자료 수집해 일정기간 보관 |
| 사용뷰 | PERFSTAT계정 밑에 'stats$'로 시작하는 뷰를 통해 수집된 성능 정보들을 조회 | SYS계정 밑에 'dba_hist_'로 시작하는 뷰를 이용 |
| 보고서출력 스크립트 | SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql | SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql |
-- 정해진 기간 동안 각 구간별로 SQL 수행횟수를 뽑아보는 쿼리
select to_char(min(s.begin_interval_time), 'hh24:mi') begin
, to_char(min(s.end_interval_time), 'hh24:mi') end
, sum(b.value-a.value) "execute count"
from dba_hist_sysstat a, dba_hist_sysstat b, dba_hist_snapshot s
where s.instance_number = &instance_number
and s.snap_id between &begin_snap and &end_snap
and a.stat_name = 'execute count'
and b.stat_id = a.stat_id
and b.snap_id = s.snap_id
and a.snap_id = b.snap_id - 1
and a.instance_number = s.instance_number
and b.instance_number = s.instance_number
group by s.snap_id
order by s.snap_id ;
-- 스냅샷 시간확인
select *
from DBA_HIST_WR_CONTROL;
-- snapshot 확인
select *
from DBA_HIST_SNAPSHOT
order by begin_interval_time desc;
-- AWR 사용법 : 스냅샷 기간,시간등 변경
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
( retention => 1440 * 30, -- 스냅샷 저장기간[30일] (1 day:1440 minutes)
interval => 60, -- 스냅샷 간격(단위:분)
dbid => 0000012212
);
END;
-- AWR REPORT 생성
SELECT output
FROM TABLE
(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT
(0000012212 /* dbid */
, 1 /* inst_num */
, 15987 /* start snap_id */
, 15990 /* end sanp_id */
)
) ;
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
RMSDB 765921261 RMSDB 1 10.2.0.3.0 NO rmdb01
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 15987 08-10월-09 10:00:07 31 11.0
End Snap: 15990 08-10월-09 13:00:33 39 8.6
Elapsed: 180.43 (mins)
DB Time: 49.65 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 6,832M 6,832M Std Block Size: 32K
Shared Pool Size: 288M 288M Log Buffer: 14,372K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 34,239.04 4,159.31
Logical reads: 9,609.31 1,167.33
Block changes: 161.17 19.58
Physical reads: 26.89 3.27
Physical writes: 1.26 0.15
User calls: 126.96 15.42
Parses: 62.83 7.63
Hard parses: 6.97 0.85
Sorts: 5.95 0.72
Logons: 0.09 0.01
Executes: 82.14 9.98
Transactions: 8.23
% Blocks changed per Read: 1.68 Recursive Call %: 83.56
Rollback per transaction %: 0.11 Rows per Sort: 32.11
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.72 In-memory Sort %: 100.00
Library Hit %: 85.99 Soft Parse %: 88.91
Execute to Parse %: 23.51 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 94.27 % Non-Parse CPU: 90.32
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 65.41 65.18
% SQL with executions>1: 85.06 82.98
% Memory for SQL w/exec>1: 63.28 79.62
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
db file sequential read 211,992 1,117 5 37.5 User I/O
CPU time 734 24.7
db file scattered read 3,475 60 17 2.0 User I/O
log file parallel write 92,603 28 0 0.9 System I/O
log file sync 87,896 26 0 0.9 Commit
-------------------------------------------------------------
| Per Second | 각 측정 지표 값들을 측정 시간(Snapshot Interval, 초)으로 나눈 것이다. 따라서 초당 부하(Load)발생량을 의미한다. |
| Per Transaction | 각 측정 지표 값들을 트랜잭션 개수로 나눈 것이다. 한 트랜잭션 내에서 평균적으로 얼만큼의 부하(Load)가 발생하는지를 나타내는 것인데, 사실 트랜잭션개수가 commit 또는 rollback 수행횟수를 단순히 더한 값이어서 의미 없는 수치로 받아들여질 때가 종종 있다. |
| Redo size | select value rsize from v$sysstat where name = 'redo size'; |
| Logical reads | select value gets from v$sysstat where name = 'session logical reads'; |
| Block changes | select value chng from v$sysstat where name = 'db block changes'; |
| Physical reads | select value phyr from v$sysstat where name = 'physical reads'; |
| Physical writes | select value phyw from v$sysstat where name = 'physical writes'; |
| User calls | select value ucal from v$sysstat where name = 'user calls'; |
| Parses | select value prse from v$sysstat where name = 'parse count (total)'; |
| Hard parses | select value hprse from v$sysstat where name = 'parse count (hard)'; |
| Sorts | select srtm+srtd from (select value srtm from v$sysstat where name = 'sorts (memory)'), (select value srtd from v$sysstat where name = 'sorts (disk)'); |
| Logons | select value logc from v$sysstat where name = 'logons cumulative'; |
| Executes | select value exe from v$sysstat where name = 'execute count'; |
| Transactions | select ucom+urol from (select value ucom from v$sysstat where name = 'user calls'), (select value urol from v$sysstat where name = 'user rollbacks'); |
| % Blocks changed per Read | 읽은 블록 중 갱신이 발생하는 비중을 나타낸다. | select round(100*chng/gets,2) "% Blocks changed per Read" from (select value chng from v$sysstat where name = 'db block changes'), (select value gets from v$sysstat where name = 'session logical reads'); |
| Rollback per transaction % | 최종적으로 커밋되지 못하고 롤백된 트랜잭션 비중을 나타낸다. | select round(100*urol/(ucom+urol),2) "Rollback per transaction %" from (select value ucom from v$sysstat where name = 'user calls'), (select value urol from v$sysstat where name = 'user rollbacks'); |
| Recursive Call % | 전체 Call 발생 횟수에서 Recursive Call이 차지하는 비중을 나타낸다. 사용자 정의 함수/프로시져를 많이 사용하면 이 수치가 높아지며, 하드파싱에 의해서도 영향을 받는다. | select round(100*recr/(recr+ucal),2) "Recursive Call %" from (select value recr from v$sysstat where name = 'recursive calls'), (select value ucal from v$sysstat where name = 'user calls'); |
| Rows per Sort | 소트 수행 시 평균 몇 건씩 처리했는지를 나타낸다. | select decode((srtm+srtd), 0, to_number(null), round(srtr/(srtm+srtd),2)) from (select value srtm from v$sysstat where name = 'sorts (memory)'), (select value srtd from v$sysstat where name = 'sorts (disk)'), (select value srtr from v$sysstat where name = 'sorts (rows)'); |
| Buffer Nowait % | 버퍼 블록을 읽으려 할 때 buffer busy waits 대기 없이 곧바로 읽기에 성공한 비율이다. | select round(100*(1-bfwt/gets),2) from (select sum(count) bfwt from v$waitstat), (select value gets from v$sysstat where name = 'session logical reads'); |
| Redo NoWait % | Redo 로그를 기록할 공간을 요청하지 않고 곧바로 Redo 엔트리를 기록한 비율을 말한다. 이 비율이 낮다면, 로그스위칭이 느리거나 너무 자주 발생함을 의미한다. 로그 스위칭 횟수가 문제라면 Redo 로그 파일 크기를 증가시킬 필요가 있다. 로그 스위칭이 자주 발생하지 않는데도 이 항목이 낮은 수치를 보인다면, I/O 서브 시스템이 느린 것이 원인일 것이다. Redo 로그 파일을 덜 바쁜 디스크 또는 Redo 로그만을 위한 전용 디스크로 옮기는 것을 고려해야 한다. 비용이 허락된다면 더 빠른 디바이스로 교체하는 것도 방법이다. | select round(100*(1-rlsr/rent),2) "Redo NoWait %" from (select value rlsr from v$sysstat where name = 'redo log space requests'), (select value rent from v$sysstat where name = 'redo entries'); |
| Buffer Hit % | 디스크 읽기를 수반하지 않고 버퍼 캐시에서 블록 찾기에 성공한 비율이다. | select round(100*(1-(phyr-phyrd-nvl(phyrdl,0))/gets),2) "Buffer Hit %" from (select value phyr from v$sysstat where name = 'physical reads'), (select value phyrd from v$sysstat where name = 'physical reads direct'), (select value phyrdl from v$sysstat where name = 'physical reads direct (lob)'), (select value gets from v$sysstat where name = 'session logical reads'); |
| Latch Hit % | 래치 경합없이 첫 번째 시도에서 곧바로 래치를 획득한 비율을 말한다. | select round(100*(1-sum(misses)/sum(gets)),2) "Latch Hit %" from v$latch; |
| Library Hit % | 이 항목부터 '% Non-Parse CPU'까지는 파싱 부하와 관련 있는 측정 항목들이다. 라이브러리 캐시 히트율(Hit Ratio)은 Get 히트율과 Pin 히트율로 나눌수 있는데, 여기서는 그 중 Pin 히트율을 표시하고 있다. | |
| Pin 히트율 | 실행 단계와 관련있다. 라이브러리 캐시에 이미 적재된 SQL 커서를 실행하거나 오브젝트 정보를 읽으려 할 때 해당 커서 또는 오브젝트 정보가 힙(Hip)영역에서 찾아진다면 히트(Hit)에 성공한 것이다. 만약 캐시에서 밀려나 찾을수 없는 경우가 빈번하게 발생한다면 히트율이 낮게 나타나고, 그만큼 다시 로드해야 하는 부하가 생기므로 라이브러리 캐시 효율이 좋지 않음을 뜻한다. 참고 : Get 히트율 : Parse 단계와 관련 있다. 이 수치가 낮다면 해당 SQL 커서 또는 오브젝트에 대한 핸들을 찾을 수 없어 하드파싱 또는 최초 로드가 자주 발생하는 경우이다. | Pin 히트율 select round(100 * sum(pinhits)/sum(pins),2) "Library Cache Pin Hit %" from v$librarycache; Get 히트율 select round(100 * sum(gethits)/sum(pins),2) "Library Cache Get Hit %" from v$librarycache; |
| Soft Parse % | 실행계획이 라이브러리 캐시에서 찾아져 하드파싱을 일으키지 않고 SQL을 수행한 비율을 말한다. 공식 : (전체Pase Call 횟수 - 하드파싱 횟수)/(전체 Parse Call 횟수)*100 이 비율이 낮다면 바인드 변수를 사용하도록 애플리케이션을 개선해야 한다. | select round(100*(1-hprs/prse),2) "Soft Parse %" from (select value hprs from v$sysstat where name = 'parse count (hard)'), (select value prse from v$sysstat where name = 'parse count (total)'); |
| Execute to Parse % | Parse Call 없이 곧바로 SQL을 수행한 비율, 즉 커서를 애플리케이션에서 캐싱한 채 반복 수행한 비율을 말한다. 애플리케이션 커서 캐싱 기법은 라이브러리 캐싱한 채 반복 수행한 비율을 말한다. | select round((1-prse/exe)*100,2) "Execute to Parse %" from (select value prse from v$sysstat where name = 'parse count (total)'), (select value exe from v$sysstat where name = 'execute count'); |
| Parse CPU to Parse Elapsd % | 파싱 총 소요 시간 중 CPU time이 차지한 비율이다. 파싱에 소요된 시간 중 실제 일을 수행한 시간 비율을 말하며, 이 값이 낮다면 파싱 도중 대기가 많이 발생했음을 의미한다. 이 수치가 낮다면 Shared Pool과 라이브러리 캐시에 경합이 많다는 것을 뜻한다. 대개 하드 파싱 부하 때문이다. | select decode( prsela, 0, to_number(null), round(prscpu/prsela*100,2)) "Parse CPU to Parse Elapsd %" from (select value prsela from v$sysstat where name = 'parse time elapsed'), (select value prscpu from v$sysstat where name = 'parse time cpu'); |
| % Non-Parse CPU | SQL을 수행하면서 사용한 전체 CPU time 중 파싱 이외의 작업이 차지한 비율이다. 이 비율이 낮다면 파싱 과정에서 소비되는 CPU time 비율이 높은 것이므로 파싱 부하를 줄이도록 애플리케이션을 개선해야한다. | select decode( tcpu, 0, to_number(null), round(100*(1-(prscpu/tcpu)),2)) "% Non-Parse CPU" from (select value tcpu from v$sysstat where name = 'CPU used by this session'), (select value prscpu from v$sysstat where name = 'parse time cpu'); |
| In-memory Sort % | 전체 소트 수행횟수엣 In-memory Sort 방식으로 수행한 비율을 말한다. | select decode( (srtm+srtd) , 0, to_number(null) , round(100*srtm/(srtd+srtm),2)) "In-memory Sort %" from (select value srtm from v$sysstat where name = 'sorts (memory)'), (select value srtd from v$sysstat where name = 'sorts (disk)'); |
| Memory Usage % | Shard Pool 내에서 현재 사용 중인 메모리 비중을 말한다. | select 100*(1-sum(decode(name, 'free memory', bytes)) / sum(bytes)) "Memory Usage %" from v$sgastat where pool = 'shared pool'; |
| % SQL with executions>1 | 전체 SQL 개수에서 두번이상 수행된 SQL이 차지하는 비중을 말한다. 이 값이 낮게 나타난다면 조건절에 바인드 변수를 사용하지 않고 Literal상수 값을 이용하는 쿼리의 수행빈도가 높은것을 의미한다. | |
| % Memory for SQL w/exec>1 | 전체 SQL이 차지하는 메모리 중 두번 이상 수행된 SQL이 차지하느느 메모리 비중이다. 이 값이 낮게 나타난다면 조건절에 바인드 변수를 사용하지 않고 Literal 상수 값을 사용하는 쿼리에 의해 Shared Pool이 낭비되고 있을을 의미한다. |
Total Call(=Response) Time = Service Time + Queue Time
= CPU time + Wate Time