I/O 효율화 원리

1. 블록 단위 I/O

• Sequential 액세스는 레코드 간 논리적 또는 물리적 순서를 따라 차례대로 읽어 나가는 방식이며 Random 액세스는 레코드 간 논리적, 물리적인 순서를 따르지 않고, 한건을 읽기 위해 한 블록씩 접근하는 방식을 말한다. 
• Sequential 액세스의 선택도를 높이기 위해서 인덱스 선두 칼럼이 = 조건이 오도록 설정하거나 선택도가 나쁠 경우 Full Scan을 고려한다. Random 액세스 발생량을 줄이기 위해서는 인덱스 액세스 단계에서 최대한 필터링하는 것이다.

2. Memory vs. Disk I/O

• 디스크 I/O를 최소화하고 대부분 처리를 메모리에서 할 수 있도록 버퍼 캐시 효율성을 높여야 한다.
• 버퍼 캐시 히트율(BCHR)이 높다고 하더라도 논리적으로 읽어야 할 블록 수의 절대량이 많아진다면 논리적인 블록 요청 횟수를 줄임으로써 물리적으로 디스크에서 읽어야할 블록 수를 줄여야 한다. 
• 디스크나 SAN, RAC 인터커넥트 문제이든 I/O 성능에 관한 가장 확실하고 근본적인 해결책은 논리적인 블록 요청 횟수를 최소화하는 것이다.

3. Single Block vs. Multiblock I/O

• db file sequential read 대기 이벤트는 Single Block I/O 방식으로 I/O를 요청할 때 발생한다.
• db file scattered read 대기 이벤트는 Multiblock I/O 방식으로 I/O를 요청할 때 발생한다.
• Index fast full scan을 제외하고 인덱스를 사용한 Scan 방식은 모두 Single Block 단위로 I/O 한다. 따라서 데이터가 적거나 부분범위처리가 가능할 때 유리하다. 하지만 대량의 데이터를 사용해야 할 때는 Multiblock I/O 방식이 유리하다.
• Single block I/O 방식으로 읽은 블록들은 LRU 리스트 상 MRU 쪽으로 연결되므로 버퍼 캐시에 오래 머물 수 있다. 하지만 Multiblock I/O 방식으로 읽는 블록들은 LRU 쪽에 연결되어 얼마 지나지 않아 버퍼 캐시에서 밀려난다.

4. Prefetch

• Prefetch는 한번에 여러 개 Single Block I/O를 동시에 수행하는 것으로 테이블 Prefetch와 인덱스 Prefetch는 인접하지 않는 블록, 즉 서로 다른 익스텐트에 위치한 블록을 배치 방식으로 미리 적재하는 것을 말한다. 당연하지만 미리 적재했지만 실제 사용하지 못하고 버려진다면 버퍼 캐시 효율만 나빠진다.
• 인덱스 Prefetch는 브랜치 블록에서 앞으로 읽게 될 리프 블록 주소를 미리 얻을 수 있으므로 I/O Call이 필요한 시점에 미리 캐싱해 두는 것이다. Sequentail 액세스 속도를 올리기 위함이며 Index Full Scan을 사용할 때 가장 효과적이다.
• 테이블 Prefetch는 인덱스를 경우해 테이블 레코드를 액세스 하는 도중 디스크에서 캐시로 블록을 적재해야 하는 상황이 발생할 때 다른 테이블 블록까지 미리 적재해 두는 기능이다. Random 액세스 속도를 올리기 위함이며 인덱스 클러스터링 팩터가 나쁠 때 효과적이다.

5. Direct Path I/O

• 일반적인 블록 I/O는 DB 버퍼 캐시를 경유하지만 오라클은 버퍼 캐시를 경유하지 않고 곧바로 데이터 블록을 읽고 쓸 수 있는 Direct Path I/O 기능을 제공한다.
• 데이터를 정렬할 때 PGA 메모리에 할당하여 Sort Area를 이용하는데 데이터가 많아 공간이 부족해지면 Temp 테이블스페이스를 추가적으로 사용한다. 이때 Sort Area와 Temp 테이블스페이스 간에 읽고 쓰는 과정에서 Direct Path I/O 방식을 사용한다.
• 병렬 쿼리로 Full Scan을 수행하면 Direct Path Read 방식을 사용한다.
• 일반적인 Insert 시에는 Freelist를 통해 데이터를 삽입할 버퍼 블록을 찾아 할당한다. 하지만 Direct Path Insert 시에는 Freelist를 참고하지 않고 HWM 바깥 영역에 데이터를 입력하므로 속도는 물론 Undo 발생량도 최소화된다. 또한 Redo 로그까지 최소화하도록 NOLOGGING 옵션을 줄 수 있다.
• insert ... select 문장에 /*+ append */ 힌트를 사용하거나 병렬 모드로 insert, create table ... as select 문장을 수행하면 된다.

6. RAC 캐시 퓨전

• 오라클 RAC 모델은 공유 디스크 방식에 기반을 두면서 인스턴스 간에 버퍼까지 공유하는 캐시 퓨전 기술로 발전했다. 캐시 퓨전 기술을 사용하면 고가용성, 확장성, 부하 분산 측면에서 뛰어나다. 특히 데이터를 하나의 데이터베이스에 통합 모델로 관리함으로써 높은 정합성을 유지할 수 있다는 것이 가장 큰 장점이다. 하지만 튜닝이 잘 되지 않아 많은 블록 I/O를 일으키는 애플리케이션이라면 여러 인스턴스에 놓인 프로세스끼리 하나의 데이터를 동시에 읽고 쓰려는 경합이 심하게 발생해 심각한 성능저하 현상을 겪게 된다.
• 캐시 퓨전 원리는 읽고자 하는 블록이 로컬 캐시에 없을 땐 마스터 노드에 전송 요청을 하고, 마스터 노드는 해당 블록을 캐싱하고 있는 노드에 메시지를 보내 블록을 요청했던 노드에 전송하도록 하는 방식이다. 만약 어느 노드에도 캐싱돼 있지 않다면 직접 디스크에서 읽도록 권한을 부여한다.
• 블록 읽기 요청횟수를 줄여 인터커넥트를 통한 데이터 전송량을 감소 시키는 것이 가장 효과적인 튜닝 방법이다.

7. Result 캐시

• Result 캐시는 Shared Pool에 위치하면서 한번 수행한 쿼리 또는 PL/SQL 함수의 결괏값을 Result 캐시에 저장해 시스템 I/O 발생량을 최소화하는데 도움이 되는 기능이다. DML이 거이 발생하지 않는 테이블을 참조하면서 반복 수행 요청이 많은 쿼리에 사용하면 효과적이다. 작은 결과 집합을 얻으려고 대용량 데이터를 읽어야 할 때나 읽기 전용 코드 테이블을 읽어 코드명칭을 반환하는 함수에 효과적이다.
• /*+ RESULT_CACHE */ 힌트를 사용하면 서버 프로세스는 Result 캐시 메모리를 먼저 찾아보고, 캐싱돼 있다면 그것을 가져다가 결과 집합을 리턴하고 없다면 쿼리를 수행해 결과를 리턴하고 캐시에 저장한다.
• 바인드 변수를 사용하면 바인드 변수 값에 따라 개별적으로 캐싱이 이루어지기 때문에 값의 종류가 다양할 경우 사용을 지양해야한다. 또한 DML이 자주 발생하는 테이블을 참조하는 쿼리나 함수도 시스템 부하를 가중시키기 때문에 지양해야 한다.

8. I/O 효율화 원리

• 최소 블록만 I/O 할 수 있도록 필요한 출력 대상 집합을 확정 짓고 난 후에 조인을 수행하여 작업량을 줄이고 같은 테이블을 여러번 읽어서 결과를 내는 쿼리를 테이블을 한 번만 읽어 결과를 내게 수정해야 한다.
• 전략적인 인덱스 구성은 옵티마이저를 돕는 가장 기본적인 옵티마이징 팩터다. 또한 오라클에서 제공하는 파티션, 클러스터, IOT, MV, FBI, 분석 함수 등을 적극 활용할 수 있다.
• 옵티마이저가 잘못된 판단을 내릴 때는 힌트를 사용해서 실행계획을 변경해 주어야 하며 잘못단 판단을 내리지 않도록 적절한 초기화 파라미터를 설정해 적절한 통계정보를 수집하도록 해야 한다.

부록 1. 라이브러리 캐시 Lock & Pin

• 라이브러리 캐시 Lock은 라이브러리 캐시 오브젝트(LOC)에 대한 핸들을 보호하기 위함이며 Shared 모드, Exclusive 모드, Null 모드가 있다. DDL이나 DML시 Exclusive 모드로 Lock을 설정하고 읽을 때는 Shared 모드로 Lock을 설정한다.
• Null 모드는 Lock을 장기간 유지하려 할 때 사용하며, 커서, 프로시저, 함수, 패키지처럼 실행 가능한 오브젝트에 Null 모드로 Lock을 설정할 수 있다. 스키마 오브젝트가 변경되거나 Drop 되면 그 오브젝트를 참조하는 실행가능 LCO가 Null 모드의 Parse Lock을 해제함으로써 그것을 참조하는 실행가능 LCO를 무효화시킨다.
• 라이브러리 캐시 Pin은 LCO의 실제 내용이 담긴 힙을 보호한다. Pin을 얻어야지 해당 LCO의 실제 내용을 읽고 변경 및 실행할 수 있으며 파싱/컴파일하거나 정보를 새로 로드할 때도 필요하다. 
• LOC 핸들은 영구적인 Fixed Array 영역에 할당되는 반면 LOC 자체는 동적으로 관리되는 힙 영역에 할당되기 때문에 전체 또는 일부가 언제든 유실될 가능성이 있기 때문에 Lock과 Pin을 설정하는 것이다. 따라서 LCO 콘텐츠의 정합성은 Pin을 통해 보장되고, 그것과 별도로 Lock을 설정함으로써 동시성을 높이는 것이다.

부록 2. Cursor_sharing

• cursor_sharing을 force로 설정하면 어떤 값으로 실행하든 항상 같은 실행계획을 사용한다. 기본값인 EXACT일 때보다 라이브러리 캐시 부하는 줄지만 칼럼 히스토그램을 사용하지 못하는 문제 때문에 성능이 더 나빠질 가능성이 있다. 이 단점을 회피하기 위해서 값을 SIMILAR로 변경해 컬럼 히스토그램을 사용하게 할 수도 있다. 하지만 이 모든 방법은 바인드 변수를 제대로 사용하지 않아 임시방편으로 해결하는 방법임으로 바인드 변수를 사용하도록 하자.

부록 3. Deterministic 함수 사용 시 주의사항

• 함수에 동일 입력 값에 대해 동일 출력 값을 보장할 수 없을 때는 사용하면 안 된다.
• 스칼라 서브쿼리에 덧입히더라도 읽기 일관성을 완전히 보장받을 수 없다.

 

- 참고 서적 : 오라클 성능 고도화 원리와 해법 1 -