Statspack 이란 ?
- Oracle Database에 대한 부하 및 resource 사용량의 문석이나 성능 문제 분석을 위하여 사용되는 툴
- 성능관련 통계정보들이 PERFSTAT USER에 누적되어 저장되므로 원하는 기간별로 비교 분석이 가능하다.
- DBMS_JOB이나 OS Utillity( ex : cron ) 등을 사용하여 주기적으로 Data를 수집 할 수 있다.
Collecting Snapshots Level
| level | Information Collected |
| 0 | 일반 성능 통계 정보 집 |
| 5 ( default) | resource를 많이 사용하는 SQL에 대한 정보 포함 |
| 6 | Level 5 + SQL 상세 실행 계획정보를 포함 |
| 7 | Level 6 + 세그먼트 정보 포함 |
| 10 | Level 7 + Parent Latch, Children Latch 등의 정보를 포함 |
Statspack Report
1. Summary Information
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
1666671522 CATDB 1 12-Jan-24 09:11 11.2.0.4.0 NO
Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
ora11catalog Linux x86 64-bit 2 2 2 3.7
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 11 12-Jan-24 10:09:08 25 1.4
End Snap: 12 12-Jan-24 10:09:13 27 1.3
Elapsed: 0.08 (mins) Av Act Sess: 0.1
DB time: 0.01 (mins) DB CPU: 0.01 (mins)
DataBase ID 및 이름, instance 이름, version 과 같이 statspack report가 수집된 instance에 대한 정보와 report에 이용 된 snapshot 정보를 제공합니다
2. Load Profile
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 0.2 0.2 0.00 0.08
DB CPU(s): 0.1 0.2 0.00 0.08
Redo size: 162,585.6 203,232.0
Logical reads: 1,444.4 1,805.5
Block changes: 356.0 445.0
Physical reads: 39.6 49.5
Physical writes: 3.8 4.8
User calls: 1.8 2.3
Parses: 96.8 121.0
Hard parses: 22.6 28.3
W/A MB processed: 5.5 6.9
Logons: 0.6 0.8
Executes: 367.2 459.0
Rollbacks: 0.0 0.0
Transactions: 0.8- Snapshot interval 사이의 시스템의 workload(작업부하)가 얼마나 되는지를 설명해 주는 부분입니다.
- Redo size, Block changes, %Blocks changed per read 가 현저하게 증가한 경우라 한다면 DML 처리가 보다 많이 행해졌다는 것이 됩니다.
- Per Second : 각 측정 지표 값을 측정 시간(Snapshot Interval, 초)으로 나눈 것으로 초당 부하 발생량을 의미
1) Redo size : report에서 발생한 redo의 양
2) Logical Reads : cache buffers chains 래치를 획득한 후 해시 체인을 탐색하고 버퍼를 사용하기 위해 buffer lock을 획득한 후 버퍼를 읽는 작업을 말함
3) Block Change : report 간격 동안 수정된 Block의 수
4) Physical Reads : 물리적 I/O를 발생시킨 블록에 대한 요청 수
5) Physical Writes : 물리적 쓰기 수
6) User Calls : 생성된 쿼리 수
7) Parses : 소프트파싱, 하드파싱 합계
3. Instance Efficiency Percentages
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 97.26 Optimal W/A Exec %: 100.00
Library Hit %: 79.46 Soft Parse %: 76.65
Execute to Parse %: 73.64 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 71.43 % Non-Parse CPU: 83.05
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 85.58 85.58
% SQL with executions>1: 81.97 89.73
% Memory for SQL w/exec>1: 81.92 92.64
이 부분은 시스템 성능 진단 과정에서 어떤 부분에 문제가 있는지를 판별 할 수 있는 정보를 줍니다.
1) Buffer nowait : Process가 buffer를 위하여 기다리지 않고 바로 얻은 비율로 다른 process에 의하여 block 읽기가 마치기를 기다리거나 incompatible mode(호환되지않는 모드)에 있어 기다린 횟수가 많은 경우 이 값이 떨어지게 됩니다
2) Buffer hit : buffer cache hit ratio이며 Hit Ratio는 60~70% 이상이어야 하며 수치가 적을 때는 db_cache_size를 점검 해야 합니다.
3) Redo Nowait
만약 이 비율이 99% 이하의 경우 아래의 내용들을 의심하여 볼 수 있음
- redo log Buffer/File의 크기가 작을 경우
- buffer cache에 dirty buffer가 너무 많이 유지되는 경우
4) Library Hit : Library Cache의 Hit Ratio는 90% 이상이 되어야 한다. 90% 미만이면 Shared Pool Size를 늘려주거나 SQL 문의 이상을 조사해야한다.
5) Memory Usage% : 사용된 Shared Pool의 비율
6) % SQL with executions>1 : 재 사용된 SQL문 비율
7) % Memory for SQL w/exec>1 : 2회 이상 실행된 SQL이 사용한 메모리 비율
4. Top 5 Wait Events
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 1 78.2
os thread startup 2 0 29 7.8
db file sequential read 113 0 0 4.6
log file parallel write 3 0 11 4.5
log file sync 2 0 11 2.9
Wait event는 session이 어디서 얼마나 오랫동안 멈춰 있었는지를 설명하는 정보로 가장 문제가 되는 top 5 wait event들에 대한 정보를 제공
CPU time
- Response Time : Service Time + Wait Time
- Wait Time : Wait Event에 소요된 시간의 합계
- Service Time : 현재 세션의 CPU 사용량, CPU Parse + CPU Recursive + CPU Other
-- 만약 시스템이 높은 CPU time을 보인다면 statspace report의 'SQL by Gets section' 에서 buffer를 많이 사용하는 SQL문을 대상으로 튜닝 작업을 하여 Service Time을 줄일 수 있습니다.
-- 만약 CPU time에 비하여 높은 Wait time을 보이는 경우 시스템은 resource contention이 있다는 것을 의미하며, 높은 Wait Event를 보이는 부분부터 Wait time을 줄임으로써 시스템 전반적인 Response Time을 줄일 수 있습니다.
5. Common Wait Event Problem
1. buffer busy wait
- buffer busy wait event는 oracle process가 사용중인 buffer를 기다리는 상태에서 가지게 되는 이벤트이다.
- 일반적으로 buffer busy wait이 심한 경우 hot block에 의한 현상이거나 I/O 상의 병목현상으로 인한 경우가 가장 흔하다
- buffer cache tuning과 SQL tuning이 효과적인 해결책이다.
2. direct path write
- buffer cache를 거치지 않고 PGA의 buffer에서 바로 datafile로 write하는 작업 중 write 요청이 완료되기를 기다리고 있는 상태를 의미
- 예를 들어 Disk Sort, Hash Join, Parallel DML operation, direct path insert 등과 같은 작업 시 write complete가 되기를 기다리는 경우 wait 상태에 있게 된다.
3. Log file sync
- Log file sync는 oracle이 commit이 발행 시 관련된 redo record가 buffer에서 redo logfile에 flush되는 동안 가지게 되는 wait event로 너무 많은 commit request가 있거나 LGWR의 I/O 작업이 원활하지 않은 경우 발생
- redo logfile과 datafile 및 archive 파일을 분리하여 I/O를 분산하거나, 가급적 redologfile을 I/O 성능 개선을 유도할 수 있는 장치로 사용
4. DB File Scattered Read
- 일반적으로 FULL 테이블 스캔과 관련된 대기를 나타낸다.
- 여기서 대기 개수가 많다는 것은 index가 존재하지 않아 full table scan을 하고 있는지를 확인해 봐야 하며 index가 존재하더라도 부정확한 통계 정보로 인해 full table scan을 하고 있는지를 확인해 봐야된다.
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
6. SQL문에 대한 통계정보
- SQL Ordered by CPU Time : CPU를 많이 사용한 문장
- SQL Ordered by Elapsed Time : 실행 시간을 많이 사용한 문장
- SQL Ordered by Gets : Buffer를 많이 사용한 문장
- SQL Ordered by Reads : Disk I/O를 많이 한 문장
- SQL Ordered by Executions : 수행 횟수가 많은 문장
- SQL Ordered by Parse Calls : soft parse calls
- SQL Ordered by Sharable Memory : Library cache 내 많은 memory를 사용하고 있는 문장
'Oracle' 카테고리의 다른 글
| Oracle Partition 테이블 종류와 개념 (0) | 2023.10.11 |
|---|---|
| Oracle ROWID (0) | 2023.09.20 |
| Oracle Index (0) | 2023.09.19 |
| NL Join / Sort Merge Join / Hash Join (0) | 2023.09.19 |
| SQL 트레이스 (0) | 2023.09.13 |