오라클 성능 고도화 원리와 해법 II (2012년)
Sort Area를 적게 사용하도록 SQL 작성
- 소트 연산이 불가피 할 경우, 메모리 내에서 처리 완료해야 함
sort area 적게 사용하는 방법
- 소트를 완료하고 나서 데이터 가공
- Top-N쿼리 : 소트 연산(=값 비교) 횟수 최소화 및 sort area 사용량 감소
소트를 완료하고 나서 데이터 가공하기
- 사례 : 1번 쿼리와 2번 쿼리 중 sort area를 적게 사용하는 쿼리는?
결론
- 1번 쿼리 : 가공된 결과치를 sort area를 담음
- 2번 쿼리 : 가공되지 않은 상태로 정렬 완료하고 최종 출력시 가공하므로 sort area 적게 사용
1번 쿼리
SELECT LPAD(상품번호, 30) || LPAD(상품명, 30) || LPAD(고객id, 10)
|| LPAD(고객명, 20) || TO_CHAR(주문일시, 'yyyymmdd hh24:mi:ss')
FROM 주문상품
WHERE 주문일시 BETWEEN :start AND :end
ORDER BY 상품번호;
2번 쿼리
SELECT LPAD(상품번호, 30) || LPAD(상품명, 30) || LPAD(고객ID, 10)
|| LPAD(고객명, 20) || TO_CHAR(주문일시, 'YYYYMMDD HH24:MI:SS')
FROM (
SELECT 상품번호, 상품명, 고객ID, 고객명, 주문일시
FROM 주문상품
WHERE 주문일시 BETWEEN :START AND :END
ORDER BY 상품번호
);
TOP-N쿼리
특징
- 소트 연산(=값 비교) 횟수를 최소화하고, sort area 사용량 감소 가능
- 종목코드 + 거래일시 순으로 구성된 인덱스가 존재한다면 옵티마이저는 그 인덱스를 이용함으로써 order by 연산 대체 가능
- rownum 조건을 사용해 N건에서 멈추도록 했으므로 조건절에 부합하는 레코드가 아무리 많아도 매우 빠른 수행 속도 낼 수 있음 ==> 실행계획 상 "count stopkey"
- TOP-N쿼리의 소트 부하 경감 원리 : 종목코드 + 거래일시 순으로 구성된 인덱스가 존재하지 않았을 경우
- rownum <= 10 : 우선 10개 레코드를 담을 배열 할당 ⇒ 처음 읽은 10개 레코드를 정렬된 상태로 담기
- 이후 읽는 레코드는 맨 우측에 있는 값(=가장 큰 값)과 비교 : 그보다 작은 값이 나타날 때만 배열 내에서 다시 정렬 시도하고, 맨 우측 값은 버림
- 이 과정 반복 : 전체 레코드를 정렬하지 않고 오름차순(ASC)으로 최소값을 갖는 10개 레코드를 정확히 찾아냄
 |
SQL Server or Sybase에서 TOP-N 쿼리
SELECT TOP 10 거래일시, 채결건수, 체수량, 거래대금
FROM 시간대별종목거래
WHERE 종목코드 = 'KR123456'
AND 거래일시 >= '20080304';
IBM DB2에서 TOP-N 쿼리
SELECT 거래일시, 채결건수, 체수량, 거래대금
FROM 시간대별종목거래
WHERE 종목코드 = 'KR123456'
AND 거래일시 >= '20080304'
ORDER 거래일시
FETCH FIRST 10 ROWS ONLY;
ORACLE에서 TOP-N 쿼리 : inline view로 한번 감싸야 함
SELECT *
FROM (
SELECT 거래일시, 채결건수, 체수량, 거래대금
WHERE 종목코드 = 'KR123456'
AND 거래일시 >= '20080304'
ORDER 거래일시
)
WHERE ROWNUM <= 10;
Execution Plan
------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS
1 0 COUNT (STOPKEY)
2 1 VIEW
3 2 TABLE ACCESS (BY INDEX ROWID) OF '시간별종목거래' (TABLE)
4 3 INDEX (RANGE SCAN) OF '시간별종목거래_PK' (INDEX (UNIQUE))
TOP-N 쿼리의 sort 부하 경감 원리 : 전체 레코드 개수 구하기
SQL> create table t as select * from all_objects;
Table created.
SQL> alter session set workarea_size_policy = manual;
Session altered.
SQL> alter session set sort_area_size = 524288;
Session altered.
SQL> set autotrace traceonly statistics
SQL> select count(*) from t;
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
773 consistent gets
687 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
효과 측정 : TOP-N 쿼리가 작동할 때
SET AUTOTRACE ON
select *
from (
select * from t
order by object_name
)
where rownum <= 10 ;
Execution Plan
----------------------------------------------------------------------------------------
Plan hash value: 3299198703
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1280 | | 2234 (1)| 00:00:22 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 59455 | 7431K| | 2234 (1)| 00:00:22 |
|* 3 | SORT ORDER BY STOPKEY| | 59455 | 7431K| 18M| 2234 (1)| 00:00:22 |
| 4 | TABLE ACCESS FULL | T | 59455 | 7431K| | 159 (2)| 00:00:02 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
692 consistent gets
0 physical reads
0 redo size
1683 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
효과 측정 : TOP-N 쿼리가 작동하지 않을 때
SQL> select *
from (
select a.*, rownum no
from (
select * from t order by object_name
) a
)
where no <= 10 ;
10 rows selected.
Execution Plan
--------------------------------------------------------------------------------------
Plan hash value: 3902787780
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 59455 | 8186K| | 2234 (1)| 00:00:22 |
|* 1 | VIEW | | 59455 | 8186K| | 2234 (1)| 00:00:22 |
| 2 | COUNT | | | | | | |
| 3 | VIEW | | 59455 | 7431K| | 2234 (1)| 00:00:22 |
| 4 | SORT ORDER BY | | 59455 | 7431K| 18M| 2234 (1)| 00:00:22 |
| 5 | TABLE ACCESS FULL| T | 59455 | 7431K| | 159 (2)| 00:00:02 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NO"<=10)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
13 recursive calls
13 db block gets
850 consistent gets
699 physical reads
0 redo size
1750 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
10 rows processed
분석함수에서 TOP-N 쿼리
- 특징 : window sort 시에도 rank(), row_number()를 사용하면 TOP-N쿼리 알고리즘 작동 : max() 등 함수 사용할 때보다 소트 부하 경감
- 마지막 이력 레코드 찾는 쿼리 : max() 함수 사용
SQL> create table tt
as
select 1 id, rownum seq, owner, object_name, object_type, created, status
from all_objects ;
Table created.
begin
for i in 1..9
loop
insert into tt
select i+1 id, rownum seq
, owner, object_name, object_type, created, status
from tt
where id = 1;
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> alter session set workarea_size_policy = manual;
SQL> alter session set sort_area_size = 1048576;
SQL> alter session set sql_trace = true;
SQL> alter session set tracefile_identifier='eun01';
SQL> select id, seq, owner, object_name, object_type, created, status
from (select id, seq
, max(seq) over (partition by id) last_seq
, owner, object_name, object_type, created, status
from tt)
where seq = last_seq;
$ TKPROF eunora_ora_18913_eun01.trc eun01.log SORT=(EXECPU) EXPLAIN = eun/loveora
==================================== eun01.log ==========================================
select id, seq, owner, object_name, object_type, created, status
from (select id, seq
, max(seq) over (partition by id) last_seq
, owner, object_name, object_type, created, status
from tt)
where seq = last_seq
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 3.85 6.44 13487 4536 9 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 3.86 6.46 13487 4537 9 10
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 68 (EUN)
Rows Row Source Operation
------- ---------------------------------------------------
10 VIEW (cr=4536 pr=13487 pw=8980 time=3669305 us)
500200 WINDOW SORT (cr=4536 pr=13487 pw=8980 time=23961167 us)
500200 TABLE ACCESS FULL TT (cr=4536 pr=0 pw=0 time=21008541 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
10 VIEW
500200 WINDOW (SORT)
500200 TABLE ACCESS (FULL) OF 'TT' (TABLE)
마지막 이력 레코드 찾는 쿼리 : rank() 함수 사용
SQL> alter session set workarea_size_policy = manual;
Session altered.
SQL> alter session set sort_area_size = 1048576;
Session altered.
SQL> alter session set sql_trace = true;
Session altered.
SQL> alter session set tracefile_identifier='eun02';
Session altered.
SQL> select id, seq, owner, object_name, object_type, created, status
from (select id, seq
, rank() over (partition by id order by seq desc) rnum
, owner, object_name, object_type, created, status
from tt)
where rnum = 1;
$ TKPROF eunora_ora_19087_eun02.trc eun02.log SORT=(EXECPU) EXPLAIN = eun/loveora
==================================== eun02.log ==========================================
select id, seq, owner, object_name, object_type, created, status
from (select id, seq
, rank() over (partition by id order by seq desc) rnum
, owner, object_name, object_type, created, status
from tt)
where rnum = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.03 0 72 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 2.36 2.63 37 4536 38 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.39 2.66 37 4608 38 10
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 68 (EUN)
Rows Row Source Operation
------- ---------------------------------------------------------------
10 VIEW (cr=4536 pr=37 pw=37 time=2576319 us)
118 WINDOW SORT PUSHED RANK (cr=4536 pr=37 pw=37 time=2575430 us)
500200 TABLE ACCESS FULL TT (cr=4536 pr=0 pw=0 time=19507973 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
10 VIEW
118 WINDOW (SORT PUSHED RANK)
500200 TABLE ACCESS (FULL) OF 'TT' (TABLE)
"구루비 데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3302
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.