서브쿼리 팩토링 절(WITH 절)이 materialize되면 redo가 발생하고 이로 인해 log file sync 이벤트 대기가 발생할 수 있습니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1) AS SELECT ROWNUM FROM XMLTABLE ('1 to 10000000');
아래 쿼리는 서브쿼리 팩토링 절이 materialize되었습니다. 실행 계획 2번에 LOAD AS SELECT (CURSOR DURATION MEMORY) 오퍼레이션이 표시됩니다. 물리 I/O가 발생하지 않았으므로 12.2 버전에 추가된 In-Memory CDT 기능이 동작한 것을 유추할 수 있습니다.
-- 2
WITH w1 AS (SELECT * FROM t1 WHERE c1 <= 100)
SELECT * FROM w1 WHERE c1 = 1 UNION ALL
SELECT * FROM w1 WHERE c1 = 2;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 15235 |
| 1 | TEMP TABLE TRANSFORMATION | | 2 | 15235 |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6BE2_24EC953 | 0 | 15234 |
|* 3 | TABLE ACCESS FULL | T1 | 100 | 15232 |
| 4 | UNION-ALL | | 2 | 0 |
|* 5 | VIEW | | 1 | 0 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6BE2_24EC953 | 100 | 0 |
|* 7 | VIEW | | 1 | 0 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6BE2_24EC953 | 100 | 0 |
--------------------------------------------------------------------------------------------------
아래 쿼리도 서브쿼리 팩토링 절이 materialize되었습니다. _in_memory_cdt 파라미터를 OFF로 설정하여 실행 계획 2번에서 LOAD AS SELECT 오퍼레이션이 표시됩니다. 물리 I/O가 발생했으므로 데이터가 임시 테이블에 저장된 것을 알 수 있습니다.
-- 3
ALTER SESSION SET "_in_memory_cdt" = 'OFF';
WITH w1 AS (SELECT * FROM t1 WHERE c1 <= 100)
SELECT * FROM w1 WHERE c1 = 1 UNION ALL
SELECT * FROM w1 WHERE c1 = 2;
ALTER SESSION SET "_in_memory_cdt" = ON;
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers | Reads | Writes |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 15249 | 1 | 1 |
| 1 | TEMP TABLE TRANSFORMATION | | 2 | 15249 | 1 | 1 |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6BE0_24EC953 | 0 | 15236 | 0 | 1 |
|* 3 | TABLE ACCESS FULL | T1 | 100 | 15232 | 0 | 0 |
| 4 | UNION-ALL | | 2 | 8 | 1 | 0 |
|* 5 | VIEW | | 1 | 6 | 1 | 0 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6BE0_24EC953 | 100 | 6 | 1 | 0 |
|* 7 | VIEW | | 1 | 2 | 0 | 0 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6BE0_24EC953 | 100 | 2 | 0 | 0 |
------------------------------------------------------------------------------------------------------
아래 쿼리는 INLINE 힌트를 사용하여 서브쿼리 팩토링 절이 인라인 방식으로 동작합니다. 앞선 예제보다 2배 많은 논리 I/O가 발생한 것을 확인할 수 있습니다.
-- 4
WITH w1 AS (SELECT /*+ INLINE */ * FROM t1 WHERE c1 <= 100)
SELECT * FROM w1 WHERE c1 = 1 UNION ALL
SELECT * FROM w1 WHERE c1 = 2;
------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 30465 |
| 1 | UNION-ALL | | 2 | 30465 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 15233 |
|* 3 | TABLE ACCESS FULL| T1 | 1 | 15232 |
------------------------------------------------------
아래는 앞선 3, 4, 5번 쿼리의 세션 통계를 비교한 결과입니다. 서브쿼리 팩토링 절이 materialize되면 In-Memory CDT 동작 여부와 무관하게 redo가 발생하는 것을 확인할 수 있습니다.
-- 5
+---------------------------------------+--------+--------+--------+------+
|NAME | QUERY 2| QUERY 3| QUERY 4|CLASS |
+---------------------------------------+--------+--------+--------+------+
|redo entries | 2.00 | 5.00 | 0.00 |Redo |
|redo size | 376.00 | 948.00 | 0.00 |Redo |
|redo synch writes | 1.00 | 1.00 | 0.00 |Redo |
|redo write info find | 1.00 | 0.00 | 0.00 |Redo |
|redo synch time (usec) | 1.85K| 1.00 | 0.00 |Debug |
|redo synch time overhead (usec) | 58.00 | 0.00 | 0.00 |Debug |
|redo synch time overhead count ( 2ms) | 1.00 | 0.00 | 0.00 |Debug |
+---------------------------------------+--------+--------+--------+------+
참고로 Kun Sun이 작성한 Oracle WITH subquery_factoring_clause TEMP TABLE TRANSFORMATION and log file sync 글에 이 동작에 대한 상세 분석 내용이 포함되어 있습니다. 아울러 오라클 포럼의 long log file sync waits 글에 Hemant K Chitale이 작성한 댓글에 쓰기 속도 저하가 아닌 CPU 경합에 의해 log file sync 이벤트 대기가 발생할 수 있다는 내용이 포함되어 있습니다.
High 'log file sync' waits would also arise when LGWR is unable to get CPU to post back to the waiter that the log write has been completed. The waiter waits on this event till LGWR can signal but if LGWR is unable to get CPU, it is unable to signal quickly enough.