데이터 모델링의 3단계 진행
블록 단위 I/O
Oracle을 포함한 모든 DBMS에서 I/O는 블록 단위로 이루어진다.
메모리 I/O vs 디스크 I/O
디스크 I/O : 디스크의 액세스 암이 움직이면서 헤드를 통해 데이터를 읽고 쓴다.
메모리 I/O : 전기적 신호
메모리는 물리적으로 한정된 자원이므로, 결국 디스크 I/O를 최소화하고 버퍼 캐시 효율을 높이는 것이 데이터베이스 I/O 튜닝의 목표가 된다.
버퍼 캐시 히트율
버퍼 캐시 히트율(BCHR)
: 물리적인 디스크 읽기를 수반하지 않고 곧바로 메모리에서 블록을 찾은 비율.
이 비율이 낮은 것이 SQL 성능을 떨어뜨리는 주원인이라고 할 수 있다.
= (버퍼캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수) * 100
= ((논리적 I/O - 물리적 I/O) / 논리적 I/O) * 100
= (1 - (물리적 I/O) / (논리적 I/O)) * 100
논리적 I/O는 데이터의 변경이 없다면 같은 SQL을 몇 번 수행해도 같지만 물리적 I/O는 같은 SQL을 여러 번 수행할수록 줄어든다. 실행할수록 DB 버퍼 캐시에서 해당 테이블 블록의 점유율이 높아지기 때문이다.
(SQL 처리 도중 읽어야 할 블록을 버퍼캐시에서 찾지 못할 때만 디스크를 액세스하므로 논리적 블록 I/O 중 일부를 물리적으로 I/O한다.)
물리적 I/O가 성능을 결정하지만, 실제 SQL 성능을 향상하려면 물리적 I/O가 아닌 논리적 I/O를 줄여야 한다.
Sequential I/O vs Random I/O
Sequential 액세스 : 레코드간 논리적 또는 물리적인 순서를 따라 차례대로 읽어나가는 방식
Random 액세스 : 레코드간 논리적, 물리적인 순서를 따르지 않고, 한 건을 읽기 위해 한 블록씩 접근하는 방식
I/O 튜닝의 핵심 원리 - Sequential 액세스에 의한 선택 비중을 높이고 Random 액세스 발생량을 줄인다.
Sequential 액세스에 의한 선택 비중 높이기
Sequential 액세스 효율성을 높이려면, 읽은 총 건수 중에서 결과집합으로 선택되는 비중을 높여야 한다. 즉, 같은 결과를 얻기 위해 얼마나 적은 레코드를 읽느냐로 효율성을 판단할 수 있다.
테이블을 스캔하면서 읽은 레코드 중 대부분 필터링되고 일부만 선택된다면 인덱스를 사용하는 것이 효과적이다.
Random 액세스 발생량 줄이기
인덱스에 속하지 않은 컬럼을 조건절로 두거나 Select하게 되면 Table Access가 발생한다. 인덱스를 조정함으로써 이러한 랜덤 액세스 횟수를 줄일 수 있다.
Single Block I/O vs MultiBlock I/O
캐시에서 찾지 못한 데이터 블록은 I/O Call을 통해 디스크에서 DB 버퍼캐시로 적재하고서 읽는다. I/O Call할 때, 한 번에 한 블록씩 요청하기도 하고, 여러 블록씩 요청하기도 한다.
Single Block I/O : 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식. 인덱스를 통해 테이블을 액세스할 때, 인덱스와 테이블 블록 모두 이 방식을 사용한다.
- 인덱스 루트 블록 읽을 때
- 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
- 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
- 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
Multiblock I/O : 한 번에 여러 블록(인접한 블록. 한 익스텐트 내에 속한 블록)씩 요청해서 메모리에 적재하는 방식. 테이블 전체를 스캔할 때 사용한다.
Single Block I/O 방식으로 읽은 블록들은 LRU 리스트 상 MRU 쪽으로 위치하므로 한 번 적재되면 버퍼 캐시에 비교적 오래 머무는 반면, MultiBlock I/O 방식으로 읽은 블록들은 LRU 리스트 상 LRU쪽으로 연결되므로 적재된지 얼마 지나지 않아 1순위로 버퍼캐시에서 밀려나게 된다.
I/O 효율화 원리
논리적인 I/O 요청 횟수를 최소화하는 것이 I/O 효율화 튜닝의 핵심 원리다.
- 필요한 최소 블록만 읽도록 SQL 작성
- 최적의 옵티마이징 팩터 제공
- 필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도
필요한 최소 블록만 읽도록 SQL 작성
select a.카드번호
, a.거래금액 전일_거래금액
, b.거래금액 주간_거래금액
, c.거래금액 전월_거래금액
, d.거래금액 연중_거래금액
from ( -- 전일거래실적
select 카드번호, 거래금액
from 일별카드거래내역
where 거래일자 = to_char(sysdate-1,'yyyymmdd')
) a
, ( -- 전주거래실적
select 카드번호, sum(거래금액) 거래금액
from 일별카드거래내역
where 거래일자 between to_char(sysdate-7,'yyyymmdd') and to_char(sysdate-1,'yyyymmdd')
group by 카드번호
) b
, ( -- 전월거래실적
select 카드번호, sum(거래금액) 거래금액
from 일별카드거래내역
where 거래일자 between to_char(add_months(sysdate,-1),'yyyymm')
|| '01' and to_char(last_day(add_months(sysdate,-1)),'yyyymmdd')
group by 카드번호
) c
, ( -- 연중거래실적
select 카드번호, sum(거래금액) 거래금액
from 일별카드거래내역
where 거래일자 between to_char(add_months(sysdate,-12),'yyyymmdd')
and to_char(sysdate-1,'yyyymmdd')
group by 카드번호
) d
where b.카드번호 (+) = a.카드번호
and c.카드번호 (+) = a.카드번호
and d.카드번호 (+) = a.카드번호 현재 SQL문을 보았을 때 '일별카드거래내역'이라는 테이블에 반복적으로 액세스했는데...
select 카드번호
, sum( case when 거래일자 = to_char(sysdate-1,'yyyymmdd')
then 거래금액
end ) 전일_거래금액
, sum( case when 거래일자 between to_char(sysdate-7,'yyyymmdd') and to_char(sysdate-1,'yyyymmdd')
then 거래금액
end ) 주간_거래금액
, sum( case when 거래일자 between to_char(add_months(sysdate,-1),'yyyymm')
|| '01' and to_char(last_day(add_months(sysdate,-1)),'yyyymmdd')
then 거래금액
end ) 전월_거래금액
, sum( 거래금액 )연중_거래금액
from 일별카드거래내역
where 거래일자 between to_char(add_months(sysdate,-12),'yyyymmdd')
and to_char(sysdate-1,'yyyymmdd')
group by 카드번호
having sum( case when 거래일자 = to_char(sysdate-1,'yyyymmdd') then 거래금액 end ) > 0 다음과 같이 작성하면 논리적인 집합 재구성을 통해 액세스해야 할 데이터 양을 최소화할 수 있다.
최적의 옵티마이징 팩터 제공
1) 전략적인 인덱스 구성
2) DBMS가 제공하는 기능 활용(인덱스, 파티션, 클러스터, 윈도우 함수 등)
3) 옵티마이저 모드(전체 처리속도 최적화, 최초 응답속도 최적화) 설정
4) 통계정보
필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도
최적의 옵티마이징 팩터를 제공했다면 가급적 옵티마이저 판단에 맡기는 것이 바람직하지만 옵티마이저가 생각만큼 최적의 실행계획을 수립하지 못하는 경우 어쩔 수 없이 힌트를 사용한다.
