데이터베이스 성능 튜닝의 3대 핵심 요소는
- 라이브러리 캐시 최적화
- 데이터베이스 Call 최적화
- I/O 효율화 및 버퍼캐시 최적화
가장 중요한 것은 세번째다. 잘 적용하려면 장기간 훈련이 필요하다.
I/O 효율화 튜닝을 잘하려면 인덱스원리, 조인원리, 옵티마이저원리에 대한 이해가 필수적이다.
그리고 이를 바탕으로 고급SQL 활용을 통해 문제 해결 경험을 많이 쌓아야만 한다.
01 블록단위 I/O
블록단위로 I/O 한다는 것은, 하나의 레코드에서 하나의 컬럼만을 읽으려 할 때도 레코드가 속한 블록 전체를 읽게 됨을 뜻한다.
Sequential 액세스 하나의 블록을 액세스해 그 안에 저장돼 있는 모든 레코드를 순차적으로 읽어들인다면 설령 무거운 디스크 I/O 를 수반하더라도 비효율은 없다.
Random 액세스 레코드 하나를 읽으려고 블록을 통째로 액세스한다면 그것은 메모리 버퍼에서 읽더라도 비효율이 존재한다.
SQL 성능을 좌우하는 가장 중요한 성능지표는 액세스하는 블록 개수이며,
옵티마이저의 판단에 가장 큰 영향을 미치는 것도 액세스해야할 블록 개수다.
블록단위 I/O 는 버퍼 캐시와 데이터파일 I/O 모두에 적용된다.
- 메모리 버퍼 캐시에서 블록을 읽고 쓸 때
- 데이터파일에 저장된 데이터 블록을 직접 읽거나 쓸 때
- 데이터파일에서 DB 버퍼 캐시로 블록을 적재할 때: Single Block Read 또는 Multiblock Read 방식을 사용
- 버퍼캐시에서 변경된 블록을 다시 데이터파일에 저장할 때:
Dirty 버퍼를 주기적으로 데이터파일에 기록하는 것을 말하며, DBWR 프로세스에 의해 수행된다.
성능 향상을 위해 한번에 여러 블록씩 처리한다.
(1) Sequential vs. Random 액세스
총 읽은 레코드에서 결과 집합으로 선택되는 비중을 선택도 (Selectivity) 라고 한다
같은 결과 건수를 내면서 얼마나 적은 레코드를 읽느냐로 효율성을 판단할 수 있다.
* I/O 튜닝의 핵심 원리
- Sequential 액세스의 선택도를 높인다.
- Random 액세스 발생량을 줄인다.
(2) Sequential 액세스 선택도 높이기
(3) Random 액세스 발생량 줄이기
02 Memory vs. Disk I/O
(1) I/O 효율화 튜닝의 중요성
디스크를 경유한 입출력은 물리적으로 액세스 암 (arm) 이 움직이면서 헤드를 통해 데이터를 읽고 쓰기 때문에 느리다.
반면, 메모리를 통한 입출력은 전기적 신호에 불과하기 때문에 디스크 I/O 에 비해 비교할 수 없을 정도로 빠르다.
디스크 I/O 를 최소화하고, 대부분 처리를 메모리에서 할 수 있도록 버퍼 캐시 효율성을 높이는 것이 데이터베이스 성능을 좌우하는 열쇠
(2) 버퍼 캐시 히트율 (Buffer Cache Hit Ratio)
버퍼 캐시 효율을 측정하는 지표로서 전통적으로 가장 많이 사용돼 온 것은 버퍼 캐시 히트율 (BCHR) 이다.
논리적 블록읽기 = 총 읽은 블록 수
캐시에서 곧바로 찾은 블록 수 = 논리적 블록읽기 - 물리적 블록읽기
SQL 을 수행하는 동안 캐시에서 읽은 총 블록 수를 '논리적 블록 읽기'
'캐시에서 곧바로 찾은 블록 수' 는 디스크를 경유하지 않고 버퍼 캐시에서 찾은 블록 수를 말하므로
'총 읽은 블록수' 에서 '물리적 블록읽기' 를 차감해서 구한다.
(3) 네트워크, 파일시스템 캐시가 I/O 효율에 미치는 영향
디스크 속도가 문제이든, SAN 이 문제이든, 아니면 RAC 인터커넥트가 문제이든 I/O 성능에 관한 가장 확실하고 근본적인 해결책은
논리적인 블록 요청 횟수를 최소화하는 것임을 잊지 말자.
방법은, SQL 튜닝에 있다.
03 Single Block vs. Multiblock I/O
Single Block I/O 는 한번의 I/O Call 에 하나의 데이터 블록만 읽어 메모리에 적재하는 것
Muitiblock I/O 는 I/O Call 이 필요한 시점에 인접한 블록들을 같이 읽어 메모리에 적재하는 것
단위는 db_file_multiblock_read_count 파라미터에 의해 결정된다.
인덱스 블록 논리적 순서란 인덱스 리프 블록끼리 이중 연결 리스트 구조로 연결된 순서
db file sequential read 대기 이벤트 : Single Block I/O 방식으로 I/O를 요청할 때 발생
db file scattered read 대기 이벤트 : Multiblock I/O 방식으로 I/O를 요청할 떄 발생
04 Prefetch
테이블 Prefetch 와 인덱스 Prefetch 를 지칭
오라클을 포함한 모든 DBMS 는 디스크 블록을 읽을 때 곧이어 읽을 가능성이 높은 블록을 미리 읽어오는 Prefetch 기능을 제공한다.
디스크 I/O가 비용이 크기 때문에 한번의 I/O Call 을 통해 다량의 블록을 퍼올릴 수 있다면 그만큼 성능향상에 도움이 되기 때문이다.
Prefetch는 한번에 여러개 Single Block I/O 를 동시 수행하는 것을 말한다.
(1) 인덱스 Prefetch
브랜치 블록에서 앞으로 읽게 될 리프 블록 주소를 미리 얻을 수 있으므로 I/O Call이 필요한 시점에 미리 캐싱해 두는 것이 가능하다.
(2) 테이블 Prefetch
인덱스를 경유해 테이블 레코드를 액세스하는 도중 디스크에서 캐시로 블록을 적재해야 하는 상황이 발생할 수 있는데,
그때 다른 테이블 블록까지 미리 적재해 두는 기능
버퍼 Pinning은 Random 액세스에 의한 논리적 블록 요청 횟수를 감소시키고,
테이블 Prefetch 는 디스크 I/O에 의한 대기 횟수를 감소시킨다.
05 Direct Path I/O
시스템 전반의 I/O 성능을 향상시키려고 버퍼 캐시를 이용하지만 개별 프로세스 입장에서 대용량 데이터를 읽고 쓸 때
건건이 버퍼 캐시를 경유한다면 오히려 성능이 나빠질 수 있다.
재사용 가능성이 없는 임시 세그먼트 블록들을 읽고 쓸 때도 버퍼캐시를 경유하지 않는 것이 유리하다.
오라클은 이럴 때 곧바로 데이터 블록을 읽고 쓸 수 있는 Direct Path I/O 기능을 제공한다.
Direct Path I/O 가 작동하는 경우
- Temp 세그먼트 블록들을 읽고 쓸 때
- 병렬 쿼리로 Full Scan 을 수행할 때
- nocache 옵션을 지정한 LOB 컬럼을 읽을 때
- direct 옵션을 지정하고 export 를 수행할 때
- parallel DML 을 수행할 때
- Direct Path Insert 를 수행할 때
(1) Direct Path Read/Write Temp
Sort Area 에 정렬된 데이터를 Temp 테이블스페이스에 쓰고 이를 다시 읽을 때, Direct Path I/O 방식을 사용한다.
(2) Direct Path Read
병렬 쿼리로 Full Scan을 수행할 때도 Direct Path Read 방식을 사용한다.
8.1.5 이후 히든 파라미터 _serial_direct_read 를 true 로 변경해 Direct Path Read 방식으로 읽게 할 수 있다.
11g 부터는 false 라도 Serial Direct Path Read 방식이 작동할 수 있으며,
이미 캐싱돼 있는 블록 개수, 디스크에 기록해야할 Dirty 블록 개수 등에 따라 오라클이 결정한다.
(3) Direct Path Write
병렬로 DML 을 수행하거나 Direct Path Insert 방식으로 데이터를 insert 할 때 사용된다.
아래는 Direct Path Insert 방식으로 데이터를 입력하는 방법이다.
- insert select 문장에 /*+append*/ 힌트 사용
- 병렬 모드로 insert
- direct 옵션을 지정하고 SQL*Loader 로 데이터를 로드
- CTAS 문장을 수행
Direct Path Insert 에서는 Redo 로그까지 최소화하도록 옵션을 줄 수도 있다.
alter table t nologging;
Direct Path Insert 방식으로 데이터를 입력하면 Exclusive 모드 테이블 Lock 이 걸린다.
트랜잭션이 빈번한 주간에 이 옵션을 사용하는 것은 절대 금물이다.
06 RAC 캐시 퓨전
1. 데이터베이스 서버 간 복제
2. 업무별 수직 분할
3. 데이터 구분에 따른 수평 분할
캐시퓨전원리: 읽고자 하는 블록이 로컬 캐시에 없을 때 마스터 노드에 전송 요청을 하고,
마스터 노드는 해당 블록을 캐싱하고 있는 노드에 메시지를 보내 그 블록을 요청했던 노드에 전송하도록 지시하는 방식
Current 블록은 디스크로부터 읽혀진 후 사용자의 갱신사항이 반영된 최종 상태의 원본 블록을 말하며,
CR 블록은 Current 블록에 대한 복사본이다. CR블록은 여러 버전이 존재할 수 있지만 Current 블록은 오직 한 개뿐이다.
RAC 환경에서의 Current 블록은 Shared 모드 Current 와 Exclusive 모드 Current 로 나뉜다.
SCur 상태의 블록은 동시에 여러 노드에 캐싱될 수 있지만
XCur 상태의 블록은 단 하나의 노드에만 존재할 수 있다.
(1) 전송 없는 읽기: Read with No Transfer
(2) 읽기/읽기 전송: Read to Read Transfer
(3) 읽기/쓰기 전송: Read to Write Transfer
(4) 쓰기/쓰기 전송: Write to Write Transfer
(5) 쓰기/읽기 전송: Write to Read Transfer
캐시 퓨전 과정에서 생기는 성능 부하는 I/O 관련 부하와 같은 시각에서 바라봐야 한다.
블록에 대한 읽기 요청 횟수가 많으면 디스크 I/O 관련 대기 이벤트가 증가하는 만큼 RAC 관련련 이벤트도 같이 증가하는 것을 관찰할 수 있다.
블록읽기 요청횟수를 줄여 인터커넥트를 통한 데이터 전송량을 감소시키는 것이 가장 확실하고 근본적이 해결책이며,
이는 SQL 튜닝을 통해 달성할 수 있다.
07 Result 캐시
버퍼 캐시 히트율이 낮을 수 밖에 없는 대용량 데이터 쿼리라면 더더욱 I/O 효율화를 위한 튜닝에 곤란을 겪게 되는데,
집계 테이블을 다로 설계하거나 M-View 를 생성하는 것 외에 별다른 I/O효율화 방안이 없는 경우가 종종 있다.
11g 부터 한번 수행한 쿼리 또는 PL/SQL 함수의 결과값을 Result 캐시에 저장해두는 기능을 제공한다.
1) DML이 거의 발생하지 않는 테이블을 참조하면서
2) 반복 수행요청이 많은 쿼리에 이 기능을 사용하면 I/O 발생량을 현격히 감소시킬 수 있다.
Result 캐시 메모리 영역
- SQL Query Result 캐시: SQL 쿼리 결과를 저장
- PL/SQL 함수 Result 캐시: PL/SQL 함수 결과값을 저장
Result 캐시는 DW 뿐 아니라 OLTP 환경에서도 잘 활용하면 반복적인 I/O 요청횟수를 줄이는 데에 기여할 것이다.
이 기능이 효과가 있으려면 기본적으로 쿼리 사용빈도가 높아야 하며, 더불어 아래와 같은 상황에서 효과가 배가될 것이다.
- 작은 결과 집합을 얻으려고 대용량 데이터를 읽어야 할 때
- 읽기 전용의 작은 테이블을 반복적으로 읽어야 할 때
- 읽기 전용 코드 테이블을 읽어 코드 명칭을 반환하는 함수
아래 경우는 Result 캐시 기능을 자제
- 쿼리가 참조하는 테이블에 DML이 자주 발생할 때
- 함수 또는 바인드 변수를 가진 쿼리에서 입력되는 값으 종류가 많고, 그 값들이 골고루 입력될 때
08 I/O 효율화 원리
애플리캐이션 측면에서의 I/O 효율화 원리
- 필요한 최소 블록만 읽도록 쿼리를 작성한다.
- 최적의 옵티마이징 팩터를 제공한다.
- 필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도한다.
(1) 필요한 최소 블록만 읽도록 쿼리 작성
(2) 최적의 옵티마이징 팩터 제공
- 전략적인 인덱스 구성
- DBMS 가 제공하는 다양한 기능 활용 (파티션, 클러스터 IOT, MV, FBI, 분석 함수 등)
- 옵티마이저 모드 설정
- 통계정보의 중요성 (오브젝트 통계 수집 전에 시스템 통계도 수집돼 있어야 한다)
1. 옵티마이저 모드를 포함해 적절한 초기화 파라미터를 설정해주고, 적절한 통계정보를 수집해 준다.
2. 전략적인 인덱스 구성이 필수적
3. 기타 다양한 DBMS 기능을 적극 활용해 옵티마이저가 최적의 선택을 할 수 있도록 수단을 제공해주어야 한다.
(3) 필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도