EXPRESSION EVALUATION 오퍼레이션

병렬 쿼리에 스칼라 서브 쿼리를 사용하면 쿼리의 성능이 저하될 수 있다. 이에 대한 개선책으로 12.1 버전에 EXPRESSION EVALUATION 오퍼레이션이 추가되었다. 해당 오퍼레이션은 병렬 서버가 스칼라 서브 쿼리를 수행한다.

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

1 2 3 4 5 6 7 8

-- 1 DROP TABLE t1 PURGE; DROP TABLE t2 PURGE; DROP TABLE t3 PURGE;   CREATE TABLE t1 AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 10000'); CREATE TABLE t2 AS SELECT ROWNUM AS c1, 1 AS c2 FROM XMLTABLE ('1 to 10000'); CREATE TABLE t3 AS SELECT * FROM t2;

아래는 병렬 쿼리에 스칼라 서브 쿼리를 사용한 쿼리다. 11.2 버전의 실행계획에서 QC가 스칼라 서브 쿼리를 수행한 것을 확인할 수 있다. 수행에 12.10초가 소요되었고, 400K의 블록 I/O가 발생했다.

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 45 46 47 48 49 50 51 52 53 54

-- 2 : 11.2 SELECT /*+ PARALLEL(A 2) */        a.c1      , (SELECT x.c2 FROM t2 x WHERE x.c1 = a.c1) AS c2      , (SELECT x.c2 FROM t3 x WHERE x.c1 = a.c1) AS c3   FROM t1 a;   10000 행이 선택되었습니다.   경   과: 00:00:12.10   ---------------------------------------------------------------------- | Id  | Operation            | Name     |    TQ  |IN-OUT| PQ Distrib | ---------------------------------------------------------------------- |   0 | SELECT STATEMENT     |          |        |      |            | |*  1 |  TABLE ACCESS FULL   | T2       |        |      |            | -- ! |*  2 |  TABLE ACCESS FULL   | T3       |        |      |            | -- ! |   3 |  PX COORDINATOR      |          |        |      |            | |   4 |   PX SEND QC (RANDOM)| :TQ10000 |  Q1,00 | P->S | QC (RAND)  | |   5 |    PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            | |*  6 |     TABLE ACCESS FULL| T1       |  Q1,00 | PCWP |            | ----------------------------------------------------------------------   Global Stats ==================== | Elapsed | Buffer | | Time(s) |  Gets  | ==================== |      11 |   400K | ====================   Parallel Execution Details (DOP=2 , Servers Allocated=2) ======================================================= |      Name      | Type  | Server# | Elapsed | Buffer | |                |       |         | Time(s) |  Gets  | ======================================================= | PX Coordinator | QC    |         |      12 |   400K | -- ! | p000           | Set 1 |       1 |    0.02 |     32 | | p001           | Set 1 |       2 |    0.02 |     32 | =======================================================   SQL Plan Monitoring Details (Plan Hash Value=2939534272) ================================================================================== | Id |       Operation        |   Name   |   Time    | Start  | Execs |   Rows   | |    |                        |          | Active(s) | Active |       | (Actual) | ================================================================================== |  0 | SELECT STATEMENT       |          |        13 |     +0 | 10000 |    10000 | |  1 |   TABLE ACCESS FULL    | T2       |        13 |     +0 | 10000 |    10000 | -- ! |  2 |   TABLE ACCESS FULL    | T3       |        13 |     +0 | 10000 |    10000 | -- ! |  3 |   PX COORDINATOR       |          |        13 |     +0 |     3 |    10000 | |  4 |    PX SEND QC (RANDOM) | :TQ10000 |         9 |     +2 |     2 |    10000 | |  5 |     PX BLOCK ITERATOR  |          |         9 |     +2 |     2 |    10000 | |  6 |      TABLE ACCESS FULL | T1       |         9 |     +2 |    16 |    10000 | ==================================================================================

아래는 12.2 버전의 실행계획이다. EXPRESSION EVALUATION 오퍼레이션을 확인할 수 있다. 이상한 점은 수행에 14.47초가 소요되었다는 것이다. 블록 I/O도 1M만큼 발생했는데 이는 11.2 버전의 2배에 해당하는 수치다. 단순한 표현의 문제일 수 있지만 11.2 버전보다 많은 시간이 소요되었다는 점에서 오동작을 의심할 수 있다.

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 45 46 47 48 49 50 51 52 53 54 55 56

-- 3 : 12.2 SELECT /*+ PARALLEL(A 2) */        a.c1      , (SELECT x.c2 FROM t2 x WHERE x.c1 = a.c1) AS c2      , (SELECT x.c2 FROM t3 x WHERE x.c1 = a.c1) AS c3   FROM t1 a;   10000 행이 선택되었습니다.   경   과: 00:00:14.47   -------------------------------------------------------------------------- | Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT         |          |        |      |            | |   1 |  PX COORDINATOR          |          |        |      |            | |   2 |   PX SEND QC (RANDOM)    | :TQ10000 |  Q1,00 | P->S | QC (RAND)  | |   3 |    EXPRESSION EVALUATION |          |  Q1,00 | PCWC |            | |   4 |     PX BLOCK ITERATOR    |          |  Q1,00 | PCWC |            | |*  5 |      TABLE ACCESS FULL   | T1       |  Q1,00 | PCWP |            | |*  6 |     TABLE ACCESS FULL    | T2       |        |      |            | -- ! |*  7 |     TABLE ACCESS FULL    | T3       |        |      |            | -- ! --------------------------------------------------------------------------   Global Stats ==================== | Elapsed | Buffer | | Time(s) |  Gets  | ==================== |      21 |     1M | ====================   Parallel Execution Details (DOP=2 , Servers Allocated=2) ======================================================= |      Name      | Type  | Server# | Elapsed | Buffer | |                |       |         | Time(s) |  Gets  | ======================================================= | PX Coordinator | QC    |         |    9.28 |   580K | -- ! | p000           | Set 1 |       1 |    5.56 |   305K | | p001           | Set 1 |       2 |    5.92 |   275K | =======================================================   SQL Plan Monitoring Details (Plan Hash Value=2064967019) ===================================================================================== | Id |         Operation         |   Name   |   Time    | Start  | Execs |   Rows   | |    |                           |          | Active(s) | Active |       | (Actual) | ===================================================================================== |  0 | SELECT STATEMENT          |          |        13 |     +2 |     3 |    10000 | |  1 |   PX COORDINATOR          |          |        11 |     +4 |     3 |    10000 | |  2 |    PX SEND QC (RANDOM)    | :TQ10000 |        12 |     +2 |     2 |    10000 | |  3 |     EXPRESSION EVALUATION |          |        12 |     +2 |     2 |    10000 | |  4 |      PX BLOCK ITERATOR    |          |        12 |     +2 |     2 |    10000 | |  5 |       TABLE ACCESS FULL   | T1       |        12 |     +2 |    16 |    10000 | |  6 |      TABLE ACCESS FULL    | T2       |        14 |     +1 | 20000 |    20000 | -- ! |  7 |      TABLE ACCESS FULL    | T3       |        13 |     +2 | 20000 |    20000 | -- ! =====================================================================================

아울러 12.2 버전의 SQL Monitor 실행계획에서 스칼라 서브 쿼리의 Execs, Rows (Actual) 항목이 20000으로 표시되는데, V$SQL_PLAN_MONITOR 뷰를 조회해보면 QC에 값이 존재하는 것을 확인할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12

-- 4 PROCESS_NAME PLAN_LINE_ID PLAN_OPERATION PLAN_OPTIONS PLAN_OBJECT_NAME STARTS OUTPUT_ROWS ------------ ------------ -------------- ------------ ---------------- ------ ----------- ora                     6 TABLE ACCESS   FULL         T2                10000       10000 -- ! p001                    6 TABLE ACCESS   FULL                            4744        4744 p000                    6 TABLE ACCESS   FULL                            5256        5256   PROCESS_NAME PLAN_LINE_ID PLAN_OPERATION PLAN_OPTIONS PLAN_OBJECT_NAME STARTS OUTPUT_ROWS ------------ ------------ -------------- ------------ ---------------- ------ ----------- ora                     7 TABLE ACCESS   FULL         T3                10000       10000 -- ! p001                    7 TABLE ACCESS   FULL                            4744        4744 p000                    7 TABLE ACCESS   FULL                            5256        5256

아래 쿼리는 스칼라 서브 쿼리를 사용한 병렬 쿼리를 MATERIALIZE시켰다. 11.2 버전의 실행계획을 살펴보면 병렬 서버가 스칼라 서브 쿼리를 수행한 것을 확인할 수 있다. 임시 세그먼트를 병렬로 생성할 수 있기 때문에 이런 동작이 가능한 것으로 보인다. MATERIALIZE 작업이 추가되었지만 3번 쿼리의 절반인 7.03초만에 쿼리가 수행되었다. EXPRESSION EVALUATION 오퍼레이션의 오동작을 방증할 수 있는 예제로 볼 수 있다.

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 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72

-- 5 : 11.2 WITH w1 AS ( SELECT /*+ MATERIALIZE PARALLEL(A 2) */        a.c1      , (SELECT x.c2 FROM t2 x WHERE x.c1 = a.c1) AS c2      , (SELECT x.c2 FROM t3 x WHERE x.c1 = a.c1) AS c3   FROM t1 a) SELECT * FROM w1;   10000 행이 선택되었습니다.   경   과: 00:00:07.03   ---------------------------------------------------------------------------------------------- | Id  | Operation                  | Name                       |    TQ  |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT           |                            |        |      |            | |   1 |  TEMP TABLE TRANSFORMATION |                            |        |      |            | |   2 |   PX COORDINATOR           |                            |        |      |            | |   3 |    PX SEND QC (RANDOM)     | :TQ10000                   |  Q1,00 | P->S | QC (RAND)  | |   4 |     LOAD AS SELECT         |                            |  Q1,00 | PCWP |            | |*  5 |      TABLE ACCESS FULL     | T2                         |  Q1,00 | PCWP |            | -- ! |*  6 |      TABLE ACCESS FULL     | T3                         |  Q1,00 | PCWP |            | -- ! |   7 |      PX BLOCK ITERATOR     |                            |  Q1,00 | PCWC |            | |*  8 |       TABLE ACCESS FULL    | T1                         |  Q1,00 | PCWP |            | |   9 |   PX COORDINATOR           |                            |        |      |            | |  10 |    PX SEND QC (RANDOM)     | :TQ20000                   |  Q2,00 | P->S | QC (RAND)  | |  11 |     VIEW                   |                            |  Q2,00 | PCWP |            | |  12 |      PX BLOCK ITERATOR     |                            |  Q2,00 | PCWC |            | |* 13 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6609_ED7EAB9 |  Q2,00 | PCWP |            | ----------------------------------------------------------------------------------------------   Global Stats ============================ | Elapsed | Buffer | Write | | Time(s) |  Gets  | Bytes | ============================ |      14 |   401K |   3MB | ============================   Parallel Execution Details (DOP=2 , Servers Allocated=4) ======================================================================== |      Name      | Type  | Group# | Server# | Elapsed | Buffer | Write | |                |       |        |         | Time(s) |  Gets  | Bytes | ======================================================================== | PX Coordinator | QC    |        |         |    0.02 |    194 |   1MB | | p000           | Set 1 |      1 |       1 |    6.54 |   190K |   1MB | -- ! | p001           | Set 1 |      1 |       2 |    6.92 |   210K |   1MB | -- ! | p000           | Set 1 |      2 |       1 |    0.02 |    191 |     . | | p001           | Set 1 |      2 |       2 |    0.03 |    270 |     . | ========================================================================   SQL Plan Monitoring Details (Plan Hash Value=4163721023) ========================================================================================================= | Id |          Operation          |            Name            |   Time    | Start  | Execs |   Rows   | |    |                             |                            | Active(s) | Active |       | (Actual) | ========================================================================================================= |  0 | SELECT STATEMENT            |                            |         1 |     +7 |     1 |    10000 | |  1 |   TEMP TABLE TRANSFORMATION |                            |         1 |     +7 |     1 |    10000 | |  2 |    PX COORDINATOR           |                            |         1 |     +7 |     3 |        2 | |  3 |     PX SEND QC (RANDOM)     | :TQ10000                   |         1 |     +7 |     2 |        2 | |  4 |      LOAD AS SELECT         |                            |         6 |     +2 |     2 |        2 | |  5 |       TABLE ACCESS FULL     | T2                         |         7 |     +1 | 10000 |    10000 | -- ! |  6 |       TABLE ACCESS FULL     | T3                         |         6 |     +2 | 10000 |    10000 | -- ! |  7 |       PX BLOCK ITERATOR     |                            |         6 |     +2 |     2 |    10000 | |  8 |        TABLE ACCESS FULL    | T1                         |         6 |     +2 |    16 |    10000 | |  9 |    PX COORDINATOR           |                            |         1 |     +7 |     3 |    10000 | | 10 |     PX SEND QC (RANDOM)     | :TQ20000                   |         1 |     +7 |     2 |    10000 | | 11 |      VIEW                   |                            |         1 |     +7 |     2 |    10000 | | 12 |       PX BLOCK ITERATOR     |                            |         1 |     +7 |     2 |    10000 | | 13 |        TABLE ACCESS FULL    | SYS_TEMP_0FD9D6609_ED7EAB9 |         1 |     +7 |    26 |    10000 | =========================================================================================================

아래는 5번 쿼리의 12.2 버전 실행계획이다. 수행에 6.20초가 소요되었으며, 581K의 블록 I/O가 발생했다. 11.2 버전과 큰 차이가 없는 것을 확인할 수 있다. 아울러 4번 오퍼레이션에서 TEMP SEGMENT MERGE 옵션을 확인할 수 있는데 이는 다수의 임시 세그먼트를 생성하고, 생성이 완료된 후 익스텐트 맵 정보를 수정하여 세그먼트는 병합하는 동작으로 알려져 있다.

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 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72

-- 6 : 12.2 WITH w1 AS ( SELECT /*+ MATERIALIZE PARALLEL(A 2) */        a.c1      , (SELECT x.c2 FROM t2 x WHERE x.c1 = a.c1) AS c2      , (SELECT x.c2 FROM t3 x WHERE x.c1 = a.c1) AS c3   FROM t1 a) SELECT * FROM w1;   10000 행이 선택되었습니다.   경   과: 00:00:06.20   ---------------------------------------------------------------------------------------------------------- | Id  | Operation                              | Name                       |    TQ  |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                       |                            |        |      |            | |   1 |  TEMP TABLE TRANSFORMATION             |                            |        |      |            | |   2 |   PX COORDINATOR                       |                            |        |      |            | |   3 |    PX SEND QC (RANDOM)                 | :TQ10000                   |  Q1,00 | P->S | QC (RAND)  | |   4 |     LOAD AS SELECT (TEMP SEGMENT MERGE)| SYS_TEMP_0FD9D6648_E03C2CE |  Q1,00 | PCWP |            | |*  5 |      TABLE ACCESS FULL                 | T2                         |  Q1,00 | PCWP |            | -- ! |*  6 |      TABLE ACCESS FULL                 | T3                         |  Q1,00 | PCWP |            | -- ! |   7 |      PX BLOCK ITERATOR                 |                            |  Q1,00 | PCWC |            | |*  8 |       TABLE ACCESS FULL                | T1                         |  Q1,00 | PCWP |            | |   9 |   PX COORDINATOR                       |                            |        |      |            | |  10 |    PX SEND QC (RANDOM)                 | :TQ20000                   |  Q2,00 | P->S | QC (RAND)  | |  11 |     VIEW                               |                            |  Q2,00 | PCWP |            | |  12 |      PX BLOCK ITERATOR                 |                            |  Q2,00 | PCWC |            | |* 13 |       TABLE ACCESS FULL                | SYS_TEMP_0FD9D6648_E03C2CE |  Q2,00 | PCWP |            | ----------------------------------------------------------------------------------------------------------   Global Stats ============================ | Elapsed | Buffer | Write | | Time(s) |  Gets  | Bytes | ============================ |      12 |   581K |   3MB | ============================   Parallel Execution Details (DOP=2 , Servers Allocated=4) ======================================================================== |      Name      | Type  | Group# | Server# | Elapsed | Buffer | Write | |                |       |        |         | Time(s) |  Gets  | Bytes | ======================================================================== | PX Coordinator | QC    |        |         |    0.03 |    227 |   1MB | | p000           | Set 1 |      1 |       1 |    6.06 |   305K |   1MB | -- ! | p001           | Set 1 |      1 |       2 |    5.53 |   275K |   1MB | -- ! | p000           | Set 1 |      2 |       1 |    0.02 |    317 |     . | | p001           | Set 1 |      2 |       2 |    0.04 |    378 |     . | ========================================================================   SQL Plan Monitoring Details (Plan Hash Value=2778567849) ====================================================================================================================== | Id |                Operation                 |            Name            |   Time    | Start  | Execs |   Rows   | |    |                                          |                            | Active(s) | Active |       | (Actual) | ====================================================================================================================== |  0 | SELECT STATEMENT                         |                            |         1 |     +6 |     1 |    10000 | |  1 |   TEMP TABLE TRANSFORMATION              |                            |         1 |     +6 |     1 |    10000 | |  2 |    PX COORDINATOR                        |                            |         1 |     +6 |     3 |        4 | |  3 |     PX SEND QC (RANDOM)                  | :TQ10000                   |         2 |     +5 |     2 |        4 | |  4 |      LOAD AS SELECT (TEMP SEGMENT MERGE) | SYS_TEMP_0FD9D6648_E03C2CE |         5 |     +2 |     2 |        4 | |  5 |       TABLE ACCESS FULL                  | T2                         |         6 |     +1 | 10000 |    10000 | -- ! |  6 |       TABLE ACCESS FULL                  | T3                         |         6 |     +1 | 10000 |    10000 | -- ! |  7 |       PX BLOCK ITERATOR                  |                            |         5 |     +2 |     2 |    10000 | |  8 |        TABLE ACCESS FULL                 | T1                         |         5 |     +2 |    16 |    10000 | |  9 |    PX COORDINATOR                        |                            |         1 |     +6 |     3 |    10000 | | 10 |     PX SEND QC (RANDOM)                  | :TQ20000                   |         1 |     +6 |     2 |    10000 | | 11 |      VIEW                                |                            |         1 |     +6 |     2 |    10000 | | 12 |       PX BLOCK ITERATOR                  |                            |         1 |     +6 |     2 |    10000 | | 13 |        TABLE ACCESS FULL                 | SYS_TEMP_0FD9D6648_E03C2CE |         1 |     +6 |    26 |    10000 | ======================================================================================================================

결론은 병렬 쿼리의 EXPRESSION EVALUATION 오퍼레이션은 검증이 필요하며, 이에 대한 개선안으로 MATERIALIZE를 고려할 수 있다는 것이다. 더불어 MATERIALIZE를 활용하면 11.2 버전의 병렬 스칼라 서브 쿼리에 대한 고민도 함께 해결할 수 있을 것으로 보인다.