1. db file scatterd read
오라클은 풀테이블스캔 (Full Table Scan. FTS) 이나 인덱스풀스캔 (Index Full Scan) 을 수행하는 경우
성능보장을 위해서 가능한 여러 개의 블록을 한꺼번에 읽는다. 이것을 멀티 블록 IO (Multi Block I/O) 라고 부른다.
FTS 에서 싱글 블록 IO 를 사용하거나 MBRC 보다 작은 수의 블록을 읽어들이는 경우는 다음과 같다.
- 익스텐트 경계에 도달한 경우
- 스캔 도중에 캐시된 블록이 있을 경우
- Chaind Row 가 있는 경우
1) 어플리케이션 레이어
db file scattered read 대기가 주로 발생하는 SQL 문을 추출해야한다.
2) 오라클 메모리 레이어
버퍼 캐시의 크기가 지나치게 작다면 그만큼 물리적 I/O 가 반복해서 필요하고 그와 비례해서 db file scatterd read 대기도 늘어난다.
다중 버퍼 풀은 세가지 면에서 버퍼캐시의 성능을 개선시킨다.
첫째, 자주 액세스되는 객체를 메모리에 상주시킴으로써 물리적인 I/O 를 최소화한다.
둘째, 휘발성의 데이터는 빠른 속도로 메모리에서 재활용함으로써 메모리의 낭비를 최소화한다.
셋째, 각 버퍼마다 별도의 cache buffer lru chain 래치를 사용하기 때문에 래치 경합을 감소시키는 효과가 있다.
큰 크기의 블록을 사용하는 것 또한 FTS 의 성능을 향상시키는 방법이다.
첫째, 한 블록이 포함하는 로우수가 증가하므로 같은 크기의 테이블을 구성하는데 적은 수의 블록을 사용하게 된다.
둘째는 블록 사이즈가 크면 Row chaining 이나 Row migration 이 발생할 확률이 낮아진다.
3) 오라클 세그먼트 레이어
파티셔닝 (Partitioning) 을 적절히 수행함으로써 FTS 의 범위를 줄일 수 있는지 검토할 필요가 있다.
4) OS/디바이스 레이어
SQL 최적화나 버퍼 캐시의 최적화로도 문제가 해결되지 않는다면, I/O 시스템 자체의 성능을 의심해보아야 한다.
2. db file sequential read
db file scattered read 이벤트가 멀티 블록 IO 와 함께 발생하는 대기이벤트라면,
db file sequential read 이벤트는 싱글 블록 IO 와 함께 발생하는 대기이벤트이다.
한번의 싱글 블록 IO 가 발생할 때마다 한번의 db file sequential read 이벤트가 발생한다.
싱글 블록 IO 는 파일로부터 하나의 블록을 읽는 모든 작업들에서 발생 가능하다.
보통 인덱스 스캔, ROWID 에 의한 테이블 스캔, 컨트롤 파일 (Control file), 파일 헤더 (File Header) 를 읽을 때 발생한다.
1) 어플리케이션 레이어
비효율적인 SQL 문장이나 비효율적인 인덱스 스캔이 자주 수행되는 경우 불필요한 물리적 I/O 로 인해 db file sequential read 대기가 증가할 수 있다.
2) 오라클 메모리 레이어
버퍼 캐시의 크기가 지나치게 작은 경우 반복적으로 물리적 I/O 발생하고 이로 인해 db file sequential read 대기가 증가할 수 있다.
인덱스가 효율적으로 생성되었음에도 불구하고 db file sequential read 대기가 기대 이상으로 높다면 다음과 같은 사항을 의심해보아야 한다.
- 클러스터링 팩터 (Clustering Factor, CF) 가 지나치게 높지 않은가?
CF 가 좋지 않다고 해서 항상 성능이 느린 것은 아니며 문제를 정확하게 파악하는 것이 매우 중요하다.
더구나 ASSM 과 같은 관리기법을 사용할 경우 기존에 비해서 CF 값이 높아지는 경향이 있으므로 판단에 주의를 요할 필요가 있다.
- Row chaining 이나 Row migration 이 지나치게 많이 발생하지 않았는가?
Row chaining 은 로우의 크기가 블록보다 큰 경우에 발생한다. 따라서 테이블 정의를 변경하거나 PCTFREE 값을 작게 해서 테이블을 재생성하거나,
혹은 더 큰 크기의 블록을 사용하는 것 외에는 Row chaining 을 피할 방법은 없다.
Row migration 은 row chaining 과는 달리 최초에는 하나의 블록 안에 정상적으로 삽입되었으나,
이후 블록의 여유 공간이 소진된 상태에서 Update 에 의해 로우 크기가 늘어난 경우에 발생한다.
Row migration 은 테이블을 재구성함으로써 해소할 수 있다.
- export 후 import 한다.
- alter table xxx move ... 를 수행한다.
- analyze table xxx list chained rows into yyyy 를 수행해서 migration 이 발행한 로우들을 추출하고
해당 로우들에 대해 delete/insert 를 수행한다.
3) OS/디바이스 레이어
SQL 최적화나 버퍼 캐시 최적화, 테이블 재구성으로도 문제가 해결되지 않는다면, I/O 시스템 자체의 성능을 의심해 보아야 한다.
3. direct path read
direct path read 이벤트 대기는 Parallel Query 수행시 슬레이브 세션 (Slave Session) 이 수행하는 direct path I/O 에 의해 발생한다.
4. direct path write
direct path write 대기는 Direct load 작업 (CTAS : Create Table As Select, insert /*+ append */ ... 등) 이 발생함을 의미한다.
5. direct path read temp / direct path write temp
정렬작업을 위해 임시 영역을 읽고 쓴느 경우에는 direct path read temp, direct path write temp 이벤트를 대기한다.
1) 어플리케이션 레이어
정렬이 필요한 SQL 문장들이 최적화되어 있는지 검토해보아야 한다.
2) 오라클 메모리 레이어
PGA 는 정렬, 해시 조인 (Hash Join), 비트맵 (Bitmap) 연산 등의 수행을 위한 메모리 영역을 가지고 있는데, 이것을 작업 공간 (Work Area) 라고 부른다.
9i 부터는 PGA_AGGREGATE_TARGET 파라미터를 이용하면 이러한 작업 공간의 크기를 동적으로 관리할 수 있다.
6. direct path read (lob) / direct path write (lob)
LOB 컬럼을 생성할 때 NOCACHE 옵션을 사용하면 LOB 세그먼트를 읽고 쓰는 작업은 direct path I/O 를 사용하며
이 경우 direct path read (lob), direct path write (lob) 이벤트를 대기하게 된다.
CACHE 옵션을 사용해서 LOB 컬럼을 생성하면 버퍼 캐시를 경우하게 되므로 일반 데이터와 동일한 메커니즘을 따르게 된다.
7. db file parallel write
버퍼 캐시를 경유하는 모든 데이터는 DBWR 에 의해 디스크에 기록이 된다.
DBWR 이 더티 블록을 기록하기 위한 I/O 요청을 보낸 후 요청이 끝나기를 기다리는 동안
db file parallel write 이벤트를 대기하게 된다.
발생원인과 해결책
- I/O 시스템의 성능이 느린 경우
1) 로디바이스 (Raw device) 와 비동기 IO (Asychronous IO) 를 조합해서 사용하는 것이 알려진 최선의 방법이다.
2) OS 차원에서 Direct I/O 를 사용한다.
- I/O 작업이 너무 많은 경우
잦은 체크포인트가 발생하는 경우 DBWR 의 활동량이 지나치게 많아지고 이로 인해 DBWR 의 성능이 저하될 수 있다.
- 버퍼 캐시를 비효율적으로 사용하는 경우
DBWR 의 성능을 간접적으로 개선시키는 또 한가지 방법으로 다중 버퍼 풀 (Default, Keep Recycle) 을 적절히 사용하는 것이다.
8. control file parallel write
일반적인 환경에서는 컨트롤 파일을 갱신하는 회수가 많지 않기 때문에 control file parallel write 대기현상은 잘 발생하지 않는다.
- 로그 파일 스위치가 자주 발생하는 경우
- 체크포인트가 자주 발생하는 경우
- Nologging 에 의한 데이터 파일 변경이 잦은 경우
- I/O 시스템의 성능이 느린 경우