WITH 이용한 TEMP TABLE 사용 테스트

DBMS : oracle 19.3

 

- WITH 이용한 TEMP TABLE 사용 테스트 - "_in_memory_cdt" 파라미터로 /*+ materialize */ 힌트 사용하여도 물리적으로 테이블 생성하지 않는 경우 있다 - ALTER SESSION SET "_in_memory_cdt" = OFF ; 명령으로 제어 가능(ON/OFF) - xplan 에서 reads 가 buffers 가 많은 것은 temp table i/o는 direct path i/o 여서 SGA 를 경유하지 않음(즉 buffers + reads 가 총 i/o)

---

with 문으로 temp table 만들기

WITH T1 AS (SELECT /*+ MATERIALIZE */ * FROM SK.TAB1 ) 
SELECT (SELECT COUNT(*) FROM T1 WHERE OBJECT_ID = A.OBJECT_ID)
FROM SK.TAB1 A, T1 B
WHERE A.OBJECT_ID = B.OBJECT_ID
AND ROWNUM <= 100
;

-- CURSOR DURATION MEMORY 가 보이고 물리적인 임시테이블 사용이 보이지 않는다
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation				 | Name 		      | Starts | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT			 |			      |      1 |    100 |00:00:00.06 |	  2845 |       |       |	  |
|   1 |  SORT AGGREGATE 			 |			      |    100 |    100 |00:00:01.87 |	     0 |       |       |	  |
|*  2 |   VIEW					 |			      |    100 |    100 |00:00:01.87 |	     0 |       |       |	  |
|   3 |    TABLE ACCESS FULL			 | SYS_TEMP_0FD9D6782_103AB50 |    100 |   7307K|00:00:01.11 |	     0 |       |       |	  |
|   4 |  TEMP TABLE TRANSFORMATION		 |			      |      1 |    100 |00:00:00.06 |	  2845 |       |       |	  |
|   5 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6782_103AB50 |      1 |      0 |00:00:00.05 |	  1422 |  1024 |  1024 |	  |
|   6 |    TABLE ACCESS FULL			 | TAB1 		      |      1 |  73079 |00:00:00.01 |	  1421 |       |       |	  |
|*  7 |   COUNT STOPKEY 			 |			      |      1 |    100 |00:00:00.01 |	  1422 |       |       |	  |
|*  8 |    HASH JOIN				 |			      |      1 |    100 |00:00:00.01 |	  1422 |  5198K|  3201K| 4828K (0)|
|   9 |     TABLE ACCESS FULL			 | TAB1 		      |      1 |  73079 |00:00:00.01 |	  1422 |       |       |	  |
|  10 |     VIEW				 |			      |      1 |    100 |00:00:00.01 |	     0 |       |       |	  |
|  11 |      TABLE ACCESS FULL			 | SYS_TEMP_0FD9D6782_103AB50 |      1 |    100 |00:00:00.01 |	     0 |       |       |	  |
---------------------------------------------------------------------------------------------------------------------------------------------------

"_in_memory_cdt" 파라미터 값 확인. ON(기본값)으로 설정되어있다

SQL> SELECT KSPPINM NAME,
       KSPPSTVL VALUE,
       KSPPSTDF IS_DEFAULT
  FROM SYS.X$KSPPI I,
       SYS.X$KSPPCV V
 WHERE I.INDX = V.INDX
   AND I.KSPPINM LIKE '_in_memory_cdt'
;

NAME			       VALUE			      IS_DEFAULT
------------------------------ ------------------------------ --------------------
_in_memory_cdt		       ON			      TRUE

현재 세션에서 _in_memory_cdt 파라미터 OFF로 변경

SQL> ALTER SESSION SET "_in_memory_cdt" = OFF ;

-- 확인
SELECT KSPPINM NAME,
       KSPPSTVL VALUE,
       KSPPSTDF IS_DEFAULT
  FROM SYS.X$KSPPI I,
       SYS.X$KSPPCV V
 WHERE I.INDX = V.INDX
   AND I.KSPPINM LIKE '_in_memory_cdt'
;

NAME			       VALUE			      IS_DEFAULT
------------------------------ ------------------------------ --------------------
_in_memory_cdt		       OFF			      TRUE

SQL 실행

WITH T1 AS (SELECT /*+ MATERIALIZE */ * FROM SK.TAB1 ) 
SELECT (SELECT COUNT(*) FROM T1 WHERE OBJECT_ID = A.OBJECT_ID)
FROM SK.TAB1 A, T1 B
WHERE A.OBJECT_ID = B.OBJECT_ID
AND ROWNUM <= 100
;

-- 물리적인 임시테이블 생성 후 사용하였다.
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		   | Name			| Starts | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	   |				|      1 |    100 |00:00:00.08 |    4304 |     64 |   1416 |	   |	   |	      |
|   1 |  SORT AGGREGATE 	   |				|    100 |    100 |00:00:01.48 |     141K|   1416 |	 0 |	   |	   |	      |
|*  2 |   VIEW			   |				|    100 |    100 |00:00:01.48 |     141K|   1416 |	 0 |	   |	   |	      |
|   3 |    TABLE ACCESS FULL	   | SYS_TEMP_0FD9D6774_103AB50 |    100 |   7307K|00:00:00.74 |     141K|   1416 |	 0 |	   |	   |	      |
|   4 |  TEMP TABLE TRANSFORMATION |				|      1 |    100 |00:00:00.08 |    4304 |     64 |   1416 |	   |	   |	      |
|   5 |   LOAD AS SELECT	   | SYS_TEMP_0FD9D6774_103AB50 |      1 |	0 |00:00:00.07 |    2862 |	0 |   1416 |  2070K|  2070K|	      |
|   6 |    TABLE ACCESS FULL	   | TAB1			|      1 |  73079 |00:00:00.01 |    1421 |	0 |	 0 |	   |	   |	      |
|*  7 |   COUNT STOPKEY 	   |				|      1 |    100 |00:00:00.01 |    1436 |     64 |	 0 |	   |	   |	      |
|*  8 |    HASH JOIN		   |				|      1 |    100 |00:00:00.01 |    1436 |     64 |	 0 |  5198K|  3201K| 4829K (0)|
|   9 |     TABLE ACCESS FULL	   | TAB1			|      1 |  73079 |00:00:00.01 |    1422 |	0 |	 0 |	   |	   |	      |
|  10 |     VIEW		   |				|      1 |    100 |00:00:00.01 |      14 |     64 |	 0 |	   |	   |	      |
|  11 |      TABLE ACCESS FULL	   | SYS_TEMP_0FD9D6774_103AB50 |      1 |    100 |00:00:00.01 |      14 |     64 |	 0 |	   |	   |	      |
-------------------------------------------------------------------------------------------------------------------------------------------------------

인라인뷰로 감싼 후 SQL 실행

WITH T1 AS (SELECT /*+ MATERIALIZE */ * FROM SK.TAB1 ) 
SELECT * FROM (SELECT (SELECT COUNT(*) FROM T1 WHERE OBJECT_ID = A.OBJECT_ID)
FROM SK.TAB1 A, T1 B
WHERE A.OBJECT_ID = B.OBJECT_ID
AND ROWNUM <= 100
)
;

-- 스칼라서브쿼리의 buffers 까지 합산되어 최종 결과가 보여진다
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		   | Name			| Starts | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	   |				|      1 |    100 |00:00:01.86 |     146K|   1480 |   1416 |	   |	   |	      |
|   1 |  SORT AGGREGATE 	   |				|    100 |    100 |00:00:01.78 |     141K|   1416 |	 0 |	   |	   |	      |
|*  2 |   VIEW			   |				|    100 |    100 |00:00:01.78 |     141K|   1416 |	 0 |	   |	   |	      |
|   3 |    TABLE ACCESS FULL	   | SYS_TEMP_0FD9D677D_103AB50 |    100 |   7307K|00:00:01.04 |     141K|   1416 |	 0 |	   |	   |	      |
|   4 |  TEMP TABLE TRANSFORMATION |				|      1 |    100 |00:00:01.86 |     146K|   1480 |   1416 |	   |	   |	      |
|   5 |   LOAD AS SELECT	   | SYS_TEMP_0FD9D677D_103AB50 |      1 |	0 |00:00:00.07 |    2863 |	0 |   1416 |  2070K|  2070K|	      |
|   6 |    TABLE ACCESS FULL	   | TAB1			|      1 |  73079 |00:00:00.01 |    1422 |	0 |	 0 |	   |	   |	      |
|   7 |   VIEW			   |				|      1 |    100 |00:00:01.79 |     143K|   1480 |	 0 |	   |	   |	      |
|*  8 |    COUNT STOPKEY	   |				|      1 |    100 |00:00:00.01 |    1435 |     64 |	 0 |	   |	   |	      |
|*  9 |     HASH JOIN		   |				|      1 |    100 |00:00:00.01 |    1435 |     64 |	 0 |  5198K|  3201K| 4829K (0)|
|  10 |      TABLE ACCESS FULL	   | TAB1			|      1 |  73079 |00:00:00.01 |    1421 |	0 |	 0 |	   |	   |	      |
|  11 |      VIEW		   |				|      1 |    100 |00:00:00.01 |      14 |     64 |	 0 |	   |	   |	      |
|  12 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D677D_103AB50 |      1 |    100 |00:00:00.01 |      14 |     64 |	 0 |	   |	   |	      |
-------------------------------------------------------------------------------------------------------------------------------------------------------