SQL 튜닝이론 - 부분범위처리를 활용한 쿼리 속도 개선 방안

SQL 튜닝이론 - 부분범위처리를 활용한 쿼리 속도 개선 방안

 

'부분범위 처리' 를 활용하면 인덱스로 엑세스를 통해 해야할  레코드가 아무리 많아도 빠른 응답속도를 낼 수 있습니다. DBMS가 클라이언트로 데이터를 전송할 때 일정량씩 나눠서 전송하기 때문입니다. 전달해야하는 전체 결과집합 중 미전송 분량이 남아있더라도 클라이언트로 부터 요청을 받지 않으면 대기합니다.

 

아래와 같은 WAS에 있는 Java execute 메서드를 통해 클라이언트에서 DB서버로  데이터를 요청한다고 가정합니다.

 

private void excute(Connection con) throws Exception {
    Statement st = conn.createStatement();
    ResultSet rs = stmt.executeQuery("select name from big_table)";

    for(int i = 0; i < 100; i ++){
        if(rs.next()) System.out.println(rs.getString(1));
    }
    rs.close();
    stmt.close();
}

 

쿼리 속의  'big_table'  테이블이 보유한 레코드가 1억건이라도 위 쿼리는 빠르게 처리 됩니다. 이유는 DBMS가 데이터를 모두 읽어 한 번에 전송하지 않고 먼저 읽는 데이터부터 일정량을 전송하고 멈추기 때문입니다. execute 메서드가 실행되면 DB서버 프로세스는 데이터를 10건 전송하고 대기합니다.

 

다음 execute 메서드를 통해 FETCH CALL이 발생하면 대기 큐에서 나와 다음 10건을 전송하고 대기합니다. 이처럼 전체 쿼리 결과집합을 연속적으로 전송하지 않고 사용자로부터 FETCH CALL이 있을 때마다 일정량씩 나눠 전송하는 것을 부분범위처리 라고 합니다.

 

데이터를 전송하는 단위인 ARRAY_SIZE는 클라이언트에서 설정합니다. 위의 예처럼 JAVA 프로그램의 ARRAY_SIZE 기본값은 10이며, Statement 객체의 setFetchSize 메서드를 통해 값을 변경할 수 있습니다.

 

최초 rs.next() 호출 시 FETCH_CALL을 통해 DB 서버로부터 전송받은 10건의 레코드를 클라이언트 캐시에 저장합니다.

1. 이후 rs.next() 호출시 FETCH_CALL을 발생시키지 않고 캐시에서 데이터를 읽습니다.
   
   
2. 케시에 있는 레코드가 모두 소진된 후, rs.next() 호출시 FETCH_CALL을 발생시킵니다.
   
   
3. 100건을 모두 읽을때 까지 2~3의 과정을 반복합니다.

 

10번의 FETCH CALL을 통해 100건의 레코드를 전송받아 출력합니다. 쿼리 수행 시 결과 집합인 100건을 클라이언트 캐시에 모두 적재하고 사용자에게 전송한다면 속도가 빠를 수 없습니다.

 

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

 

private void excute(Connection con) throws Exception {
    Statement st = conn.createStatement();
    ResultSet rs = stmt.executeQuery("select name from big_table order by created");

    for(int i = 0; i < 100; i ++){
        if(rs.next()) System.out.println(rs.getString(1));
    }
    rs.close();
    stmt.close();
}

 

 

만약 쿼리문에 order by 구문이 있으면 DB 서버는 모든 데이터를 다 읽고 created 순으로 정렬을 마친 후 클라이언트에게 데이터 전송을 시작할 수 있습니다. 이는 부분범위처리가 아닌 전체범위처리입니다. SORT AREA와 TEMP 테이블스페이스를 이용해 정렬을 마치고 일정량씩 나눠 클라이언트에 데이터를 전송합니다.

 

created 컬럼이 선두인 인덱스가 있으면 부분범위 처리가 가능합니다. 인덱스는 항상 정렬된 상태를 유지하므로 전체 데이터를 정렬하지 않고도 정렬된 상태의 결과집합을 바로 전송할 수 있습니다.

ARRAY_SIZE 조정을 통한 FETCH CALL 최소화 전략

 

네트워크를 통해 전송해야할 데이터량에 따라 ARRAY_SIZE를 조절할 필요가 있습니다. 만약 대량의 데이터 전체를 클라이언트로 전송해야 합니다. ARRAY_SIZE를 크게 설정하는 것이 좋습니다. 어차피 모든 데이터를 전송해야하므로 한 번에 가져오는 양이 많을 수록 FETCH_CALL의 횟수가 감소합니다.

 

반대로, 앞쪽의 일부 데이터만 FETCH 하다가 멈추는 프로그램이라면 ARRAY SIZE를 작게 설정하는 것이 유리합니다. 이미 케시에 저장된 데이터를 쓰지않는 비효율이 발생할 수 있기 때문입니다.

DB TOOL 에서의 부분범위 처리

 

토드나 오라클을 통해 데이터베이스를 조회할 때도 부분범위 처리를 이용합니다. 쿼리로 데이터를 조회하면 결과는 빨리나오지만 전체 데이터를 읽은 것은 아닙니다. 부분범위로 출력할 레코드의 수(INITIAL FETCH)를 지정하고 FETCH CALL 한 번에 읽어올 레코드의 수인 ARRAY SIZE를 설정합니다.

 

쿼리를 실행하면 INITIAL FETCH / ARRAY SIZE 만큼의 FETCH CALL을 실행하고 사용자가 스크롤을 통해 100번째 이상의 데이터를 읽으려고하면 다시 FETCH CALL을 발생시켜 ARRAY SIZE 만큼의 레코드 읽어옵니다.

 

Java 프로그래밍으로 부분범위 처리 구현

 

전체범위 처리로 구현한 자바프로그래밍 코드입니다.

 

public class AllRange {
    public static void execute(Connection con) throws Exception {
    int arraySize = 10;
    String SQLSTMT = "select object_id, object_name from all_objects";
    Statement stmt = conn.createStatement();
    stmt.setFetchSize(arraySize);
    Resultset rs = stmt.executeQuery(SQLSTMT);
    while(rs.next()){
        System.out.println(rs.getLong(1) + " : " + rs.getString(2));
    }
    rs.cloase();
    stmt.close()
}

 

다음은 부분범위 처리를 활용한 자바프로그래핑 코드입니다. 출력 레코드 수가 ARRAY_SIZE에 도달하면 멈추었다가 사용자 요청이 있으면 다시 FETCH CALL 합니다.

 

public class PartitalRange {
    public static int fetch(ResultSet rs, int arraysize) throws Exception {
        int i = 0;
        while(rs.next()){
        	System.out.println(rs.getLong(1) + " : " + rs.getString(2));
        	if( ++ i >= arraysize) return i;
        }
        
        return i;
    }
    public static void execute(Connection con) throws Exception {
        int arraysize = 10;
        String SQLSTMT = "select object_id, object_name from all_objects";
        Statement stmt = conn.createStatement();
        stmt.setFetchSize(arraySize);
        Resultset rs = stmt.executeQuery(SQLSTMT);
        while(true){
            int r = fetch(rs, arraysize);
            if(r < arraysize) break;
            System.out.println(rs.getLong(1) + " : " + rs.getString(2));
            BuffredReader in = new BufferedReader(new InputStringReader(System.in));
            String input = in.readLine();
            if(input.equals("Q") break;
         }
            rs.cloase();
            stmt.close();
    }
}

 

온라인트렌젝션 환경에서 부분범위 처리를 통한 성능개선

온라인트렌젝션 환경에서는 일반적으로 소량의 데이터를 읽고 갱신합니다. 다양의 데이터를 조회하는 경우에도 사용자가 모든 데이터를 일일이 확인하기보다 상위 일부 데이터만 확인하거나 페이지를 나누어 일부만 확인하는 경향이 있습니다. 이럴때 항상 정렬상태를 유지하는 인덱스를 이용하면, 정렬작업을 생략하고 앞쪽 일부데이터만 빠르게 보여줄 수 있습니다.

 

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

 

소트연산을 생략하기 위해 인덱스의 선두 컬럼을 게시판구분코드 + 등록일시 순으로 구성해야합니다. 게시판구분코드가 'A' 인 조건을 만족하는 레코드의 수만큼 테이블 렌덤 엑시스가 발생합니다. 인덱스에 등록일시 컬럼이 없는 경우 이후 모든 데이터를 다 읽어 등록일시 역순으로 정렬을 마치고야 출력을 시작하므로 속도가 느립니다. 아래는 인덱스로 소트연산을 생략할 수 없을 때 나타나는 실행계획입니다.

 

--EXECUTION PLAN
-- SELECT STATEMENT
--     SORT ORDER BY
--         TABLE ACCESS BY INDEX ROWID OF 게시판
--            INDEX RANGE SCAN OF 게시판_x01
-- 
--     PREDICATE INFORMATION
--    ACCESS("게시판구분코드" = 'A')

 

등록일시 컬럼을 인덱스에 추가해 sort order by 연산이 생략됐을때 실행계획입니다.

 

--EXECUTION PLAN
-- SELECT STATEMENT
--     TABLE ACCESS BY INDEX ROWID OF 게시판
--         INDEX RANGE SCAN OF 게시판_x01
-- 
--     PREDICATE INFORMATION
--    ACCESS("게시판구분코드" = 'A')

 

이 방식으로 수행하면 조건을 만족하는 전체 로우를 읽지 않고도 부분 결과집합을 출력할 수 있습니다.