인덱스 튜닝(2) - 부분범위 처리 활용

 

테이블 랜덤 엑세스로 인한 인덱스 손익분기점의 한계를 극복할 히든카드인 부분범위 처리를 활용해보자

 

 

1. 부분범위 

 

친절한 SQL 튜닝

 

DBMS가 클라이언트에게 데이터를 전송할 때도 일정량씩 나누어 전송한다. 서버 프로세스는 클라이언트로부터 추가 Fetch Call을 받기 전까지 그대로 멈춰 서서 기다린다. 

데이터를 전송하고 나면 서버 프로세스는 CPU를 OS에 반환하고 대기 큐에서 잠을 잔다. 

이처럼 전체 쿼리 결과집합을 쉼 없이 연속적으로 전송하지 않고 사용자로부터 Fetch Call이 있을 때마다 일정량씩 나누어 전송하는 것을 부분범위 처리라고 한다. 

 

*데이터 전송단위인 Array Size는 클라이언트 프로그램에서 설정함 -> JAVA에서는 기본값이 10이며, Statement 객체 setFetchSize 메소드를 통해 설정을 변경할 수 있다. 

-> 최초 rs.next()에서 10건을 캐시에 저장 -> 이후 호출 시 Fetch Call을 발생시키지 않고 캐시 데이터 읽음 

 

1.1 정렬조건이 있을 때 부분범위 처리 

 

만약 쿼리문에 orderby를 추가한다면, DB서버는 모든 데이터를 다 읽어 정렬을 마치고서 클라이언트에게 데이터를 전송할 수 있다. 이는 전체범위처리다. Sort Area와 Temp 테이블스페이스까지 이용해서 정렬하고, 일정량 나눠 전송한다.

*이때 정렬조건이 인덱스 선두컬럼이라면, order by를 생략

 

1.2 Array Size 조정을 통한 Fetch Call 최소화

 

부분범위 처리 원리가 있으니, 전송해야 할 데이터량에 따라 Array Size를 조절할 필요가 있다.

대량 데이터를 파일로 내려받는다면, 가급적 값을 크게 설정한다. 반대로 앞쪽 일부 데이터만 Fetch하다 멈추는 프로그램이라면 Array Size를 작게 설정하는 것이 유리하다. 

 

1.3 부분범위 처리 구현 

 

public static int fetch(Result rs, in arraysize) throws Exception{

	int i=0;
    while(rs.next()){
    	//..결과 출력
        if(++i >= arraysize) return i;
    }
}


//..생략

while(true){

	int r = fetch(rs,arraysize);
    if(r<arraysize) break;
    //..생략

}

 

자바에서 부분범위처리를 구현하면 대략 위와 같다. rs.next()로 한번에 읽어온 출력 수가  arraysize과 같거나 많다면, 

출력을 중단하고, 계속 출력할지 여부를 결정한다. 

이때 rs.next()로 읽은 총 출력 수가 arraysize보다 작으면, 더 읽을 row가 없음으로 break한다.

 

 

---

2. OLTP 환경에서 부분범위 처리에 의한 성능개선 원리

 

OLTP는 Online Transaction Processing을 의미한다. 온라인 트랜잭션 처리 시스템을 말한다.

소량의 데이터를 읽고 갱신한다. 하지만 수천수만 건을 조회하는 경우도 있다.

다만, 업무 특성상 모든 데이터를 읽지 않고, 특정 몇개 상위 데이터만 확인하는 경우가 많다.

이때, 항상 정렬 상태를 유지하는 인덱스를 이용하면, 정렬 작업을 생략하고, 앞쪽 일부 데이터를 빠르게 보여줄 수 있다.

 

인덱스와 부분범위 처리 원리를 잘 활용하면 OLTP 환경에서 극적인 성능개선 효과를 얻을 수 있는 원리가 여기 숨어있다.

SELECT 게시글 ID, 제목, 작성자, 등록일시
FROM 게시판
WHERE 게시판구분코드 = 'A'
ORDER BY 등록일시 desc

 

인덱스 선두 컬럼을 게시판구분코드  + 등록일시 순으로 구성하지 않으면 소트 연산을 생략할 수 없다.

 

게시판구분코드와 등록일시로 인덱스를 구성하면, 게시판구분코드와 등록일시 순으로 정렬되므로, sort연산을 생략할 수 있다. 

 

(2.1) 멈출 수 있어야 의미있는 부분범위 처리 

 

부분범위 처리의 핵심은 앞쪽 일부만 출력하고 멈출 수 있는가이다. 

WAS,AP 서버등이 조재하는 n-Tier 아키텍처에서는 클라이언트가 특정 DB 커넥션을 독점할 수 없다.

단위 작업을 마치면 DB 커넥션을 곧바로 반환해야한다. 즉 SQL 조회결과를 모두 전송하고 Cursor를 닫아야 한다.

 

n-Tier 환경에서 부분범위 처리는 추후 알아보자

---

 

3. 배치 I/O

 

오라클의 배치 I/O 기능은 읽는 블록마다 건건이 I/O Call을 발생시키는 것이 아니라, 버퍼 캐시에서 블록을 찾기 못하면 읽을 블록이 일정량 쌓이면 한꺼번에 처리한다. 11g에서 NL 조인 Inner 테이블 엑세스 때만 작동했지만 12c부터는 인덱스 ROWID로 테이블을 엑세스하는 어떤 부분에서든 기능이 작동할 수 있다.

 

3.1 데이터 정렬 이슈 

 

배치 I/O 기능이 작동하면 인덱스를 이용해 출력하는 데이터 정렬 순서가 매번 다를 수 있다. 

(버퍼에서 찾으면 순서가 맞지만, 그렇지 않으면 다를 수 있음) 

 

select /*+ batch_table_access_by_rowid(e) */ *
from emp e
where deptno = 20
order by job, empno;

친절한 SQL 튜닝

위와 같이 테이블 엑세스 단계 뒤쪽에 BATCHED가 추가되고, SORT ORDER BY가 실시된다. (소트 연산이 생략 가능한데도 소트 연산을 했다)

 

애초에 인덱스로 소트 연산을 생략할 수 없거나, ORDER BY가 없으면, BATCHE 연산을 실행한다. 

 

*index를 활용한 소트연산 생략으로, SQL 자체에서 order by를 생략하는 패턴은 12c부터는 좋지 못할 수 있다.

order by가 없으면 BATCHED를 실행하므로, 때에 따라 정렬 순서가 보장되지 않을 수 있기 때문이다.