- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=6260122&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
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)
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=6260122&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.