by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

6. Sort Area를 적게 사용하도록 SQL 작성




Sort Area를 적게 사용하도록 SQL 작성

  1. 소트 연산이 불가피 할 경우, 메모리 내에서 처리 완료해야 함
  2. sort area 적게 사용하는 방법
    1. 소트를 완료하고 나서 데이터 가공
    2. Top-N쿼리 : 소트 연산(=값 비교) 횟수 최소화 및 sort area 사용량 감소

소트를 완료하고 나서 데이터 가공하기

  1. 사례 : 1번 쿼리와 2번 쿼리 중 sort area를 적게 사용하는 쿼리는?
    1. 결론
      1. 1번 쿼리 : 가공된 결과치를 sort area를 담음
      2. 2번 쿼리 : 가공되지 않은 상태로 정렬 완료하고 최종 출력시 가공하므로 sort area 적게 사용
    2. 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 상품번호;
      
    3. 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쿼리

  1. 특징 : 소트 연산(=값 비교) 횟수를 최소화하고, sort area 사용량 감소 가능
    • [종목코드 + 거래일시] 순으로 구성된 인덱스가 존재한다면 옵티마이저는 그 인덱스를 이용함으로써 order by 연산 대체 가능
    • rownum 조건을 사용해 N건에서 멈추도록 했으므로 조건절에 부합하는 레코드가 아무리 많아도 매우 빠른 수행 속도 낼 수 있음 ==> 실행계획 상 "count stopkey"
    • TOP-N쿼리의 소트 부하 경감 원리 : [종목코드 + 거래일시] 순으로 구성된 인덱스가 존재하지 않았을 경우
    • rownum <= 10 : 우선 10개 레코드를 담을 배열 할당 ⇒ 처음 읽은 10개 레코드를 정렬된 상태로 담기
      • 이후 읽는 레코드는 맨 우측에 있는 값(=가장 큰 값)과 비교 : 그보다 작은 값이 나타날 때만 배열 내에서 다시 정렬 시도하고, 맨 우측 값은 버림
      • 이 과정 반복 : 전체 레코드를 정렬하지 않고 오름차순(ASC)으로 최소값을 갖는 10개 레코드를 정확히 찾아냄
    1. SQL Server or Sybase에서 TOP-N 쿼리
      SELECT TOP 10 거래일시, 채결건수, 체수량, 거래대금
      FROM   시간대별종목거래
      WHERE  종목코드 = 'KR123456'
      AND    거래일시 >= '20080304';
      
    2. IBM DB2에서 TOP-N 쿼리
      SELECT 거래일시, 채결건수, 체수량, 거래대금
      FROM   시간대별종목거래
      WHERE  종목코드 = 'KR123456'
      AND    거래일시 >= '20080304'
      ORDER  거래일시
      FETCH  FIRST 10 ROWS ONLY;
      
    3. 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))
      
    4. 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
      
    5. 효과 측정 : 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
      
    6. 효과 측정 : 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
      
  2. 분석함수에서 TOP-N 쿼리
    1. 특징 : window sort 시에도 rank(), row_number()를 사용하면 TOP-N쿼리 알고리즘 작동 : max() 등 함수 사용할 때보다 소트 부하 경감
    2. 마지막 이력 레코드 찾는 쿼리 : 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)
      
    3. 마지막 이력 레코드 찾는 쿼리 : 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)
      

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.