05.데이터베이스 Call 최소화 원리 - 05. Fetch Call 최소화

(1)부분범위처리 원리
공사장에서 시멘트를 이용해 벽돌을 쌓는 동안 운반공이 벽돌을 실어 나르는 상황을 상상해봅니다.
벽돌은 많이 있어서 한번에 다 옮길 수 없으며 한 수레(array size)씩 운반해야 합니다. 운반공은 미장공이 벽돌을 더 가져오라는 요청(Fetch Call)이 있을때만 벽돌을 운반합니다. 추가요청이 없으면 더 이상 운반하지 않습니다.
DBMS도 이와 마찬가지로 데이터를 클라이언트에게 전송할 때 일정량씩 나누어 전송하며, 오라클의 경우 Array Size(또는 Fetch Size)설정을 통해 운반단위를 조절합니다. 전체 결과집합 중 아직 전송하지 않은 분량이 많이 남아있어도 클라이언트로부터 추가 Fetch Call을 받기 전까지는 그대로 멈춰 서서 기다립니다. 이게 OLTP성 환경에서 대용량 데이터를 빠르게 핸들링할 수 있는 중요한 원리입니다.
쿼리결과집합을 전송할 때 전체데이터를 쉼 없이 연속적으로 처리하지 않고 사용자로부터 Fetch Call이 있을때마다 일정량씩 나누어서 전송하는 것을 이른바 ‘부분범위처리’라고 합니다.
[실제로 제가 Orange프로그램을 이용해서 select 결과를 리턴 받을때 row수가 많을때에도 금방 결과값을 볼 수 있는데 그것은 orange에서 기본적으로 설정된 array size 때문에 다 보여주지 않고 일부분만 보여주고, 제가 그 값의 끝으로 스크롤을 내리면, 그때 그다음 select 값을 다시 보여줍니다.]
적정한 데이터 운반단위는 시스템의 성격에 따라 달라지며 적정하게 정하는 것이 좋습니다.
지금까지 설명한 부분범위처리 원리를 이해했다면, 네트워크를 통해 전송해야 할 데이터양에 따라 Arraysize를 조절할 필요가 있습니다. 예를 들어, 대량 데이터를 파일로 내려받는다면 어차피 전체 데이터를 전송해야 하므로 가급적 그 값을 크게 설정해야 합니다. Arraysize를 조정한다고 해서 전송해야 할 총량이 변하지는 않지만, Fetch Call횟수를 그만큼 줄일 수 있습니다. 반대로 앞쪽 일부 데이터만 Fetch하다가 멈추는 프로그램이라면 Arraysize를 작게 설정하는 것이 유리합니다. 불필요하게 많은 데이터를 전송하고 버리는 비효율을 줄일 수 있기 때문입니다.
Arraysize를 5로 설정하면, 서버 측에서는 Oracle Net으로 데이터를 내려 보내다가 5건당 한 번씩 전송 명령을 날리고는 클라이언트로부터 다시 Fetch Call이 올 때까지 대기합니다. 클라이언트 측에서는 서버로부터 전송받은 5개 레코드를 담을 Array버퍼가 할당되며, 그곳에 서버로부터 받은 데이터를 담았다가 한 건씩 꺼내 화면에 출력하거나 다르 작업들을 수행합니다.

(2)OLTP환경에서 부분범위처리에 의한 성능개선 원리
OLTP환경에서는 결과집합이 많을수록 오히려 성능이 좋아집니다. 클라이언트의 Array 버퍼가 비지 않고 바로바로 차면 화면에 뿌려줄 수 있기 때문입니다. Array 버퍼에 못미치는 값을 받을때에는 클라이언트가 (Arraysize가 5이지만 결과값이 1 row일 경우) 5가 가득 찰때까지 기다렸다가 서버가 테이블 액세스를 끝내고 1건 밖에 없다는 사실을 다시 Oracle Net을 통해 클라이언트에 전송하고 나서 화면에 뿌려지기 떄문입니다.
OLTP성 업무에서는 쿼리 결과 집합이 아주 많더라도 그 중 일부만 Fetch하고 멈출 때가 있습니다. 따라서 출력 대상 건이 많을수록 Array를 빨리 채울 수 있어 쿼리 응답 속도도 그만큼 빨라집니다. 인덱스와 부분범위처리 원리를 잘 활용하면 성능개선 효과를 볼 수 있는 것입니다.

(3)Arraysize 조정에 의한 Fetch Call 감소 및 블록 I/O감소 효과
대량 데이터를 내려받을 때 Arraysize를 크게 설정할수록 그만큼 Fetch Call횟수가 줄어 네트워크 부하가 감소하고 쿼리 성능이 향상됩니다. 그뿐 아니라 서버 프로세스가 읽어야 할 블록 개수까지 줄어드는 효과까지 받습니다. 테스트를 통해 확인해보겠습니다.

Arraysize가 2인 경우에 49839개의 로우를 가져오려고 읽은 블록 개수는 25336개입니다. 그리고 Fetch 횟수는 24921이므로 2개 로우씩을 한번의 Fetch마다 읽었다고 볼 수 있습니다.
SQL트레이스에서도 같은 것을 확인할 수 있습니다.

 

이 테스트를 arraysize를 5,10,100,1000,5000으로 늘려서 아래표에 Fetch횟수와 Block I/O로 비교하였습니다.

Arraysize를 키운다고 해서 같은 비율로 Fetch Count와 Block I/O가 줄지는 않지만 반비례관계임을 확인할 수 있습니다. 따라서 무작정 크게 하기 보단 적절한 크기로 하는게 성능 상 좋습니다.

위의 그림을 보면 10개의 행(작은 네모)으로 구성된 3개의 블록이 있습니다. 총 30개의 행이므로 arraysize를 3으로 설정하면 Fetch 횟수는 10회이고, 이때 Block I/O는 12회 발생합니다. 왜냐하면 10개 레코드가 담긴 블록들을 각각 4번에 걸쳐 반복 액세스해야 하기 떄문입니다. 그림에서 보면 1번블록이 4번 읽히게 됩니다. 그렇게되서 전체적으로 Block I/O가 12번 일어나게 됩니다.

(4)프로그램 언어에서 Array 단위 Fetch 기능 활용
JAVA에서 Fetch size 기본 값은 10입니다. 대량 데이터를 Fetch 할 때 이 값을 100~500 정도로 늘려주면 기본 값을 사용할 때보다 데이터베이스 Call 부하를 1/10~1/50으로 줄일 수 있습니다.