1. Summary Information
database ID 및 이름, instance 이름, version 과 같이 statspack report가 수집된 instance에 대한 정보와 report에 이용된 snapshot 정보를 제공한다.
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
3909019673 ora10g 1 14-5월 -06 20:35 10.2.0.1.0 NO
Host Name: STORM-NOTEBOOK Num CPUs: 1 Phys Memory (MB): 1,022
~~~~
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 1 14-5월 -06 22:03:11 16 3.8
End Snap: 12 14-5월 -06 22:07:29 16 5.3
Elapsed: 4.30 (mins)
2. Load Profile
- snapshot interval 사이의 시스템의 workload(작업부하)가 얼마나 되는지를 설명해 주는 부분이다.
- 다른 스냅샷간에서 작성된 2개 이상의 리포트를 사용하여 업무량을 비교 할 때 유용하다.
- 즉, Redo 발생 량 및 logical & physical block IO 량 및 parse, sort 정보를 초/트랜잭션 별로 제공을 하고 있어 이 내용을 비교하여 시간대별 작업량의 변동 및 타 시스템과의 workload 비교가 가능하다.
- Redo size,Block changes,%Blocks changed per read 이 현저하게 증가한 경우라 한다면 insert/update/delete처리가 보다 많이 행해졌다는 것이 된다.
Per Second(초당) Per Transaction(트랜잭션당)
--------------- ---------------
Redo size: 4,402.51 81,132.00
Logical reads: 58.97 1,086.79
Block changes: 7.77 143.14
Physical reads: 0.17 3.07
Physical writes: 1.31 24.07
User calls: 0.43 7.93
Parses: 6.58 121.29
Hard parses: 0.89 16.36
Sorts: 5.30 97.71
Logons: 0.03 0.64
Executes: 11.62 214.21
Transactions: 0.05
% Blocks changed per Read: 13.17 Recursive Call %: 99.72
Rollback per transaction %: 0.00 Rows per Sort: 10.63
3. Instance Efficiency Percentages (인스턴스 효율)
시스템 성능 진단 과정에서 어떤 부분에 문제가 있는지를 판별 할 수 있는 정보를 제공해준다.
- Buffer nowait : process가 buffer 위하여 기다리지 않고 바로 얻은 비율로 다른 process에 의하여 block이 읽기가 마치기를 기다리거나 incompatible mode에 있어 기다린 횟수가 많은 경우 이 값이 떨어지게 된다
- Buffer hit : buffer cache hit ratio 이다. Hit Ratio 는 60~70% 이상이어야 하며 수치가 적을 때는 db_cache_size를 점검 해야 한다.
- Redo Nowait : 만약 이 비율이 99% 이하의 경우 아래의 내용들을 의심하여 볼 수 있다.
.redo log Buffer/File의 크기가 너무 작지 않은가?
.buffer cache에 dirty buffer가 너무 많이 유지되고 있진 않는가?
- In-memory Sort : index 생성, sort morge join, order by, group by , 기타 등등의 sort작업시 disk sort 대 memory sort 비율 이다.
- Library Hit : Library Cache의 Hit Ratio는 90%이상이 되어야 한다. 90% 이하이면 Shared Pool Size를 늘려주거나, SQL 문의 이상을 조사해야 한다.
- Memory Usage % ： 사용된 Shared Pool의 비율
- % SQL with executions>1： 재 사용된 SQL문 비율
- % Memory for SQL w/exec>1：２회이상 실행된 SQL이 사용한 메모리 비율
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.72 In-memory Sort %: 100.00
Library Hit %: 80.63 Soft Parse %: 86.51
Execute to Parse %: 43.38 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 63.41 % Non-Parse CPU: 78.33
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 77.87 81.52
% SQL with executions>1: 68.00 75.73
% Memory for SQL w/exec>1: 87.01 81.60
4. Top 5 Wait Events
이 부분은 시스템이 가진 두드러진 문제점을 간접적으로 설명해 주는 top 5 wait event 정보이다. Wait event는 session이 어디서 얼마나 오랫동안 멈춰 있었는지를 설명하는 정보로 가장 문제가 되는 top 5 wait event들에 대한 정보를 제공 한다.
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 5 76.7
control file sequential read 527 1 1 9.6
db file parallel write 184 0 2 5.1
db file sequential read 43 0 5 3.3
control file parallel write 86 0 2 3.1
-------------------------------------------------------------
4.1 CPU time
- Response Time = Service Time + Wait Time
- Wait Time = the sum of time spent on Wait Events
- Service Time = CPU used by this session = CPU Parse + CPU Recursive + CPU Other
- 만약 시스템이 높은 CPU time을 보인다면 statspack report의 'SQL by Gets section' 에서 buffer 를 많이 사용하는 SQL문을 대상으로 tuning작업을 하여 Service time을 줄일 수 있다.
- 만약 CPU time에 비하여 높은 wait time을 보이는 경우 시스템은 resource contention이 있다는 것을 의미하며, 높은 wait event를 보이는 부분부터 wait time을 줄임으로써 시스템 전반적인 Response time을 줄일수 있다.
4.2 Common Wait Event Problem Areas
4.2.1 buffer busy wait- buffer busy wait event는 oracle process가 사용중인 buffer를 기다리는 상태에서 가지게 되는 이벤트이다.
- 일반적으로 buffer busy wait이 심한 경우 hot block에 의한 현상이거나 IO 상의 bottleneck 으로 인한 경우가 가장 흔하다.
- 사실 buffer busy wait에 대한 가장 효과적인 해결책은 buffer cache tuning과 SQL tuning이라고 할 수 있다.
4.2.2 direct path write- Buffer cache를 거치지 않고 PGA의 buffer에서 바로 datafile로 write하는 작업 중 write 요청이 완료되기를 기다리고 있는 상태를 의미한다.
- 예를 들어 Disk Sort, hash join, Parallel DML operation, direct path insert 등과 같은 작업 시 write complete가 되기를 기다리는 경우 'direct path write' wait 상태에 있게 된다.
4.2.3 Log file sync- Log file sync는 oracle 이 commit 발행 시 관련된 redo record가 buffer에서 redo logfile에 flush 되는 동안 가지게 되는 wait event로 너무 많은 commit request가 있거나 LGWR의 IO 작업이 원활하지 않는 경우 이러한 현상이 발생할 수 있다.
- redo logfile과 datafile 및 archive 파일을 분리하여 IO를 분산하거나, 가급적 redologfile을 IO 성능 개선을 유도할 수 있는 장치를 사용한다.
4.2.4 DB File Scattered Read- 일반적으로 FULL 테이블 스캔과 관련된 대기를 나타낸다
- 여기서 대기 개수가 많다는 것은 index가 존재하지 않아 full table scan을 하고 있는지를 확인하여 보아야 하며 index가 존재하더라도 부정확한 통계정보로 인하여 full table scan을 하고 있는지를 확인하여 보아야 한다.
4.2.5 DB File Sequential Read- DB File Sequential Read 는 index 의 rowid 정보를 이용하여 data block을 access할 때 발생 할 수 있는 wait event로 read block은 1개가 된다.
- 이 wait event가 심한 경우 아래 사항들을 점검하여 조치하도록 한다.
.낮은 Buffer cache hit ratio
.많은 Data update 작업 후 변경되지 않은 table, index statistics
.Buffer gets가 높은 SQL 문장에 대한 tuning 여부
.Partitioning 기법 고려
.많은 chained rows.
5. SQL문에 대한 통계정보
Statspack Report에는 resource를 많이 사용한 SQL문장 들을 아래와 같이 resource별로 정리하여 주는 section을 제공하고 있어 SQL tuning 대상 선정에 도움을 주고 있다. 스냅샷 레벨 0에서는 아래 정보들이 생성되지 않는다.
- - SQL Ordered by CPU Time : CPU를 많이 사용한 문장
- - SQL Ordered by Elapsed Time : 실행 시간을 많이 사용한 문장
- - SQL ordered by Gets : Buffer를 많이 사용한 문장
- - SQL ordered by Reads : Disk IO를 많이 한 문장
- - SQL ordered by Executions : 수행 횟수가 많은 문장
- - SQL ordered by Parse Calls : soft parse calls
- - SQL ordered by Sharable Memory : Library cache 내 많은 memory를 사용하고 있는 문장
참고문서
- 시스템성능 진단을 위한 Statspack 사용방법 - 한국오라클 (주) 제품지원실 이상헌 -
- http://blog.naver.com/hirokorea?Redirect=Log&logNo=20023344842
참고링크
- 강좌 URL : http://www.gurubee.net/lecture/1916
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.