UPDATE 문 성능 개선 사례

이번 글에서는 다소 당황스러운 UPDATE 문 성능 개선 사례를 살펴보자.

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

1 2 3 4

-- 1 DROP TABLE t1 PURGE; CREATE TABLE t1 (c1 NUMBER, c2 NUMBER, c3 NUMBER, c4 NUMBER, c5 NUMBER, c6 NUMBER); ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (c1, c2);

아래가 문제의 쿼리다. 무의미한 서브 쿼리가 2번 사용되었고, PARALLEL 힌트에 테이블을 지정하지 않았고, PDML도 동작하지 않았다.

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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

-- 2 UPDATE t1 a    SET a.c6 = (SELECT /*+ FULL(X) PARALLEL(2) */                       x.c4 / x.c5                  FROM t1 x                 WHERE x.c1 = a.c1                   AND x.c2 = a.c2                   AND x.c3 > 0                   AND x.c4 > x.c5)  WHERE EXISTS (SELECT 1                  FROM t1 y                 WHERE y.c1 = a.c1                   AND y.c2 = a.c2                   AND y.c3 > 0                   AND y.c4 > y.c5);   --------------------------------------------------------------------------------- | Id  | Operation                       | Name     |    TQ  |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------- |   0 | UPDATE STATEMENT                |          |        |      |            | |   1 |  UPDATE                         | T1       |        |      |            | |   2 |   PX COORDINATOR                |          |        |      |            | |   3 |    PX SEND QC (RANDOM)          | :TQ10000 |  Q1,00 | P->S | QC (RAND)  | |   4 |     NESTED LOOPS SEMI           |          |  Q1,00 | PCWP |            | |   5 |      PX BLOCK ITERATOR          |          |  Q1,00 | PCWC |            | |   6 |       TABLE ACCESS FULL         | T1       |  Q1,00 | PCWP |            | |*  7 |      TABLE ACCESS BY INDEX ROWID| T1       |  Q1,00 | PCWP |            | |*  8 |       INDEX UNIQUE SCAN         | T1_PK    |  Q1,00 | PCWP |            | |*  9 |   TABLE ACCESS FULL             | T1       |        |      |            | ---------------------------------------------------------------------------------   Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------      1 - SEL$10439C08    6 - SEL$10439C08 / A@UPD$1    7 - SEL$10439C08 / Y@SEL$2    8 - SEL$10439C08 / Y@SEL$2    9 - SEL$1        / X@SEL$1   Note -----    - Degree of Parallelism is 2 because of hint    - PDML is disabled in current session

PDML 여부를 확인한 후 아래처럼 수정할 것을 권고했다.

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

-- 3 UPDATE /*+ ENABLE_PARALLEL_DML FULL(T1) PARALLEL(T1 2) */        t1    SET c6 = c4 / c5  WHERE c3 > 0    AND c4 > c5;   -------------------------------------------------------------------------- | Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib | -------------------------------------------------------------------------- |   0 | UPDATE STATEMENT         |          |        |      |            | |   1 |  PX COORDINATOR          |          |        |      |            | |   2 |   PX SEND QC (RANDOM)    | :TQ10001 |  Q1,01 | P->S | QC (RAND)  | |   3 |    INDEX MAINTENANCE     | T1       |  Q1,01 | PCWP |            | |   4 |     PX RECEIVE           |          |  Q1,01 | PCWP |            | |   5 |      PX SEND RANGE       | :TQ10000 |  Q1,00 | P->P | RANGE      | |   6 |       UPDATE             | T1       |  Q1,00 | PCWP |            | |   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            | |*  8 |         TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            | --------------------------------------------------------------------------   Note -----    - Degree of Parallelism is 2 because of table property