In-Memory CDT 기능과 스칼라 서브 쿼리

12.2 버전에 WITH 절에 기술한 서브 쿼리를 메모리에 저장하는 In-Memory Cursor Duration Temp Tables 기능이 추가되었다. 이를 활용하면 스칼라 서브 쿼리를 PGA로 저장한 후 액세스할 수 있을 것이라 예상되어 테스트를 수행해보았다. 

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

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 AS SELECT ROWNUM AS c1, ROWNUM AS c2 FROM XMLTABLE ('1 to 10000');

아래는 스칼라 서브 쿼리의 실행계획이다. 쿼리 수행에 3.73초가 소요되었고, t2 테이블의 반복 액세스로 인해 과도한 블록 I/O가 발생했다.

-- 2
SELECT (SELECT x.c2 FROM t2 x WHERE x.c1 = a.c1)
  FROM t1 a;

---------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |  10000 |00:00:00.01 |     128 |
|*  1 |  TABLE ACCESS FULL| T2   |  10000 |  10000 |00:00:03.73 |     260K|
|   2 |  TABLE ACCESS FULL| T1   |      1 |  10000 |00:00:00.01 |     128 |
---------------------------------------------------------------------------

In-Memory Cursor Duration Temp Tables 기능은 _in_memory_cdt 파라미터로 제어할 수 있다.

-- 3
NAME           DESCRIPTION
-------------- -------------
_in_memory_cdt In Memory CDT

NAME           ORDINAL VALUE   ISDEFAULT
-------------- ------- ------- ---------
_in_memory_cdt       1 ON      FALSE
_in_memory_cdt       2 OFF     FALSE
_in_memory_cdt       3 LIMITED TRUE

아래는 In-Memory Cursor Duration Temp Tables 기능을 사용한 쿼리의 실행계획이다. 블록 I/O가 발생하지 않았지만, CDT를 비효율적으로 액세스하여 쿼리 수행에 13.69초가 소요되었다.

-- 4
ALTER SESSION SET "_in_memory_cdt" = ON;

WITH w1 AS (SELECT /*+ MATERIALIZE */ * FROM t2)
SELECT (SELECT x.c2 FROM w1 x WHERE x.c1 = a.c1)
  FROM t1 a;

------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name     | Starts | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |          |      1 |  10000 |00:00:00.02 |     160 |
|*  1 |  VIEW                                    |          |  10000 |  10000 |00:00:13.67 |       0 | -- !
|   2 |   TABLE ACCESS FULL                      | SYS_TEMP |  10000 |    100M|00:00:06.91 |       0 | -- !
|   3 |  TEMP TABLE TRANSFORMATION               |          |      1 |  10000 |00:00:00.02 |     160 |
|   4 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP |      1 |      0 |00:00:00.02 |      31 |
|   5 |    TABLE ACCESS FULL                     | T2       |      1 |  10000 |00:00:00.01 |      30 |
|   6 |   TABLE ACCESS FULL                      | T1       |      1 |  10000 |00:00:00.01 |     128 |
------------------------------------------------------------------------------------------------------

아래는 이전 방식으로 수행한 결과다. 동일한 현상이 발생하는 것을 확인할 수 있다.

-- 5
ALTER SESSION SET "_in_memory_cdt" = OFF;

WITH w1 AS (SELECT /*+ MATERIALIZE */ * FROM t2)
SELECT (SELECT x.c2 FROM w1 x WHERE x.c1 = a.c1)
  FROM t1 a;

----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Starts | A-Rows |   A-Time   | Buffers | Reads  | Writes |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |      1 |  10000 |00:00:00.02 |     185 |      0 |     18 |
|*  1 |  VIEW                      |          |  10000 |  10000 |00:00:13.73 |     260K|     18 |      0 | -- !
|   2 |   TABLE ACCESS FULL        | SYS_TEMP |  10000 |    100M|00:00:06.94 |     260K|     18 |      0 | -- !
|   3 |  TEMP TABLE TRANSFORMATION |          |      1 |  10000 |00:00:00.02 |     185 |      0 |     18 |
|   4 |   LOAD AS SELECT           | SYS_TEMP |      1 |      0 |00:00:00.01 |      51 |      0 |     18 |
|   5 |    TABLE ACCESS FULL       | T2       |      1 |  10000 |00:00:00.01 |      30 |      0 |      0 |
|   6 |   TABLE ACCESS FULL        | T1       |      1 |  10000 |00:00:00.01 |     128 |      0 |      0 |
----------------------------------------------------------------------------------------------------------

결론적으로 CDT는 스캔 방식으로 액세스하기 위해 구현된 기능으로 보이며, 반복 액세스가 필요한 스칼라 서브 쿼리에 사용하지 않는 편이 바람직할 것으로 판단된다.