꿈꾸는 개발자, DBA 커뮤니티 구루비

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 값이 엄청 줄어들었다.

6. Sort Area를 적게 사용하도록 SQL 작성 (by hrson81) [2017.06.23]