SQL 튜닝의 시작 (2013년)
기본적인 형태의 PAGINATION을 익히자
- 앞에서 소개한 페이지 처리 시 알아야 할 필수 요건을 토대로, 효율적인 페이지 처리 SQL을 작성하는 과정을 같이 진행해 보자.
- 이 과정을 통해 페이지 처리에 대한 보다 깊은 이해를 할 수 있는 기회가 되길 희망한다.
- 아래 성능 문제 SQL은 페이지 처리가 되어 있지 않은 온라인 화면이며, 페이지 처리는 WEB 서버에서 이루어 진다고 가정하자.
- 이런 SQL 은 많은 비효율을 가지고 있으므로, 효율적인 페이지 처리가 가능한 SQL 로 완성해 가도록 하자.
성능 문제 SQL
SELECT ... 생략
FROM trd100 a ,
trm100 b ,
trm150 c
WHERE a.acpt_no = b.acpt_no
AND a.acpt_no = c.acpt_no
AND b.styl_cd = 'AC01'
AND a.proc_stus IN ( 'D' , 'E' )
AND a.brof_cd LIKE TRIM( :b0 ) || '%'
AND a.acpt_dt || a.acpt_time < :b1
ORDER BY a.acpt_dt DESC ,
a.acpt_time DESC ;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 41 0.90 26.17 4718 8666 0 599
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 43 0.90 26.18 4718 8666 0 599
Rows Row Source Operation
---------- ---------------------------------------------------
599 SORT ORDER BY (cr=8666 pr=4718 pw=0 time=26173450 us ...)
599 TABLE ACCESS BY INDEX ROWID TRM100 (cr=8666 pr=4718 ...)
1199 NESTED LOOPS (cr=8067 pr=4141 pw=0 time=33177311 us ...)
599 NESTED LOOPS (cr=6864 pr=3678 pw=0 time=42743190 us ...)
2057 TABLE ACCESS BY INDEX ROWID TRD100 (cr=2143 pr=2050 ...)
2057 INDEX RANGE SCAN IX_TRD100_01 (cr=163 pr=162 ...)
599 TABLE ACCESS BY INDEX ROWID TRM150 (cr=4721 pr=1628 ...)
599 INDEX UNIQUE SCAN PK_TRM150 (cr=4122 pr=1059 ...)
599 INDEX UNIQUE SCAN IX_TRM100_02 (cr=1203 pr=463 ...)
[A] WHERE 조회 조건
AND a.proc_stus IN ( 'D' , 'E' )
AND a.brof_cd LIKE TRIM( :b0 ) || '%'
[B] 인덱스 구성정보
INDEX_NAME COLUMN LIST
------------ ---------------------------------------
ix_trd100_01 brof_cd, proc_stus, acpt_dt, acpt_time
[C] ORDER BY (정렬 기준)
ORDER BY a.acpt_dt DESC,
a.acpt_time DESC
[D] ROWNUM 처리 . 없음.
[E] PLAN 상 OPERATION
599 SORT ORDER BY (cr=8666 pr=4718 pw=0 time=26173450 us ...)
- SQL 분석결과 . [A], [B], [C], [D]의 정보로 [E]를 분석
- 처리결과 - 페이지 처리가 되지 않아 전체 데이터 추출하여 비효율 발생
- 앞의 [성능 문제 SQL]은 전체 데이터를 모두 추출하는 온라인 화면의 SQL 이며, DB 서버에서 추출된 599 건을 WAS 서버로 전송한다.
- WAS 서버는 전송받은 데이터 중에서 클라이언트 화면에 필요한 데이터 건수만큼 추출해 클라이언트에 보내게 된다.
- DB 서버에서 효율 적인 페이지 처리 (부분범위 처리)로 수행되지 않지만, 실제 클라이언트 화면에서는 페이지 처리가 된 상태로 데이터를 보여주는 것이다.
- 그렇다면 [성능 문제 SQL]이 효율적인 페이지 처리가 가능하도록 작성한 경우와 그렇지 못한 경우에 대한 SQL 을 작성하여 어느 정도의 성능차이가 발생하는지 분석해 보도록 하자.
잘못된 페이지 처리(1)
- ROWNUM 처리와 ORDER BY 절을 잘못 사용한 경우 [실행계획: SORT ORDER BY -> COUNT]
SELECT *
FROM (
SELECT ROWNUM rnum , x.*
FROM ( SELECT /*+ LEADING(A) USE_NL(A B C) INDEX_DESC(A IX_TRD100_01) */
... 생략
FROM trd100 a, trm100 b, trm150 c
WHERE a.acpt_no = b.acpt_no
AND a.acpt_no = c.acpt_no
AND b.styl_cd = 'AC01'
AND a.proc_stus IN ( 'D' , 'E' )
AND a.brof_cd LIKE TRIM( :b0 ) || '%'
AND a.acpt_dt || a.acpt_time < :b1
ORDER BY a.acpt_dt DESC , a.acpt_time DESC
) x
)
WHERE rnum <= :b2 and rnum >= :b3 ;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.90 27.90 4672 8665 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.90 27.91 4672 8665 0 50
Rows Row Source Operation
---------- ---------------------------------------------------
50 FILTER (cr=8665 pr=4672 pw=0 time=27904482 us)
50 VIEW (cr=8665 pr=4672 pw=0 time=27904479 us ...)
599 COUNT (cr=8665 pr=4672 pw=0 time=27905514 us)
599 VIEW (cr=8665 pr=4672 pw=0 time=27905065 us ...)
599 SORT ORDER BY (cr=8665 pr=4672 pw=0 time=27904910 us ...)
599 TABLE ACCESS BY INDEX ROWID TRM100 (cr=8665 pr=4672 ...)
1199 NESTED LOOPS (cr=8066 pr=4100 pw=0 time=4626814 us ...)
599 NESTED LOOPS (cr=6863 pr=3635 pw=0 time=13468562 us ...)
2057 TABLE ACCESS BY INDEX ROWID TRD100 (cr=2142 pr=1905 ...)
2057 INDEX RANGE SCAN DESCENDING IX_TRD100_01 (cr=163 pr=1 ...)
599 TABLE ACCESS BY INDEX ROWID TRM150 (cr=4721 pr=1730 ...)
599 INDEX UNIQUE SCAN PK_TRM150 (cr=4122 pr=1166 ...)
599 INDEX UNIQUE SCAN IX_TRM100_02 (cr=1203 pr=465 ...)
[A] WHERE 조회 조건
AND a.proc_stus IN ( 'D' , 'E' )
AND a.brof_cd LIKE TRIM( :b0 ) || '%'
[B] 인덱스 구성정보
INDEX_NAME COLUMN LIST
------------- --------------------------------------
ix_trd100_01 brof_cd, proc_stus, acpt_dt, acpt_time
[C] ORDER BY (정렬 기준)
ORDER BY a.acpt_dt DESC,
a.acpt_time DESC
[D] ROWNUM 처리
rnum <= :b2 and rnum >= :b3
[E] PLAN 상 OPERATION
599 COUNT (cr=8665 pr=4672 pw=0 time=27905514 us)
599 VIEW (cr=8665 pr=4672 pw=0 time=27905065 us ...)
599 SORT ORDER BY (cr=8665 pr=4672 pw=0 time=27904910 us ...)
- SQL 분석결과 . [A], [B], [C], [D]의 정보로 E를 분석
- 처리결과 - 페이지 처리가 비효율 (아래의 PLAN 의 오퍼레이션으로 판단)
- SORT ORDER BY ORDER BY 절의 순서가 인덱스 컬럼 순서에 부적합
- COUNT ROWNUM 처리 부적합 (전체범위 처리가 되는 방식)
잘못된 페이지 처리[1]의 개선 전 SQL
SELECT *
FROM (
SELECT ROWNUM rnum ,
x.*
FROM (
SELECT * FROM ...
ORDER BY a.acpt_dt DESC,
a.acpt_time DESC ---> 문제점.Order By 절과 인덱스 구성순서와 다름
) x
)
WHERE rnum <= :b2 AND rnum >= :b3 ; ---> 문제점.ROWNUM 처리가 부적절
잘못된 페이지 처리[1]의 개선 후 SQL
SELECT *
FROM (
SELECT ROWNUM rnum ,
x.*
FROM (
SELECT * FROM ...
ORDER BY a.brof_cd DESC,
a.proc_stus DESC,
a.acpt_dt DESC,
a.acpt_time DESC ---> 개선내용.Order By 절과 인덱스 구성순서와 맞춤
) x
WHERE ROWNUM <= :b2 ---> 개선내용.부분범위 처리가 되도록 ROWNUM 처리 변경
)
WHERE rnum >= :b1 ;
잘못된 페이지 처리(2)
- ROWNUM 처리는 효율적이나 ORDER BY를 잘못 사용한 경우 (실행계획 : SORT ORDER BY STOPKEY -> COUNT STOPKEY)
SELECT *
FROM (
SELECT ROWNUM rnum , x.*
FROM (
SELECT /*+ LEADING(A) USE_NL(A B C) INDEX_DESC(A IX_TRD100_01) */
... 생략
FROM trd100 a ,
trm100 b ,
trm150 c
WHERE a.acpt_no = b.acpt_no
AND a.acpt_no = c.acpt_no
AND b.styl_cd = 'AC01'
AND a.proc_stus IN ( 'D' , 'E' )
AND a.brof_cd LIKE TRIM( :b0 ) || '%'
AND a.acpt_dt || a.acpt_time < :b1
ORDER BY a.acpt_dt DESC ,
a.acpt_time DESC
) x
WHERE ROWNUM <= :b2
)
WHERE rnum >= :b3 ;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 5 0.97 31.65 4949 8665 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.98 31.66 4949 8665 0 50
Rows Row Source Operation
---------- ---------------------------------------------------
50 VIEW (cr=8665 pr=4949 pw=0 time=31655963 us ...)
50 COUNT STOPKEY (cr=8665 pr=4949 pw=0 time=31655952 us)
50 VIEW (cr=8665 pr=4949 pw=0 time=31655949 us ...)
50 SORT ORDER BY STOPKEY (cr=8665 pr=4949 pw=0 time=31655944 us ...)
599 TABLE ACCESS BY INDEX ROWID TRM100 (cr=8665 pr=4949 ...)
1199 NESTED LOOPS (cr=8066 pr=4373 pw=0 time=10498695 us ...)
599 NESTED LOOPS (cr=6863 pr=3885 pw=0 time=20250270 us ...)
2057 TABLE ACCESS BY INDEX ROWID TRD100 (cr=2142 pr=2108 ...)
2057 INDEX RANGE SCAN DESCENDING IX_TRD100_01 (cr=163 pr=162 ...)
599 TABLE ACCESS BY INDEX ROWID TRM150 (cr=4721 pr=1777 ...)
599 INDEX UNIQUE SCAN PK_TRM150 (cr=4122 pr=1207 ...)
599 INDEX UNIQUE SCAN IX_TRM100_02 (cr=1203 pr=488 ...)
[A] WHERE 조회 조건
AND a.proc_stus IN ( 'D' , 'E' )
389
AND a.brof_cd LIKE TRIM( :b0 ) || '%'
[B] 인덱스 구성정보
INDEX_NAME COLUMN LIST
------------ ---------------------------------------
ix_trd100_01 brof_cd, proc_stus, acpt_dt, acpt_time
[C] ORDER BY (정렬 기준)
ORDER BY a.acpt_dt DESC,
a.acpt_time DESC
[D] ROWNUM 처리
WHERE ROWNUM <= :b2
[E] PLAN 상 OPERATION
50 COUNT STOPKEY (cr=8665 pr=4949 pw=0 time=31655952 us)
50 VIEW (cr=8665 pr=4949 pw=0 time=31655949 us ...)
50 SORT ORDER BY STOPKEY (cr=8665 pr=4949 pw=0 time=31655944 us ...)
----------------------------------------------------------------------------------------------
- SQL 분석결과 . [A], [B], [C], [D]의 정보로 [E]를 분석
- 처리결과 - 페이지 처리가 비효율 (아래의 PLAN 의 오퍼레이션으로 판단)
- SORT ORDER BY STOPKEY ORDER BY 절의 순서가 인덱스 컬럼 순서에 부적합
- COUNT STOPKEY ROWNUM 처리가 적합 (부분범위 처리가 되는 방식)
잘못된 페이지 처리[2]의 개선 전 SQL
SELECT *
FROM ( SELECT ROWNUM rnum,
X.*
FROM (
SELECT * from ...
ORDER BY a.acpt_dt desc,
a.acpt_time desc ---> 문제점.Order By 절과 인덱스 구성순서와 다름.
) x
WHERE rownum <= :b2
)
WHERE rnum >= :b1 ;
잘못된 페이지 처리[2]의 개선 후 SQL
SELECT *
FROM (
SELECT ROWNUM rnum,
X.*
FROM (
SELECT *
FROM ...
ORDER BY a.brof_cd desc,
a.proc_stus,
a.acpt_dt desc,
a.acpt_time desc --> 개선내용.Order By 절과 인덱스 구성순서와 맞춤
) x
WHERE ROWNUM <= :b2
)
WHERE rnum >= :b1 ;
잘못된 페이지 처리(3)
- ROWNUM 처리가 잘못된 경우 (실행 계획 : COUNT [SORT ORDER BY(X)] )
SELECT *
FROM (
SELECT ROWNUM rnum , x.*
FROM (
SELECT /*+ LEADING(A) USE_NL(A B C) INDEX_DESC(A IX_TRD100_01) */
... 생략
FROM trd100 a ,
trm100 b ,
trm150 c
WHERE a.acpt_no = b.acpt_no
AND a.acpt_no = c.acpt_no
AND b.styl_cd = 'AC01'
AND a.proc_stus IN ( 'D' , 'E' )
AND a.brof_cd LIKE TRIM( :b0 ) || '%'
AND a.acpt_dt || a.acpt_time < :b1
ORDER BY a.brof_cd DESC ,
a.proc_stus DESC ,
a.acpt_dt DESC ,
a.acpt_time DESC
) x
)
WHERE rnum <= :b2 AND rnum >= :b3 ;
call count 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.16 3.95 538 8677 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.17 3.96 538 8677 0 50
Rows Row Source Operation
---------- ---------------------------------------------------
50 FILTER (cr=8677 pr=538 pw=0 time=1957 us)
50 VIEW (cr=8677 pr=538 pw=0 time=1955 us cost=9 size=159 card=1)
599 COUNT (cr=8677 pr=538 pw=0 time=3261348 us)
599 VIEW (cr=8677 pr=538 pw=0 time=3260896 us cost=9 size=146 card=1)
599 TABLE ACCESS BY INDEX ROWID TRM150 (cr=8677 pr=538 ...)
1199 NESTED LOOPS (cr=8078 pr=538 pw=0 time=227759 us ...)
599 NESTED LOOPS (cr=6874 pr=530 pw=0 time=4307258 us ...)
2057 TABLE ACCESS BY INDEX ROWID TRD100 (cr=2147 pr=10 ...)
2057 INDEX RANGE SCAN DESCENDING IX_TRD100_01 (cr=167 pr=0 ...)
599 TABLE ACCESS BY INDEX ROWID TRM100 (cr=4727 pr=520 ...)
599 INDEX UNIQUE SCAN IX_TRM100_02 (cr=4128 pr=7 pw=0 time=56539 us ...)
599 INDEX UNIQUE SCAN PK_TRM150 (cr=1204 pr=8 pw=0 time=53068 us ...)
[A] WHERE 조회 조건
AND a.proc_stus IN ( 'D' , 'E' )
AND a.brof_cd LIKE TRIM( :b0 ) || '%'
[B] 인덱스 구성정보
INDEX_NAME COLUMN LIST
------------ ---------------------------------------
ix_trd100_01 brof_cd, proc_stus, acpt_dt, acpt_time
[C] ORDER BY (정렬 기준)
ORDER BY a.brof_cd DESC,
a.proc_stus DESC,
a.acpt_dt DESC,
a.acpt_time DESC
[D] ROWNUM 처리
WHERE rnum <= :b2 and rnum >= :b3
[E] PLAN 상 OPERATION
599 COUNT (cr=8677 pr=538 pw=0 time=3261348 us)
- SQL 분석결과 . [A], [B], [C], [D]의 정보로 [E]를 분석
- 처리결과 - 페이지 처리가 비효율 (아래의 PLAN 의 오퍼레이션으로 판단)
- COUNT PLAN 에 SORT ORDER BY[STOPKEY] 없이 COUNT 만 있는 경우는, ORDER BY 절의 컬럼 순서와 인덱스 구성정보는 효율적이나, ROWNUM 처리가 잘못되어 있을 때 보이는 오퍼레이션으로 부분범위 처리가 되지 않음.
잘못된 페이지 처리[3]의 개선 전 SQL
SELECT *
FROM (
SELECT ROWNUM rnum,
X.*
FROM (
SELECT * FROM ...
ORDER BY a.brof_cd desc,
a.proc_stus desc,
a.acpt_dt desc,
a.acpt_time desc
) x
)
WHERE rnum <= :b2 AND rnum >= :b3 ; ---> 문제점.ROWNUM 처리가 부적절
잘못된 페이지 처리[3]의 개선 후 SQL
SELECT *
FROM (
SELECT ROWNUM rnum,
X.*
FROM (
SELECT * FROM ...
ORDER BY a.brof_cd desc,
a.proc_stus,
a.acpt_dt desc,
a.acpt_time desc
) x
WHERE ROWNUM <= :b2 ---> 개선내용.부분범위 처리가 되도록 ROWNUM 처리 변경
)
WHERE rnum >= :b3 ;
- [성능 문제 SQL]이 부분범위 처리가 가능한 효율적인 페이지 처리가 되도록 SQL 을 작성하는 방법에 대해서 알아 보았다.
- ROWNUM 처리, Where 절 조건, Order By 절, 그리고 인덱스 구성은 효율적인 페이지 처리에 꼭 필요한 필수 요건이다.
- 그리고 Where 절과 Order By 절을 수행하는 테이블은 Driving Table 이여야 하고, 조인은 Nested Loops Join 으로 수행되어야 한다.
- 여기까지 내용을 읽은 독자들은 어쩌면 페이지 처리를 매우 간단하게 생각할 수 있다.
- 하지만 페이지 처리의 기본과 핵심 내용을 완벽하게 이해하지 못한다면, 잘못된 페이지 처리를 SQL 에 적용하여, 엉뚱한 데이터가 출력된다거나 예기치 못한 비효율을 발생시켜 DB 서버에 성능 문제를 일으킬 수도 있으므로 주의해야 한다.
- 더욱이 페이지 처리는 결코 간단하게 이해할 수 있는 내용이 아니므로, 꼼꼼하고 깊이 있는 이해를 바탕으로 SQL 을 작성해야 할 것이다.
"데이터베이스 스터디모임" 에서 2013년에 "SQL튜닝의시작
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3836
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.