부분범위 처리
- DBMS가 클라이언트에게 데이터를 전송할 때, 일정량씩 나눠 전송
- 전체 결과집합 중 아직 전송하지 않은 분량이 많이 남아있어도 서버 프로세스는 클라이언트로부터 추가 Fetch Call을 받기 전까지 그대로 멈춰 있음
- OTLP 환경에서 대용량 데이터를 빠르게 핸들링할 수 있는 중요한 원리
pivate void execute(Connection con) throws Exception {
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT NAME FROM TBL");
for(int i = 0; i < 100; ++i) {
if(rs.next()) System.out.println(rs.getString(1));
}
rs.close();
stmt.close();
}
- TBL이 1억 건에 이르는 대용량 테이블이어도 실행 결과는 매우 빠르게 출력
- DBMS가 데이터를 모두 읽어 한 번에 전송하지 않고 먼저 읽은 데이터부터 일정량(Array Size)을 전송하고 멈추기 때문
- 데이터를 전송하고 나면 서버 프로세스는 CPU를 OS에 반환하고 대기 큐에서 Sleep
- 다음 Fetch Call을 받으면 대기 큐에서 나와 그 다음 데이터부터 일정량 읽어 전송
- 전체 쿼리 결과집합을 쉼 없이 연속적으로 전송하지 않고 사용자로부터 Fetch Call이 있을 때마다 일정량씩 나눠 전송하는 것이 부분범위 처리
- 위 소스 수행 매커니즘
- 최초 rs.next() 호출 시 Fetch Call을 통해 DB 서버로부터 전송받은 데이터를 클라이언트 캐시에 저장
- 이후 rs.next() 호출 시 Fetch Call 없이 캐시에서 데이터 읽음
- 캐시에 저장한 데이터를 모두 소진한 상태에서 rs.next() 호출 시 추가 Fetch Call
- 위 과정 반복
- 100개 레코드를 전송받아 콘솔에 출력하고는 곧바로 ResultSet과 Statement 객체를 닫았으므로 위 프로그램은 테이블에 데이터가 아무리 많아도 오래 걸리지 않음
정렬 조건이 있을 때 부분범위 처리
- DB 서버는 모든 데이터를 다 읽어 정렬한 후 클라이언트에 데이터 전송 시작
- 전체범위처리임
- Sort Area와 Temp 테이블스페이스까지 이용해 정렬을 마치고 나면, 일정량씩 나눠 클라이언트에게 전송
- 정렬 기준 컬럼이 선두인 인덱스가 있다면 부분범위 처리 가능
- 인덱스는 항상 정렬된 상태이므로, 정렬 작업 없이 정렬된 상태의 결과집합을 얻을 수 있기 때문
Array Size 조정을 통한 Fetch Call 최소화
- 대량 데이터를 파일로 내려받는다면 데이터를 모두 전송해야 하므로 가급적 Array Size가 큰 것이 유리
- 전송량이 줄진 않으나, Fetch Call 횟수가 줄어듦
- 앞쪽 일부 데이터만 Fetch하다가 멈추는 프로그램이라면 Array Size를 작게 설정하는 것이 유리
- 불필요하게 많은 데이터를 전송하고 버리는 비효율을 줄일 수 있음
부분범위 처리 구현
public class AllRange {
public static void execute(Connection con) throws Exception {
int arraySize = 10;
String SQLStmt = "SELECT ID, NAME FROM TBL";
Statement stmt = con.createStatement();
stmt.setFetchSize(arraySize);
ResultSet rs = stmt.executeQuery(SQLStmt);
while(rs.next()) {
System.out.println(rs.getLong(1) + " " + rs.getString(2));
}
rs.close();
stmt.close();
}
public static void main(String[] args) throws Exception {
Connection con = getConnection();
execute(con);
releaseConnection(con);
}
}
public class PartitialRange {
public static int fetch(Resultset rs, int arraySize) throws Exception {
int i = 0;
whlie(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 ID, NAME FROM TBL";
Statement stmt = con.createStatement();
stmt.setFetchSize(arraySize);
ResultSet rs = stmt.executeQuery(SQLStmt);
while(true) {
int r = fetch(rs, arraySize);
if(r < arraySize) break;
System.out.println("Enter to Continue... (Q)uit?");
BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
String input = in.readLine();
if(input.equals("Q")) break;
}
rs.close();
stmt.close();
}
public static void main(String[] args) throws Exception {
Connection con = getConnection();
execute(con);
releaseConnection(con);
}
}
OLTP 환경에서 부분범위 처리에 의한 성능개선 원리
- OLTP(온라인 트랜잭션)은 일반적으로 소량의 데이터를 읽고 갱신
- OLTP에서 수천 ~ 수만건을 인덱스를 이용해 조회하려면 만족할만한 성능을 내기 어려울 수 있음
- 많은 테이블 랜덤 엑세스 때문
- 버퍼캐시히트율이 좋다면 빠른 성능을 보일 수도 있지만, 그렇지 않을 수도 있음
- OLTP성 업무에서 쿼리 결과 집합이 아주 많을 때, 사용자가 모든 데이터를 일일이 다 확인하지는 않음
- 특정한 정렬 순서로 상위 일부 데이터만 확인
- 항상 정렬 상태를 유지하는 인덱스를 활용하면, 정렬 작업을 생략하고 앞쪽 일부 데이터를 아주 빠르게 보여줄 수 있음
select 게시글ID, 제목, 작성자, 등록일시
from 게시판
where 게시판구분코드 = 'A'
order by 등록일시 desc
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
| 0 | SELECT STATEMENT | | 40000 | 3515K | 2041 (1)|
| 1 | SORT ORDER BY | | 40000 | 3515K | 2041 (1)|
| 2 | TABLE ACCESS BY INDEX ROWID | 게시판 | 40000 | 3515K | 1210 (1)|
|* 3 | INDEX RANGE SCAN | 게시판_X01 | 40000 | | 96 (2)|
Predicate Information (identified by operation id):
2 - access("게시판구분코드"='A')
- 인덱스 선두 컬럼을 (게시판구분코드 + 등록일시) 순으로 구성하지 않으면, 소트 연산을 생략할 수 없음
- 게시판구분코드 조건을 만족하는 모든 레코드를 인덱스에서 읽어야 함
- 모든 데이터를 다 읽어 등록일시 역순으로 정렬을 마치고 출력
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
| 0 | SELECT STATEMENT | | 40000 | 3515K | 1372 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID | 게시판 | 40000 | 3515K | 1372 (1)|
|* 2 | INDEX RANGE SCAN DESCENDING | 게시판_X02 | 40000 | | 258 (1)|
Predicate Information (identified by operation id):
2 - access("게시판구분코드"='A')
멈출 수 있어야 의미있는 부분범위 처리
- 문제는 앞쪽 일부만 출력하고 멈출 수 있는가
- 클라이언트 - DB 사이에 WAS, AP 서버 등이 존재하는 n-Tier 아키텍처에서는 클라이언트가 특정 DB 커넥션을 독점할 수 없음
- 단위 작업을 마치면 DB 커넥션을 곧바로 커넥션 풀에 반환
- 그 전에 SQL 조회 결과를 클라이언트에게 모두 전송하고 커서(Cursor)를 닫아야 함
- 결과집합을 나눠 전송하기 어려움
배치 I/O
- 인덱스 ROWID를 이용한 테이블 랜덤 엑세스는 고비용 구조
- 인덱스를 이용해 대량 데이터를 조회하면, 디스크 I/O 발생량도 함께 증하개 성능이 나빠짐
- 부분범위 처리를 활용해 상위 N개 집합을 빠르게 출력하도록 구현할 수 있다면, 인덱스로 엑세스할 전체 대상 레코드가 많아도 빠른 응답이 가능
- 배치(Batch) I/O는 읽는 블록마다 건건이 I/O Call을 발생시키는 비효율을 줄이기 위해 고안한 기능
- 인덱스를 이용해 테이블을 엑세스하다가 버퍼 캐시에서 블록을 찾지 못하면 일반적으로 디스크 블록을 바로 읽음
- 배치 I/O에서는 테이블 블록에 대한 디스크 I/O Call을 미뤗다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리
데이터 정렬 이슈
- 배치 I/O 기능이 작동하면 인덱스를 이용해 출력하는 데이터 정렬 순서가 매번 다를 수 있음
- 테이블 블록을 모두 버퍼 캐시에서 찾을 때는 인덱스 키값 순으로 출력
- 버퍼 캐시 히트율이 100% 미만이면, 실제 배치 I/O가 작동할 때는 데이터 출력 순서가 인덱스 정렬 순서와 다를 수 있음
SQL> create index emp_x01 on emp(deptno, job, empno);
SQL> set autotrace traceonly exp;
SQL> select * from emp e where deptno = 20 order by job, empno;
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 5 | 190 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 190 | 2 |
| 2 | INDEX RANGE SCAN | EMP_X01 | 5 | | 1 |
- 배치 I/O를 활성화 시키면, 테이블 엑세스 단계에 배치 I/O가 작동할 수 있다는 뜻인 BATCHED가 추가
- SORT ORDER BY 오퍼레이션도 추가
- 소트 생략 가능한 인덱스를 사용하더라도 배치 I/O 기능이 작동하면 데이터 정렬 순서를 보장할 수 없기 때문
SQL> select *
2 from emp e
3 where deptno = 20
4 order by job, empno;
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 5 | 190 | 2 |
| 1 | SORT ORDER BY | | 5 | 190 | 2 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 5 | 190 | 2 |
| 3 | INDEX RANGE SCAN | EMP_X01 | 5 | | 1 |
- 인덱스로 소트 연산을 생략할 수 없거나, SQL에 ORDER BY가 없으면, 랜덤 I/O 성능을 향상하는 이 기능을 생략할 이유가 없음
SQL> select * from emp e where deptno = 20 order by empno;
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 5 | 190 | 3 |
| 1 | SORT ORDER BY | | 5 | 190 | 3 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 5 | 190 | 2 |
| 3 | INDEX RANGE SCAN | EMP_X01 | 5 | | 1 |
SQL> select * from emp e where deptno = 20;
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 5 | 190 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 5 | 190 | 2 |
| 2 | INDEX RANGE SCAN | EMP_X01 | 5 | | 1 |
- 배치 I/O로 얻는 성능 이점이 많음에도, 시스템 레벨에서 이를 비활성화하는 경우가 있음
- 필요한 ORDER BY를 생략한 SQL 패턴 때문
- SQL에 ORDER BY가 없으면 결과집합의 정렬 순서를 보장할 필요가 없으므로 옵티마이저가 배치 I/O를 선택할 수 있음
- 출력된 결과집합의 정렬 순서가 매번 다를 수 있음
SELECT 장비번호, 변경일시, 상태코드
FROM 상태변경이력 H
WHERE 장비번호 = :eqp_no
AND ROWNUM <= 10;
SELECT 장비번호, 장비명, 상태코드
, (SELECT 변경일시
FROM 상태변경이력 H
WHERE 장비번호 = P.장비번호
AND ROWNUM <= 1) 최종변경일시
FROM 장비 P
WHERE 장비구분코드 = 'A001';
- 인덱스를 이용하면 결과집합이 자동으로 인덱스 키값 순으로 정렬되므로 ORDER BY를 생략한 채 rownum 조건과 index/index_desc 힌트를 사용하는 패턴을 과거에 사용
- 부분범위 처리 효과를 얻기 위해 rownum 조차 없이 index 힌트만으로 쿼리 작성하기도 함
SELECT 장비번호, 변경일시, 상태코드
FROM 상태변경이력 H
WHERE 장비번호 = :eqp_no ;
- 인덱스 정렬 순서를 믿고 ORDER BY를 생략하는 개발 패턴은 사용하지 않아야 함