행 복제 성능 저하 사례

12.2 버전에서 아우터 조인으로 행 복제를 수행하면 쿼리의 성능이 저하될 수 있다. 관련 내용을 살펴보자.

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

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 1000000');

아래가 문제의 쿼리다. 11.2 버전에서는 소트 머지 조인으로 인해 계층 전개를 1번만 수행한 것을 확인할 수 있다.(Starts = 1)

-- 2 : 11.2
SELECT COUNT (*)
  FROM t1
  LEFT OUTER
  JOIN (SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL <= 10)
    ON 1 = 1;

---------------------------------------------------------------------------------
| Id  | Operation                         | Name | Starts | A-Rows |   A-Time   |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |      1 |      1 |00:00:03.13 |
|   1 |  SORT AGGREGATE                   |      |      1 |      1 |00:00:03.13 |
|   2 |   MERGE JOIN OUTER                |      |      1 |     10M|00:00:02.85 |
|   3 |    TABLE ACCESS FULL              | T1   |      1 |   1000K|00:00:00.37 |
|   4 |    BUFFER SORT                    |      |   1000K|     10M|00:00:01.29 |
|   5 |     VIEW                          |      |      1 |     10 |00:00:00.01 | -- !
|   6 |      VIEW                         |      |      1 |     10 |00:00:00.01 |
|   7 |       CONNECT BY WITHOUT FILTERING|      |      1 |     10 |00:00:00.01 |
|   8 |        FAST DUAL                  |      |      1 |      1 |00:00:00.01 |
---------------------------------------------------------------------------------

아래는 12.2 버전의 실행계획이다. 12.2 버전도 소트 머지 조인으로 조인했지만 Lateral 뷰로 인해 계층 전개가 100만번 수행된 것을 확인할 수 있다.(START = 1000K) 이로 인해 과도한 수행 시간이 소요되었다.

-- 3 : 12.2
SELECT COUNT (*)
  FROM t1
  LEFT OUTER
  JOIN (SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL <= 10)
    ON 1 = 1;

--------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Starts | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |      1 |      1 |00:00:14.29 |
|   1 |  SORT AGGREGATE                   |                 |      1 |      1 |00:00:14.29 |
|   2 |   MERGE JOIN OUTER                |                 |      1 |     10M|00:00:13.84 |
|   3 |    TABLE ACCESS FULL              | T1              |      1 |   1000K|00:00:00.45 |
|   4 |    BUFFER SORT                    |                 |   1000K|     10M|00:00:12.03 |
|   5 |     VIEW                          | VW_LAT_5B1BC3A4 |   1000K|     10M|00:00:08.29 | -- !
|   6 |      VIEW                         |                 |   1000K|     10M|00:00:06.70 |
|   7 |       CONNECT BY WITHOUT FILTERING|                 |   1000K|     10M|00:00:05.34 |
|   8 |        FAST DUAL                  |                 |   1000K|   1000K|00:00:00.19 |
--------------------------------------------------------------------------------------------

3번 쿼리는 12.2 버전에서 아래와 같이 변환된다. 바람직하지 못한 쿼리 변환으로 판단된다.

-- 4
SELECT COUNT(*)
  FROM t1
     , LATERAL (SELECT 0 FROM (SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL<=10))(+) VW_LAT_5B1BC3A4;

해법은 단순하다. INNER JOIN이나 CROSS JOIN을 사용하면 된다.

-- 5-1
SELECT /*+ LEADING(T1) */
       COUNT (*)
  FROM t1
 INNER
  JOIN (SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL <= 10)
    ON 1 = 1;

--------------------------------------------------------------------------------
| Id  | Operation                        | Name | Starts | A-Rows |   A-Time   |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |      1 |      1 |00:00:02.87 |
|   1 |  SORT AGGREGATE                  |      |      1 |      1 |00:00:02.87 |
|   2 |   MERGE JOIN CARTESIAN           |      |      1 |     10M|00:00:02.57 |
|   3 |    TABLE ACCESS FULL             | T1   |      1 |   1000K|00:00:00.31 |
|   4 |    BUFFER SORT                   |      |   1000K|     10M|00:00:01.20 |
|   5 |     VIEW                         |      |      1 |     10 |00:00:00.01 | -- !
|   6 |      CONNECT BY WITHOUT FILTERING|      |      1 |     10 |00:00:00.01 |
|   7 |       FAST DUAL                  |      |      1 |      1 |00:00:00.01 |
--------------------------------------------------------------------------------

-- 5-2
SELECT /*+ LEADING(T1) */
       COUNT (*)
  FROM t1
 CROSS
  JOIN (SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL <= 10);

--------------------------------------------------------------------------------
| Id  | Operation                        | Name | Starts | A-Rows |   A-Time   |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |      1 |      1 |00:00:02.82 |
|   1 |  SORT AGGREGATE                  |      |      1 |      1 |00:00:02.82 |
|   2 |   MERGE JOIN CARTESIAN           |      |      1 |     10M|00:00:02.52 |
|   3 |    TABLE ACCESS FULL             | T1   |      1 |   1000K|00:00:00.30 |
|   4 |    BUFFER SORT                   |      |   1000K|     10M|00:00:01.18 |
|   5 |     VIEW                         |      |      1 |     10 |00:00:00.01 | -- !
|   6 |      CONNECT BY WITHOUT FILTERING|      |      1 |     10 |00:00:00.01 |
|   7 |       FAST DUAL                  |      |      1 |      1 |00:00:00.01 |
--------------------------------------------------------------------------------