WINDOW SORT로 인한 성능 저하

비율을 계산하기 위해 분석 함수를 사용하는 경우 WINDOW SORT로 의해 쿼리의 성능이 저하될 수 있다.

예제를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT ROWNUM AS c1
     , CEIL (ROWNUM / 100000) AS c2
     , 1 AS c3
  FROM XMLTABLE ('1 to 10000000');

아래 쿼리는 비율을 계산하기 위해 분석 함수를 사용했다. 17초가 소요되었고, 디스크 소트가 발생했다. WINDOW SORT 과정에서 테이블의 전체 행이 저장되기 때문이다.

-- 2
SELECT /*+ MONITOR */
       MAX (c3 / c3_sum)
  FROM (SELECT c3, SUM (c3) OVER (PARTITION BY c2) AS c3_sum FROM t1);

Global Stats
====================================
| Elapsed | Buffer | Read  | Write |
| Time(s) |  Gets  | Bytes | Bytes |
====================================
|      17 |  23406 | 299MB | 116MB |
====================================

SQL Plan Monitoring Details (Plan Hash Value=1261076573)
======================================================================================================
| Id |       Operation        | Name |   Time    | Start  |   Rows   | Read  | Write |  Mem  | Temp  |
|    |                        |      | Active(s) | Active | (Actual) | Bytes | Bytes | (Max) | (Max) |
======================================================================================================
|  0 | SELECT STATEMENT       |      |        10 |     +8 |        1 |       |       |     . |     . |
|  1 |   SORT AGGREGATE       |      |        10 |     +8 |        1 |       |       |     . |     . |
|  2 |    VIEW                |      |        10 |     +8 |      10M |       |       |     . |     . |
|  3 |     WINDOW SORT        |      |        17 |     +1 |      10M | 116MB | 116MB |  48MB | 117MB |
|  4 |      TABLE ACCESS FULL | T1   |         7 |     +2 |      10M | 183MB |       |     . |     . |
======================================================================================================

아래 쿼리는 집계 후 조인으로 비율을 계산했다. 3.74초가 소요되었고, 2MB의 메모리를 사용했다. 테이블을 2번 액세스하여 블록 I/O가 증가했지만, 집계 결과가 100건에 불과해 메모리 소트로 수행되어 쿼리의 성능이 개선된 것이다.

-- 3
SELECT /*+ MONITOR */
       MAX (a.c3 / b.c3)
  FROM t1 a
     , (SELECT c2, SUM (c3) AS c3 FROM t1 GROUP BY c2) b
 WHERE b.c2 = a.c2;

Global Stats
============================
| Elapsed | Buffer | Read  |
| Time(s) |  Gets  | Bytes |
============================
|    3.74 |  46811 | 365MB |
============================

SQL Plan Monitoring Details (Plan Hash Value=1564293631)
=======================================================================================
| Id |        Operation        | Name |   Time    | Start  |   Rows   | Read  |  Mem  |
|    |                         |      | Active(s) | Active | (Actual) | Bytes | (Max) |
=======================================================================================
|  0 | SELECT STATEMENT        |      |         1 |     +4 |        1 |       |     . |
|  1 |   SORT AGGREGATE        |      |         1 |     +4 |        1 |       |     . |
|  2 |    HASH JOIN            |      |         1 |     +4 |      10M |       |   2MB |
|  3 |     VIEW                |      |         1 |     +4 |      100 |       |     . |
|  4 |      HASH GROUP BY      |      |         3 |     +2 |      100 |       |   2MB |
|  5 |       TABLE ACCESS FULL | T1   |         3 |     +2 |      10M | 183MB |     . |
|  6 |     TABLE ACCESS FULL   | T1   |         2 |     +3 |      10M | 183MB |     . |
=======================================================================================

이런 유형의 쿼리는 블록 I/O와 소트 부하에 따라 분석 함수와 집계 후 조인을 선택적으로 사용할 필요가 있다.