Statspack Report
Instance Efficiency Percentages (인스턴스 효율)
이 섹션에서는 각 히트율을 확인할 수 있다. 특히 Buffer Hit ratio, Library Hit ratio 등의 메모리 Hit ratio은 반드시 확인해야 한다.
Buffer nowait ratio
- 프로세스가 버퍼를 기다리지 않고 바로 얻는 비율
- 다른 프로세스가 블럭읽기를 마치기를 기다리거나 incompatible mode에 있어 기다린 횟수가 많은 경우 이 값이 떨어진다.
- 이 값이 낮은 경우, Top-5 wait events 섹션에서 buffer busy waits가 있는지 없는지 확인해야 한다.
Redo nowait ratio
- Redo 로그의 생성시에 log switch를 대기할 필요가 없는 비율
- Redo nowait ratio가 100%일 경우, 인스턴스는 redo log를 사용하는데 기다릴 필요가 없다.
- 이 값이 낮은 경우는 log switch가 빈번하거나 log swith에 걸리는 시간이 길 경우이다.
- 만약 비율이 99% 이하일 경우 redo log Buffer/File의 크기가 너무 작지 않은지 확인한다. - log switch가 빈번할 경우
- 만약 비율이 99% 이하일 경우 buffer cache에 dirty buffer가 너무 많이 유지되고 있진 않은지 확인한다.
참고
dirty buffer : 사용자가 사용하여 내용이 변경되었지만 아직 디스크에 기록되지 않은 버퍼를 말한다.
Buffer Hit ratio
- Buffer Cache Hit ratio 이다.
- Buffer Hit ratio는 버퍼에 대한 요청이 발생했을 때, 그 버퍼가 메모리에서 이용가능하고 물리 디스크 I/O에서 일어날 필요가 없는 비율을 의미한다.
- 즉, 필요한 데이터 블럭이 캐쉬상에서 발견되었으므로 디스크 IO가 발생하지 않는 비율이다.
- Hit ratio는 60~70% 이상이어야 한다.
- Buffer Hit ratio가 작을때는 db_cache_size를 점검해야 한다.
- Buffer Hit ratio는 버퍼 캐쉬의 사이즈가 너무 작거나 풀테이블 스캔이 대량으로 발생할 경우 과도하게 낮아진다.
- 버퍼캐쉬의 사이즈는 Top-5 wait events 섹션의 free buffer waits나 write complete waits를 참고한다.
- 풀테이블 스캔의 발생회수는 Instance Activity Stats 섹션의 table scans(long table)로 알 수 있다.
In-memory sort ratio
- 95% 이상이어야 한다.
- 인스턴스가 물리적 IO 대비 메모리에서 sort작업을 하는 ratio를 의미한다.
- 즉, sort 처리시에 disk sort의 필요성이 없었던 비율을 말한다.
- OLTP 시스템에서는 절대 디스크에서 sort를 하면 안된다. == 이 비율이 높아야 한다.
- In-memory sort ratio 가 낮을 경우, spfile/pfile에 있는 SORT_AREA_SIZE 나 PGA_AGGREGATE_TARGET을 증가시켜라.
Library Hit ratio
- SQL의 실행 코드가 이미 Library cache(Shared pool)상에 존재하는 비율을 의미한다.
- 만약 Library Hit ratio가 낮다면, 그것은 shared pool size가 너무 작거나 SQL문에 bind 변수들이 사용되지 말아야 한다는 것을 의미한다.
- Library Hit ratio는 90% 이상이 되어야 한다.
- 이 비율이 낮을 경우 Soft Parse ratio도 낮다.
Soft Parse ratio
- SQL의 해석시에 이미 Library Cache에 해석이 끝난 SQL이 존재하므로 새로운 해석 처리를 실시할 필요가 없었던 것을 말한다.
- 즉, SQL의 parse시에 이미 SQL문이 Library cache에 존재하고 있던 비율을 말한다.
- 95% 이상이 이상적이다.
- 만약 이 퍼센티지가 80%이하일 경우, bind변수나 CURSOR_SHARING(8.1.6)을 이용하여 SQL문이 보다 많이 공유되도록 해야 한다.
- Soft Parse ratio가 낮다는 것은 새로운 해석 처리(Hard Parse)가 많다는 것을 의미한다.
- 해석 결과는 Library Cache에 보존되기 때문에 Soft Parse ratio가 낮으면 Library Hit ratio도 낮아진다.
- Library Cache가 작을경우 Parse 결과를 보존할 수 있는 SQL의 수가 적어서 Hard Parse가 많아질 수도 있다.
- OLTP(On-Line Transaction Processing) 시스템의 경우 가능하면 100%에 가까워야 한다.
-
Data Warehouse의 경우 일반적으로 낮은 Soft Parse ratio를 보여준다.
Execute to Parse ratio
- Execute to parse ratio는 실행횟수를 parse 횟수와 비교한 비율 말한다, 즉, parse 없이(soft parse 포함) execute(실행)한 비율이다.
- 이 퍼센티지를 얻는 공식은 다음과 같다. round(100*(1-parsevalue/executevalue),2)
- 오라클의 Library cache 기능은 SQL문을 실행할 때마다 해석하는 것이 아니라, 한번 해석한 결과를 공유하여 실행하는 것을 가능하게 한다.
- 각각의 구문은 오직 하나의 parse가 필요하다.
Latch Hit ratio
- 이 ratio는 100%에 가까워야 한다. 그렇지 않을 경우, top wait event들을 확인해라.
- 이 퍼센티지가 낮을 경우, Latch 경합이 발생하고 있다는 것을 의미한다.
- Latch 경함에 관해서는 Top-5 wait events 섹션의 latch free 이벤트가 있는지 확인하고, 만약 있다면 Latch Activity 섹션에서 문제의 Latch를 특정한다.
Parse CPU to Parse Elapsd ratio
- parse 처리 시간에 대한 실제 처리 시간의 비율.
- 이 비율이 낮다는 것은, parse시에 실제는 처리를 하지 않고 대기하고 있는 시간이 길다는 것을 의미한다.
- 대부분 Latch에 대한 대기시간이 길어진다.
- "Latch Activity" 섹션에서 Shared pool latch나 library cache latch의 경합이 발생하고 있는지 확인해야 한다.
ratio Non-Parse CPU
- 인스턴스의 전처리 시간에 대한 parse 이외의 처리 시간의 비율
- 이 퍼센티지가 낮은 경우 SQL문의 해석 처리 빈도가 너무 높다는 것을 의미한다.
- bind 변수등을 사용하는 SQL문의 공유가 이루어지게 튜닝해야 한다.
- 또는, shared pool의 size가 너무 작아서 캐쉬되어 있던 공유 커서가 age out하고 있을 가능성이 있기 때문에 Shared Pool Statistics을 확인해봐야 한다.
Shared Pool Statistics
Shared pool의 통계 정보
Memory Usage %
- 사용된 shared pool의 비율
% SQL with executions>1
- 2번 이상 실행된 공유 커서(SQL)의 비율(수)
% Memory for SQL w/exec>1
- 2번 이상 실행된 공유 커서(SQL)가 사용한 메모리 비율
- 이 퍼센티지가 낮다는 것은, 1번 실행된 SQL문이 Shared pool의 대부분을 차지하고 있다는 것을 의미한다.
- 즉, 어플리케이션으로부터 발행되는 SQL문이 공유되어 있지 않다는 의미이다.
Top 5 Timed Events (Top 5 Wait Events)
Oracle 9i realase1 이전 버전에서는 Top 5 Timed Events 라 하고, Oracle 9i release2 이후 버전에서는 Top 5 Wait Events 라 한다.
이 섹션은 Statspack report에서 가장 중요하고 의미있는 섹션 중 하나이다.
시스템이 가진 두드러진 문제점을 간접적으로 설명해준다.
어떤 이벤트가 가장 많은 waiting 시간을 소비했는지 알려준다.
wait event란 oracle process가 처리중 대기해야 했던 내역에 대한 통계를 말한다.
Wait events 설명
oracle process가 wait하는 경우 간략 설명
|
구분
|
이벤트명
|
설 명
|
| 일상적인 Wait Event | db file scattered read | Full Scan시 OS에 I/O를 요청해놓고 대기 |
| db file sequential read | Index Scan시 OS에 I/O를 요청해놓고 대기 | |
| (IO, Network) | log file sync | 변경 log buffer를 log file에 반영하는 동안 대기 |
| DFS lock handle | OPS 환경에서 노드간 분산 Lock 교환에 따른 대기 | |
| global cache cr request | OPS 환경에서 노드간 Buffer Block 교환에 의한 대기 | |
| 자원 경합에 따른 Wait Event |
enqueue | Type에 따라 세분화 (24개의 enqueue type (9i)) |
| latch free | Name에 따라 세분화 (239개의 latch가 존재 (9i)) | |
| buffer busy waits | 동일블록에 대한 동시 액세스에 따른 경합 | |
| free buffer waits | free buffer를 할당위해 DBWR의 Write를 대기 | |
| Log buffer space | Log buffer를 할당 받기 위해 LGWR의 write를 대기 | |
| library cache lock | SGA내의 library cache를 참조하기 위한 대기(검색) | |
| row cache lock | SGA내의 dictionary cache를 참조하기 위한 대기 | |
| Idle Event | SQL*Net message from client | Client로부터의 작업요청을 대기 |
| Pmon timer | PMON이 할일 없을 때 대기하는 Event |
CPU Time
- 실제 wait event는 아니지만, 이 세션동안 사용된 CPU의 합 또는 snapshot window 동안 사용된 CPU time의 합이다.
local write wait
-
db file parallel write
- 이름이 암시하는 것과 달리,db file parallel write 대기이벤트는 병렬 처리(parallel DML)와 연관은 없다
- DBWR이 더티 블록를 기록하기 위한 I/O 요청을 보낸 후 요청이 끝나기를 기다리는 동안db file parallel write 이벤트를 대기하게 된다
- DBWR가 write batch 를 수행한 후 I/O 요청이 완료되기를 대기할 때 해당 이벤트가 발생
참고
write batch : 한번의 I/O 요청에 여러 개의 더티 블록을 디스크에 기록하는 방식
direct path read
- Parallel Query 수행시 슬레이브 세션(Slave Session)이 수행하는 direct path I/O에 의해 발생
- direct path I/O는 SGA 내의 버퍼캐쉬를 거치지 않고 세션의 PGA 로 직접 블록을 읽어 들이는 것을 말한다.
- Direct read I/O는 일반적으로 디스크에 위치한 임시(temporary) 세그먼트를 액세스 하는 동안 사용된다. 이러한 작업은 정렬(sort), 병렬조회(parallel query) 및 해쉬조인(hash join)시에 발생한다.
direct path write
- buffer cache를 거치지 않고 PGA의 buffer에서 바로 datafile로 write하는 작업 중 write 요청이 완료되기를 기다리고 있는 상태를 의미.
- 세션이 I/O 처리가 완료되었다고 인지하는 시점에 direct path write wait event를 wait한다.
- direct path write 대기는 Direct load 작업(CTAS: Create Table As Select, insert /*+ append */ ... 등)이 발생함을 의미한다
- 이러한 작업이 요청될 경우 오라클은 SGA를 경유하지 않고 데이터 파일에 직접 쓰기 작업을 수행한다. 즉, DBWR에 의해 쓰기 작업이 이루어지는 것이 아니라 서버 프로세스에 의해 직접 쓰기작업이 이루어진다
log file parallel write
- LGWR 프로세스에서만 발생하는 대기이벤트
- LGWR는 redo buffer의 내용을 redo log 파일에 기록하기 위해 필요한 I/O 콜을 수행한 후 I/O 작업이 완료되기를 기다리는 동안 log file parallel write 이벤트를 wait하게 된다
log file sync
- Server Process는 Commit 명령을 내린 후 LGWR가 성공적으로 기록을 할 때까지 기다리게 되는데, 이때 log file sync event를 wait하게 된다
- 다시 말해 로그 동기화(log synchronization)를 수행하는 동안,LGWR 프로세스는 “sync write” 가 완료할 때까지log file parallel write 대기이벤트를 대기하게 되고, Server Process는log file sync 대기이벤트를 대기하는 것이다.
sort segment request
-
control file parallel write
- control file parallel write wait event는 세션이 모든 컨트롤 파일(control file)에 대한 쓰기 I/O 요청이 완료되기를 wait할 때 발생한다
control file sequential read
-
latch free (latch : rock의 일종)
- 프로세스가 Willing-to-wait 모드로 _SPIN_COUNT 만큼 스핀을 수행하고도 latch 획득에 실패하여 sleep한다는 것을 의미한다.
db file sequential read
- 싱글블록 I/O와 함께 발생하는 대기 이벤트이다
- 한 번의 싱글블록 I/O가 발생할 때마다 한 번의db file sequential read 이벤트 대기가 발생한다
- 싱글 블록 I/O는 파일로부터 하나의 블록을 읽는 모든 작업들에서 발생 가능하다
- 보통 인덱스 스캔, ROWID에 의한 테이블 스캔, 컨트롤 파일(Control file), 파일 헤더(File header)를 읽을 때 발생한다
참고
Conventional Path I/O
: 사용자가 요청한 데이터가 SGA의 버퍼 캐쉬에 존재하지 않을 경우 서버 프로세스가 데이터 파일로부터 해당 데이터 블록을 버퍼 캐쉬로 로드하는 것
방식
- Single Block I/O : 한 번에 하나의 블록만 읽어 들이는 작업을 수행하는 I/O
- Mulit Block I/O : 한 번에 여러 개의 연속된 블록을 읽어 들이는 작업을 수행하는 I/O
SQL*Net message from client
- The server process (foreground process) waits for a message from the client process to arrive
SQL*Net message to client
- The server (foreground process) is sending a message to the client
wakeup time manager
-
virtual circuit status
- The session waits for a virtual circuit to return a message type indicated by status.
rdbms ipc message
-The background processes (LGWR, DBWR, LMS0) use this event to indicate that they are idle and are waiting for the foreground processes to send them an IPC message to do some work.
이 글은 스프링노트에서 작성되었습니다.