오라클 성능 고도화 원리와 해법 II (2016년)
h1.06.Sort Area를 적게 사용하도록 SQL 작성
- 소트 연산이 불가피하다면 메모리 내에서 처리를 완료할 수 있도록 노력해야 한다.
Sort Area 크기를 늘리는 방법도 있지만 Sort Area를 적게 사용할 방법부터 찾는 것이 순서다.
h3.(1)소트를 완료하고 나서 데이터 가공하기
- 소팅을 하고 난 후 데이터를 가공해주면 메모리 사용량을 줄일 수 있다.
소트 후 데이터 가공 테스트
[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 상품번호
)
테스트 해보니 두 방법 실행계획이 똑같았다. 아마도 쿼리 변환 때문인 것 같다.
다시 테스트 진행
--바로 소팅한 경우
select /*+gather_plan_statistics*/ lpad(dt,30)||lpad(key,30)||lpad(nm,10)||lpad(dept,20)
from testtab1
where dt = '2017-05-31'
order by dt
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 113K|00:00:01.08 | 53228 | | | |
| 1 | SORT ORDER BY | | 1 | 2247 | 113K|00:00:01.08 | 53228 | 14M| 1437K| 12M (0)|
| 2 | TABLE ACCESS BY INDEX ROWID| TESTTAB1 | 1 | 2247 | 113K|00:00:00.51 | 53228 | | | |
|* 3 | INDEX RANGE SCAN | TESTTAB1_IDX22 | 1 | 2247 | 113K|00:00:00.11 | 3984 | | | |
------------------------------------------------------------------------------------------------------------------------------------
--alias 주고 소팅한 경우
select /*+gather_plan_statistics*/ lpad(dt,30)||lpad(key,30)||lpad(nm,10)||lpad(dept,20) dt
from testtab1
where dt = '2017-05-31'
order by dt
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 113K|00:00:01.07 | 53228 | | | |
| 1 | SORT ORDER BY | | 1 | 2247 | 113K|00:00:01.07 | 53228 | 12M| 1374K| 11M (0)|
| 2 | TABLE ACCESS BY INDEX ROWID| TESTTAB1 | 1 | 2247 | 113K|00:00:00.51 | 53228 | | | |
|* 3 | INDEX RANGE SCAN | TESTTAB1_IDX22 | 1 | 2247 | 113K|00:00:00.11 | 3984 | | | |
------------------------------------------------------------------------------------------------------------------------------------
--먼저 소팅하고 데이터 가공한 경우
select /*+gather_plan_statistics*/ lpad(dt,30)||lpad(key,30)||lpad(nm,10)||lpad(dept,20)
from
(
select /*+no_merge*/ dt, key, nm, dept from testtab1
where dt = '2017-05-31'
order by dt
)
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 113K|00:00:00.65 | 53228 | | | |
| 1 | VIEW | | 1 | 2247 | 113K|00:00:00.65 | 53228 | | | |
| 2 | SORT ORDER BY | | 1 | 2247 | 113K|00:00:00.60 | 53228 | 7353K| 1079K| 6535K (0)|
| 3 | TABLE ACCESS BY INDEX ROWID| TESTTAB1 | 1 | 2247 | 113K|00:00:00.49 | 53228 | | | |
|* 4 | INDEX RANGE SCAN | TESTTAB1_IDX22 | 1 | 2247 | 113K|00:00:00.11 | 3984 | | | |
-------------------------------------------------------------------------------------------------------------------------------------
메모리 사용량이 거의 15배가량 차이나는 것 같다.
역시나 소팅 먼저하고 가공하는 게 유리하다.
h3.(2)Top-N 쿼리
- Top-N 쿼리 형태로 작성하면 소트 연산 횟수를 최소화함은 물론 Sort Area 사용량을 줄일 수 있다.
create table t as select * from all_objects;
alter session set workarea_size_policy = manual;
alter session set sort_area_size = 524288;
select /*+gather_plan_statistics*/ *
from (
select * from t
order by object_name
)
where rownum < 10;
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.06 | 1044 | | | |
|* 1 | COUNT STOPKEY | | 1 | | 9 |00:00:00.06 | 1044 | | | |
| 2 | VIEW | | 1 | 86006 | 9 |00:00:00.06 | 1044 | | | |
|* 3 | SORT ORDER BY STOPKEY| | 1 | 86006 | 9 |00:00:00.06 | 1044 | 14336 | 14336 |12288 (0)|
| 4 | TABLE ACCESS FULL | T | 1 | 86006 | 73593 |00:00:00.03 | 1044 | | | |
---------------------------------------------------------------------------------------------------------------------
--Top-N이 먹히지 않게 쿼리 수정
select /*+gather_plan_statistics*/ *
from (
select rownum no, t.* from t
order by object_name
)
where no < 10;
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.32 | 1065 | 1134 | 1134 | | | | |
|* 1 | VIEW | | 1 | 86006 | 9 |00:00:00.32 | 1065 | 1134 | 1134 | | | | |
| 2 | SORT ORDER BY | | 1 | 86006 | 73593 |00:00:00.36 | 1065 | 1134 | 1134 | 9M| 1232K| 512K (1)| 9216 |
| 3 | COUNT | | 1 | | 73593 |00:00:00.06 | 1044 | 0 | 0 | | | | |
| 4 | TABLE ACCESS FULL| T | 1 | 86006 | 73593 |00:00:00.03 | 1044 | 0 | 0 | | | | |
----------------------------------------------------------------------------------------------------------------------------------------------
역시나 Top-N이 사용되지 않으면 자원을 많이 소모하는 것을 알 수 있다.
h3.(3)분석함수에서의 Top-N 쿼리
- window sort 시에도 rank()나 row_number()를 쓰면 Top-N 쿼리 알고리즘이 작동해 max() 등 함수를 쓸 때보다 소트 부하를 경감시켜 준다.
create table t
as
select 1 id, rownum seq, owner, object_name, object_type, created, status
from all_objects
begin
for i in 1..9
loop
insert into t
select i + 1 id, rownum seq
,owner, object_name, object_type, created, status
from t
where id = 1;
commit;
end loop;
end;
alter session set workarea_size_policy = manual;
alter session set sort_area_size = 1048576;
select /*+gather_plan_statistics*/ 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 t)
where seq = last_seq
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:01.13 | 6648 | 13349 | 6610 | | | | |
|* 1 | VIEW | | 1 | 737K| 10 |00:00:01.13 | 6648 | 13349 | 6610 | | | | |
| 2 | WINDOW SORT | | 1 | 737K| 735K|00:00:02.25 | 6648 | 13349 | 6610 | 58M| 2657K| 1009K (0)| 53248 |
| 3 | TABLE ACCESS FULL| T | 1 | 737K| 735K|00:00:00.19 | 6646 | 0 | 0 | | | | |
---------------------------------------------------------------------------------------------------------------------------------------------
select /*+gather_plan_statistics*/ 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 t)
where rnum = 1
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:02.00 | 6730 | 82 | 82 | | | | |
|* 1 | VIEW | | 1 | 737K| 10 |00:00:02.00 | 6730 | 82 | 82 | | | | |
|* 2 | WINDOW SORT PUSHED RANK| | 1 | 737K| 20 |00:00:02.00 | 6730 | 82 | 82 | 729K| 493K| 1032K (2)| 1024 |
| 3 | TABLE ACCESS FULL | T | 1 | 737K| 735K|00:00:00.23 | 6646 | 0 | 0 | | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
rank로 바꾸었더니 reads, writes, OMem 값이 엄청 줄어들었다.
"구루비 주주클럽 스터디모임" 에서 2016년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3375
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.