오라클을 포함한 모든 DBMS에서 I/O는 블록 단위로 이루어진다.
내가 한 레코드(한 줄)나 한 칼럼이 필요하더라도 그게 속한 블록 전체를 읽어서 가져온다.
오라클의 성능을 좌우하는 것은 이 블록을 얼마나 액세스하는 지이다.
그리고 물론 디스크에서 갖고 오는 것보다 버퍼 캐시에서 갖고 오는 것이 더 빠르다.
버퍼 캐시는 DBMS의 메모리에 있다. (SGA 내의 Database Buffer Cache)
디스크에서 읽는 것보다 랜덤 액세스가 가능한 메모리에서 자료를 읽는 것이 더 빠르다.
메모리에 모든 블록을 올릴 수가 없기 때문에,
최대한 버퍼에 있는 블록들을 재활용할 수 있도록 SQL을 잘 짜야 한다.
버퍼 캐시 히트율
내가 찾으려는 블록이 버퍼 캐시에 있는 경우를 버퍼 캐시 히드 (Buffer Cache Hit)라고 하며,
전체 블록 중 이 비율을 버퍼 캐시 히트율(Buffer Cache Hit Ratio, BCHR)라고 한다.
BCHR = (버퍼 캐시에서 찾은 블록 수 / 총 읽은 블록 수) * 100이다.
Call Count CPU Time Elapsed Time Disk Query Current Rows
------ ------- --------- ------------ ------ ---------- ------- -----
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.010 0.006 0 0 0 0
Fetch 2 138.680 1746.630 601458 1351677 0 1
------ ------- --------- ------------- ------ ---------- ------- -----
Total 4 138.690 1746.637 601458 1351677 0 1count = OCI 프로시저가 실행된 횟수
cpu = 실행 CPU 시간
elapsed = 실행 총 시간
disk = 디스크에서 물리적으로 읽은 버퍼 수
query = consistent 읽기에서 읽은 버퍼 수
current = current 읽기 (주로 업데이트 시)에서 읽은 버퍼 수
rows = fetch나 execute call 의 처리 대상이 되는 행 수
전체 읽은 블록 수는 query + current = 1351677이며,
그 중 disk = 601458개를 디스크에서 읽었다.
즉 750219개를 버퍼 캐시에서 읽었고
BCHR는 약 55%이다.
한계
같은 블록을 100번 1000번 읽는다면 BCHR은 100%에 달한다.
하지만 이는 좋은 성능을 보이지 못한다.
버퍼 캐시에서 읽긴 하겠지만 블록을 반복해서 읽으면서 래치를 얻어야 하기 때문이다.
같은 블록을 여러 세션이 동시에 액세스한다면 래치 경합과 버퍼 Lock 경합으로 이어질 수도 있다.
BCHR이 전부는 아니라, 논리적으로 읽는 블록의 수를 줄여야 한다.
Single Block I/O vs. Multiblock I/O
버퍼 캐시가 미스했을 때 해당 블록을 디스크에서 찾은 뒤 캐시에 올려야 한다.
이때 그 한 블록만 올릴 수도 있고,
그 블록이 있는 익스텐트 내의 인접한 블록들도 같이 올릴 수도 있다.
전자를 Single Block I/O,
후자를 Multiblock I/O라고 한다.
둘은 장단점이 있다.
Multiblock I/O는 일단 여러개를 가져오기 때문에
나중에 BCHR를 늘릴 수 있고, I/O Call 횟수가 줄어든다.
다만 인덱스 범위 스캔 (Index Range Scan) 시에는 좋지가 않다.
왜냐면 인덱스의 정렬 순서가 블록의 정렬 순서와 다르기 때문이다.
인덱스에 맞는 블록들은 링크드 리스트로 연결되어 있기 때문에,
익스텐트 내 블록들을 가져온다고 해도 그 블록들은 연관이 없을 수 있다.
만약에 시간값으로 인덱스를 생성한 테이블에서
10월 15일부터 10월 16일까지의 자료를 버퍼 캐시로 올릴 때는
디스크에서 찾은 원본 블록의 주변 블록들을 Multiblock I/O로 옮기는 것보단
원본 블록들 하나하나만을 옮기는 게 낫다.
찾아진 블록의 주변 블록이 10월이랑 멀리 떨어져 있을 수 있기 때문이다.
그렇게 되면 쓸데없는 블록들이 자꾸 캐시로 올라와서
나중에는 LRU 때문에 정말 필요한 블록들이 밀려날 수도 있다.
그래서 인덱스 범위 스캔이나 index full scan (전체를 찾으나 정렬되어 있는 순서로 찾는) 시에는 Single Block I/O가 효율적이다.
다만 정렬 신경 안 쓰고 물리적인 순서에 따라 Index fast full scan 을 하는 경우에는
multiblock I/O를 쓴다.
Sequential vs. Random Access
Sequential Access 는 한 블록에 있는 레코드들을 순차적으로 읽는 것을 말한다.
반면 Random Access는 레코드 하나 읽으려고 블록을 액세스하는 것을 말한다.
내가 찾고 싶은 레코드 100개가 하나의 블록에 모여있으면 Sequential,
100개의 블록에 흩어져 있어서 블록 하나하나를 다 봐야되면 Random Access이다.
즉 sequential access를 늘리고 random access를 줄이는 것이 효율적이다.
또한 Sequential access를 하더라도, 정말 읽어야 되는 것만 읽어야 할 것이다.
이를 선택도를 늘린다고 한다.
Sequential Access의 선택도를 높이면서 Random Access를 줄이는 것이 전략이다.
