블록단위 입출력
모든 DBMS에서 입출력은 블록단위로 이뤄집니다. 하나의 레코드를 읽더라도 레코드가 속한 블록 전체를 읽습니다. 블록 단위 입출력은 버퍼 캐시와 데이터 FILE I/O 모두에 적용됩니다.
- 데이터 파일에서 DB 버퍼 캐시로 블록을 적재할때
- 데이터 파일에서 블록을 직접 읽고 쓸때
- 버퍼 캐시에서 블록을 읽고 쓸때
- 버퍼 캐시에서 변경된 블록을 다시 데이터 파일에 쓸 때
메모리 입출력 vs 디스크 입출력
디스크 입출력은 물리적으로 기록하는 방식으로 속도가 느립니다. 반면 메모리 입출력은 전기적 신호를 이용하기 때문에 속도가 빠릅니다. 모든 DBMS는 읽고자 하는 블록을 먼저 버퍼 캐시에서 찾고 없을 경우 디스크에서 읽어 버퍼 캐시에 적재한 후 사용합니다. 물리적 디스크 입출력을 하기위해 서버 프로세스는 시스탬 입출력을 호출하고 대기 상태가 됩니다. 이때 디스크 입출력의 경합이 심하면 대기 시간도 길어집니다. 디스크 I/O를 최소화하고 버퍼 캐시 히트율을 높이는 것이 DB I/O 튜닝의 목표중 하나입니다.
버퍼캐시히트율
읽어온 블록 중 메모리 버퍼 캐시에서 찾은 블록의 비율을 의미합니다. 즉, 물리적 디스크 읽기를 수반하지 않고 곧바로 메모리에서 블록을 찾은 비율입니다. DIRECT PATH READ 방식 이외의 모든 블록 읽기는 버퍼 캐시를 경유합니다.
-- BCHR = (버퍼 캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수 ) * 100
BCHR은 주로 시스템 전체적인 관점에서 측정하지만, 개별 sql 축면에서 이 비율이 낮으면 성능이 낮음을 의미합니다. 논리적인 블록 요청횟수를 줄이고, 물리적으로 디스크에서 읽어야 할 블록 수를 줄이는 것이 I/O 효율화 튜닝의 핵심 원리입니다. 같은 블록을 반복적으로 엑세스하는 경우 비효율적인 논리적 I/O 가 많이 발생함에도 BCHR은 높아집니다. 이는 버퍼캐시히트율을 성능지표로 사용하는 한계점입니다.
작은 테이블을 반복적으로 엑세스하면 BCHR은 높지만 블록을 찾는 과정에서 lach 경합과 버퍼 LOCK 경합이 발생하면서 메모리 I/O 비용이 디스크 I/O 비용보다 커질 수 있습니다. 따라서 BHCR 보다는 절대적으로 읽어야하는 블록의 수를 줄이는 것이 더 중요합니다.
네트워크,파일시스템 캐시가 I/O 효율에 미치는 영향
대용량 데이터를 읽고 쓰는데 네트워크 기술이 사용되면서 네트워크 속도가 SQL 성능에 큰 영향을 줍니다. SQL을 작성할 때 튜닝을 통해 네트워크 전송량을 줄이려고 노력해야 합니다.
RAC 같은 클러스터링 데이터베이스 환경에선 인스턴스가 캐시된 블록을 공유하므로 메모리 I/O 성능에도 네트워크 속도가 큰 영향을 줍니다. SQL을 처리하는 데이터 베이스는 자체적으로 캐시 영역을 갖고 있으므로 네트워크나 파일 시스템의 캐시버퍼를 사용하는것 보다 DB 버퍼 공간을 크게 할당하는 것이 더 효과 적입니다. 가장 근본적인 해결방법은 논리적 블록 요청 횟수를 최소화 하는 것입니다.
SEQUENTAIL I/O 와 RANDOM I/O
SEQUENTIAL I/O는 레코드간 논리적 혹은 물리적 순서를 따라 차례로 읽어 나가는 방식입니다. 인덱스의 경우 리프블록에 위치한 모든 레코드는 포인터를 따라 논리적으로 연결돼있고 이 포인터를 따라 스캔합니다.
RANDOM I/O는 레코드간 논리적, 물리적 순서를 따르지 않고, 한 건을 읽기 위해 한 블록씩 접근하는 방식입니다. 시퀄셜 방식으로 대상 레코드를 한번에 읽는 다면 비효율이 없지만 레코드 마다 한 블록씩 모두 읽으면 매우 비효율적입니다. I/O 튜닝의 핵심 원리는 다음과 같습니다.
- 시퀀셜 엑세스에 의한 선택비중을 높인다.
- 랜덤 엑세스 발생량을 줄인다.
시퀀셜 엑세스에 의한 비중 높이기
읽어오는 총 건수에서 결과집합으로 선택되는 비중이 높아야합니다. 인덱스만 스캔해 결과를 구할 수 있다면 최적입니다. 단 결과집합의 수에 비해 읽은 인덱스 블록의 수가 많다면 인덱스를 시퀀셜 엑세스 방식으로 스캔할 때도 비효율이 나타날 수 있습니다. 인덱스 구성 칼럼의 순서변경을 통해 인덱스 스캔 범위를 줄일 수 있습니다.
랜덤 엑세스 발생량 줄이기
인덱스 구성 칼럼 변경을 통해 테이블 랜덤 엑세스의 수를 줄일 수 있습니다.
SINGLE BLOCK I/O 와 MULTIBLOCK I/O
SINGLE BLOCK I/O는 한 번의 입출력 콜에 하나의 데이터 블록만을 읽어 메모리에 적재하는 방식입니다. 인덱스를 통해 테이블을 엑세스할 때는 기본적으로 인덱스와 테이블 모두 이 방식을 사용합니다.
MULTIBLOCK I/O는 입출력 콜이 필요한 시점에, 인접한 블록을 같이 읽어 메모리에 적재하는 방식입니다. TABLE FULL SCAN 처럼 물리적으로 저장된 순서에 따라 읽을 때는 인접한 블록을 같이 읽는 것이 유리합니다. 여기서 인접한 블록이란 하나의 익스텐트에 속한 블록을 말합니다. 익스텐트 범위를 넘어서 까지 읽지는 않으므로 익스텐트 단위가 클 수록 I/O 횟수가 줄어듭니다.
인덱스 스캔 시에는 SINGLE BLOCK I/O 방식이 효율적입니다. 인덱스 블록의 논리적 순서는 데이터 파일이 저장된 물리적인 순서와 다르기 때문입니다. 물리적으로 한 익스텐트에 속하는 인덱스 블록을 메모리에 올려도 인덱스의 논리적 순서와 다르기 때문에 사용하지 않을 블록이 버퍼상에 위치하게 되고 이는 버퍼캐시 활용의 효율을 저해합니다.
입출력 효율화 원리
논리적 I/O 요청횟수를 최소화 하는 것이 튜닝의 핵심입니다.
필요한 최소 블록만 읽도록 SQL 작성
SELECT
, SUM( CASE WHEN T_DATE = TO_CHAR(SYSDATE -1 , 'YYYYDDMM') THEN T_AMOUNT END )
, SUM( CASE WHEN T_DATE BETWEEN TO_CHAR(SYSDATE -7 , 'YYYYMMDD') AND TO_CHAR(SYSDATE -1 , 'YYYYMMDD') THEN T_AMOUNT END )
, SUM( CASE WHEN BETWEEN TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMMDD') || '01' AND TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)), 'YYYYMMDD') END)
FROM T_LIST
WHERE T_DATE BETWEEN TO_CHAR(ADD_MONTHS(SYSDATE, 10), 'YYYYMMDD') || '01') AND TO_CHAR(LAST_DAT(SYSDATE, -1),'YYYYMMDD')
GROUP BY CARD_NUM최적의 옵티마이징 팩터 제공
옵티마이저가 블록 엑세스를 최소화하면서 효율적으로처리할 수 있도록 하려면 최적의 옵티마이징 펙터를 재공해야합니다. DBNS가 제공하는 기능을 활용해 최적의 옵티마이징팩터를 제공할 수 있습니다.
- 파티션 클러스터
- 윈도우 함수
- 옵티마이저 모드 설정
- 부분범위처리
옵티마이저 힌트를 사용해 최적의 엑세스 경로 유도
옵티마이저 힌트를 사용할 때는 의도한 실행계획으로 수행되는지 반드시 확인해야 합니다.