중복 표현식

불필요한 CPU 연산은 쿼리의 성능을 저하시킬 수 있다. 대표적인 경우가 중복 표현식을 사용하는 것이다. 관련 내용을 살펴보자.

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

1 2 3 4 5 6

-- 1 DROP TABLE t1 PURGE; DROP TABLE t2 PURGE;   CREATE TABLE t1 AS SELECT 1 AS c1, 1 AS c2 FROM XMLTABLE ('1 to 10'); CREATE TABLE t2 AS SELECT 1 AS c1, 1 AS c2, 1 AS c3 FROM XMLTABLE ('1 to 10000000');

아래 쿼리는 POWER (a.c1, a.c2) 표현식을 중복 사용했다. t1 테이블이 10건, t2 테이블이 천만건이고, 표현식을 3번 기술했으므로 동일한 표현식이 3억회나 수행된다. 표현식 수행에 69.67초가 소요된 것을 확인할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

-- 2 SELECT SUM (c1 + c2 + c3)   FROM (SELECT /*+ NO_MERGE */                POWER (a.c1, a.c2) + b.c1 AS c1              , POWER (a.c1, a.c2) + b.c2 AS c2              , POWER (a.c1, a.c2) + b.c3 AS c3           FROM t1 a              , t2 b);   -------------------------------------------------------------------------------- | Id  | Operation              | Name | Starts | A-Rows |   A-Time   | Buffers | -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT       |      |      1 |      1 |00:01:53.76 |   19389 | |   1 |  SORT AGGREGATE        |      |      1 |      1 |00:01:53.76 |   19389 | |   2 |   VIEW                 |      |      1 |    100M|00:01:38.74 |   19389 | -- 69.67 |   3 |    MERGE JOIN CARTESIAN|      |      1 |    100M|00:00:29.07 |   19389 | |   4 |     TABLE ACCESS FULL  | T1   |      1 |     10 |00:00:00.01 |       3 | |   5 |     BUFFER SORT        |      |     10 |    100M|00:00:21.44 |   19386 | |   6 |      TABLE ACCESS FULL | T2   |      1 |     10M|00:00:00.76 |   19384 | --------------------------------------------------------------------------------

아래 쿼리는 POWER (c1, c2) 표현식을 인라인 뷰에 기술했다. 뷰 머지를 방지하기 위해 NO_MERGE 힌트를 사용했다. t1 테이블이 10건이므로 표현식이 10회만 수행된다. 표현식 수행에 22.54초가 소요된 것을 확인할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

-- 3 SELECT SUM (c1 + c2 + c3)   FROM (SELECT /*+ NO_MERGE */                a.c1 + b.c1 AS c1              , a.c1 + b.c2 AS c2              , a.c1 + b.c3 AS c3           FROM (SELECT /*+ NO_MERGE */ POWER (c1, c2) AS c1 FROM t1) a              , t2 b);   -------------------------------------------------------------------------------- | Id  | Operation              | Name | Starts | A-Rows |   A-Time   | Buffers | -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT       |      |      1 |      1 |00:01:06.72 |   19389 | |   1 |  SORT AGGREGATE        |      |      1 |      1 |00:01:06.72 |   19389 | |   2 |   VIEW                 |      |      1 |    100M|00:00:52.04 |   19389 | -- 22.54 |   3 |    MERGE JOIN CARTESIAN|      |      1 |    100M|00:00:29.50 |   19389 | |   4 |     VIEW               |      |      1 |     10 |00:00:00.01 |       3 | |   5 |      TABLE ACCESS FULL | T1   |      1 |     10 |00:00:00.01 |       3 | |   6 |     BUFFER SORT        |      |     10 |    100M|00:00:21.71 |   19386 | |   7 |      TABLE ACCESS FULL | T2   |      1 |     10M|00:00:00.75 |   19384 | --------------------------------------------------------------------------------

SQL Monitor 레포트를 확인해보면 2번 쿼리의 CPU Activity가 3번 쿼리의 값보다 큰 것을 확인할 수 있다. CPU Activity가 과도하게 높은 오퍼레이션에 대해서는 중복 표현식이 사용되지 않았는지 확인해 볼 필요가 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

-- 4-1: 2 ========================================================================================== | Id |        Operation         | Name |   Time    | Start  | Activity | Activity Detail | |    |                          |      | Active(s) | Active |   (%)    |   (# samples)   | ========================================================================================== |  0 | SELECT STATEMENT         |      |       112 |     +4 |          |                 | |  1 |   SORT AGGREGATE         |      |       112 |     +4 |    22.12 | Cpu (25)        | |  2 |    VIEW                  |      |       112 |     +4 |    61.06 | Cpu (69)        | -- ! |  3 |     MERGE JOIN CARTESIAN |      |       112 |     +4 |     3.54 | Cpu (4)         | |  4 |      TABLE ACCESS FULL   | T1   |       112 |     +4 |          |                 | |  5 |      BUFFER SORT         |      |       113 |     +3 |    12.39 | Cpu (14)        | |  6 |       TABLE ACCESS FULL  | T2   |         3 |     +2 |     0.88 | Cpu (1)         | ==========================================================================================   -- 4-2: 3 ========================================================================================== | Id |        Operation         | Name |   Time    | Start  | Activity | Activity Detail | |    |                          |      | Active(s) | Active |   (%)    |   (# samples)   | ========================================================================================== |  0 | SELECT STATEMENT         |      |        63 |     +4 |          |                 | |  1 |   SORT AGGREGATE         |      |        64 |     +4 |    16.42 | Cpu (11)        | |  2 |    VIEW                  |      |        63 |     +4 |    49.25 | Cpu (33)        | -- ! |  3 |     MERGE JOIN CARTESIAN |      |        63 |     +4 |    13.43 | Cpu (9)         | |  4 |      VIEW                |      |        59 |     +4 |          |                 | |  5 |       TABLE ACCESS FULL  | T1   |        63 |     +4 |          |                 | |  6 |      BUFFER SORT         |      |        66 |     +1 |    20.90 | Cpu (14)        | |  7 |       TABLE ACCESS FULL  | T2   |         1 |     +4 |          |                 | ==========================================================================================