SQL 튜닝의 시작 (2013년)
ROWNUM처리를 잘하자
- 페이지 처리를 하기에 앞서, 부분범위 처리가 가능하냐의 여부는 ROWNUM 을 어떻게 처리하느냐에 따라 결정되는 경우가 많으므로, 먼저 ROWNUM 의 효율적인 사용방법에 대해 알아야 한다.
부분 범위처리가 가능한 예
SELECT *
FROM (
SELECT ROWNUM rnum ,
x.*
FROM (
SELECT * FROM ...
ORDER BY ...
) x
WHERE ROWNUM <= :b2
)
WHERE rnum >= :b1 ;
- 앞의 SQL 에서 ROWNUM <= :B2 부분은 Optimizer 에게 데이터를 어디까지 추출하고 멈춰야 하는지를 알려주는 부분이다.
- 즉, COUNT STOPKEY 오퍼레이션에 의해 Order By 절에 기술된 컬럼 순서대로 정렬된 데이터를 :B2 에 입력된 값 만큼 추출하고 종료한다.
- 예를 들어, :B2 값이 100 이라면 전체 데이터 중 정렬 순서에 따라 100 건을 추출하면 데이터 추출을 멈추게 된다.
- 이렇게 SQL 에서 추출될 데이터는 많지만, 일부 데이터만 빠르게 추출하기 위해 조건으로 사용할 수 있는 것이 ROWNUM 이다.
- 그래서 페이지 처리 시 부분범위 처리가 가능하도록 SQL 을 작성하기 위한 필수 요건 중 하나가 바로 ROWNUM 이다.
- 그런데 ROWNUM 은 부분범위 처리가 되도록 사용하지 않으면, 전체범위 처리로 수행되어 SQL 성능개선에 도움을 주지 못한다.
부분 범위처리가 불가능한 예
SQL(1)
SELECT *
FROM (
SELECT ROWNUM rnum ,
x.*
FROM (
SELECT * FROM ...
ORDER BY ...
) x
)
WHERE rnum >= :b1 AND rnum <= :b2 ;
SQL(2)
SELECT *
FROM (
SELECT ROWNUM rnum ,
x.*
FROM (
SELECT * FROM ...
ORDER BY ...
) x
)
WHERE rnum BETWEEN :b1 AND :b2 ;
- SQL(1),SQL(2)의 경우 모두 전체 데이터를 조회하고, 정렬 후 전체 데이터 중 :B1 ~ :B2에 매칭되는 데이터를 추출한다.
- 전체 데이터 처리량이 많다면 이러한 페이지 처리는 성능문제를 일으킬 수 있다.
- 다만, 클라이언트 화면에 보여줄 한 페이지에 필요한 데이터만 추출하여 DB 서버에서 WAS 서버로 전송하기 때문에 네트워크 부하나 WAS 서버의 물리 메모리 사용량을 감소시킬 수 있는 장점은 있다.
- 지금까지 페이지 처리 시 효율적인 SQL 작성을 하기 위한 필수 요건인 ROWNUM 처리에 대해 알아 보았다.
- 앞에서는 이론상으로 성능을 예측하였으므로, 이제부터 테스트를 통해 ROWNUM 처리에 따른 SQL 성능차이를 알아 보도록 하자.
Script. 테스트 데이터 생성용
DROP TABLE T1 PURGE;
< T1 >
> 생성 요건
- 테이블 데이터 건수는 1,000,000 Rows
- 컬럼 c1 은 값의 종류가 1,000,000 가지 즉, unique 성
> 테이블 생성
CREATE TABLE t1
AS
SELECT 1000000-(LEVEL-1) c1, TO_CHAR(SYSDATE-(LEVEL-1),'yyyymmdd') c2, LEVEL c3
FROM DUAL
CONNECT BY LEVEL <= 1000000 ;
> 각 컬럼에 인덱스 생성 및 통계정보 수집
CREATE INDEX idx_t1_01 ON t1(c2,c1);
BEGIN
dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',
TABNAME=>'T1',
ESTIMATE_PERCENT=>99,
METHOD_OPT=>'FOR ALL INDEXED COLUMNS',
GRANULARITY=>'ALL', CASCADE=>TRUE,
NO_INVALIDATE=>FALSE) ;
END;
/
- 이제 아래와 같은 요건을 가진 화면이 있다고 가정하고, SQL 작성 시 ROWNUM 처리를 바꿔가면서 어떻게 작성해야 효율적인 수행이 되는지 알아 보자.
[화면 설명]
- 추출 조건 : T1 테이블에서 C2 (날짜 데이터) 컬럼 조건에 의해 오늘 이전의 모든 데이터를화면에 출력하여야 한다는 요건이 있다고 가정 한다.
- 하지만 오늘 이전의 데이터를 모두 추출하고자 할 경우 데이터가 너무 많아 페이지 처리를 하였다. 이때 각 페이지에 C2, C1 컬럼을 내림차순으로 정렬한 데이터를 50 건씩 보여줘야 한다.
- 이제 위 추출 조건을 만족하는 SQL 을 작성해 보도록 하자.
- 먼저, 오늘 이전의 모든 데이터를 추출하고 C2, C1 컬럼으로 정렬하는 SQL 을 작성하도록 하자. 작성된 SQL 은 아래와 같을 것이다.
SELECT *
FROM t1
WHERE c2 <= TO_CHAR(SYSDATE,'yyyymmdd')
ORDER BY c2 DESC, c1 DESC ;
- 이제 SQL 에서 추출되는 전체 데이터 중 선택된 페이지에 해당하는 50 건 만 가져오도록 ROWNUM 조건을 추가해야 할 것이다. 아래의 테스트[1] ~ 3은 동일한 데이터를 추출하는 SQL 이지만, ROWNUM을 처리하는 방법은 서로 다르다. 이제 이 3 개의 SQL 을 통해ROWNUM 처리 방법에 따라 SQL 성능이 어떻게 달라지는지 확인해 보도록 하자.
테스트 (1) 부분범위 처리가 되지 않는 부적절한 ROWNUM 처리 적용 SQL (C2+C1순으로 결합 인덱스 idx_t1_01가 존재함)
SELECT *
FROM (
SELECT ROWNUM rnum,
x.*
FROM (
SELECT *
FROM t1
WHERE c2 <= TO_CHAR(SYSDATE,'yyyymmdd'
)
ORDER BY c2 DESC, c1 DESC
) x
)
WHERE rnum >= :b1 AND rnum <= :b2 ;
call cou cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.76 0.77 0 535987 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.78 0.77 0 535987 0 50
Rows Row Source Operation
------- ---------------------------------------------------
50 FILTER (cr=535987 pr=0 pw=0 time=50 us)
50 VIEW (cr=535987 pr=0 pw=0 time=48 us)
1000000 COUNT (cr=535987 pr=0 pw=0 time=44 us)
1000000 VIEW (cr=535987 pr=0 pw=0 time=42 us)
1000000 TABLE ACCESS BY INDEX ROWID T1 (cr=535987 pr=0 pw=0 time=41 us)
1000000 INDEX RANGE SCAN DESCENDING IX_T1_2 (cr=3489 pr=0 pw=0 time=33 us)
- Note. Order By 절 순서와 동일한 인덱스가 존재하므로 원하는 부분까지만 읽고 처리할 것 같지만 트레이스 결과를 보면 전체 데이터를 처리한 후 50 건을 추출하였다.
테스트 (2) 부분범위 처리가 되지 않는 부적절한 ROWNUM 처리 적용
SELECT *
FROM (
SELECT ROWNUM rnum ,
x.*
FROM (
SELECT /*+ index_desc(t1(c2)) */
*
FROM t1
WHERE c2 <= TO_CHAR(SYSDATE,'yyyymmdd')
ORDER BY c2 DESC, c1 DESC
) x
)
WHERE rnum BETWEEN :b1 AND :b2 ;
call cou cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.76 0.77 0 535987 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.78 0.77 0 535987 0 50
Rows Row Source Operation
------- ---------------------------------------------------
50 FILTER (cr=535987 pr=0 pw=0 time=50 us)
50 VIEW (cr=535987 pr=0 pw=0 time=48 us)
1000000 COUNT (cr=535987 pr=0 pw=0 time=44 us)
1000000 VIEW (cr=535987 pr=0 pw=0 time=42 us)
1000000 TABLE ACCESS BY INDEX ROWID T1 (cr=535987 pr=0 pw=0 time=41 us)
1000000 INDEX RANGE SCAN DESCENDING IX_T1_2 (cr=3489 pr=0 pw=0 time=33 us)
- Note. 인덱스를 내림차순으로 읽으라는 힌트가 없어서 이런 일이 발생한 것이 아닌가 하여 힌트를 부여하고 ROWNUM 조건을 BETWEEN 으로 작성하여 SQL 을 수행하였으나, 결과는 테스트[1]과 동일하다.
- 무엇이 문제일까? 원인은 ROWNUM 조건을 잘못 작성했기 때문이다. 자세한 내용은 앞의 내용 중 "부분범위 처리가 불가능한 예"을 참조하기 바란다.
테스트 (3) 부분범위 처리가 되도록 적절한 ROWNUM 처리를 적용한 SQL(C2 +C1 순으로 결합 인덱스 idx_t1_01가 존재함)
var b1 number
var b2 number
exec :b1 := 1
exec :b2 := 50 ---> 50 건
SELECT *
FROM (
SELECT ROWNUM rnum,
x.*
FROM ( SELECT /*+ INDEX_DESC(C2) */ *
FROM T1
WHERE c2 <= TO_CHAR(SYSDATE,'yyyymmdd')
ORDER BY c2 DESC, c1 DESC
) x
WHERE ROWNUM <= :b2
)
WHERE rnum >= :b1 ;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.05 0 0 0 0
Fetch 5 0.00 0.00 0 12 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.05 0 12 0 50
Rows Row Source Operation
------- ---------------------------------------------------
50 VIEW (cr=12 pr=0 pw=0 time=93 us)
50 COUNT STOPKEY (cr=12 pr=0 pw=0 time=86 us)
50 VIEW (cr=12 pr=0 pw=0 time=83 us)
50 TABLE ACCESS BY INDEX ROWID T1 (cr=12 pr=0 pw=0 time=81 us)
50 INDEX RANGE SCAN DESCENDING IX_T1_2 (cr=7 pr=0 pw=0 time=49 us)
- 테스트를 통해 알아본 것과 같이 ROWNUM 처리방법이 SQL 성능에 미치는 영향이 매우 크다.
- 그러므로 앞으로는 ROWNUM을 무조건 사용하기 보다는 부분범위 처리를 할 수 있도록 SQL 을 작성해야 할 것이다.
"데이터베이스 스터디모임" 에서 2013년에 "SQL튜닝의시작
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3831
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.