Oracle 21c에 WITH 절에 대한 FPD(Filter PushDown) 쿼리 변환 기능이 추가되었습니다. 이 기능은 SELECT 문에 사용한 조건을 WITH 절에 Pushdown함으로써 성능을 개선합니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1, c2) AS SELECT ROWNUM, LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 1000000');
아래는 19.3 버전에서 WITH 문을 사용한 쿼리의 실행 계획입니다. t1 테이블 전체를 임시 영역에 저장한 것을 확인할 수 있습니다.
-- 3: 19.3
WITH w1 AS (SELECT /*+ MATERIALIZE */ * FROM t1)
SELECT * FROM w1 WHERE c1 <= 100;
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Reads | Writes |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 |00:00:00.79 | 31004 | 14318 | 7735 |
| 1 | TEMP TABLE TRANSFORMATION | | 100 |00:00:00.79 | 31004 | 14318 | 7735 |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6796_2777275 | 0 |00:00:00.52 | 23253 | 6583 | 7735 |
| 3 | TABLE ACCESS FULL | T1 | 1000K|00:00:00.25 | 15395 | 6583 | 0 |
|* 4 | VIEW | | 100 |00:00:00.27 | 7746 | 7735 | 0 |
| 5 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6796_2777275 | 1000K|00:00:00.23 | 7746 | 7735 | 0 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("C1"<=100)
아래는 21.3 버전에서 WITH 문을 사용한 쿼리의 실행 계획입니다. c1 <= 100 조건이 WITH 절로 Pushdown되어 100 로우만 메모리에 저장한 것을 확인할 수 있습니다. 아울러 Predicate Information 항목에 3 - filter("T1"."C1"<=100) 조건이 표시됩니다.
-- 2: 21.3
WITH w1 AS (SELECT /*+ MATERIALIZE */ * FROM t1)
SELECT * FROM w1 WHERE c1 <= 100;
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 |00:00:00.06 | 15390 | 15385 |
| 1 | TEMP TABLE TRANSFORMATION | | 100 |00:00:00.06 | 15390 | 15385 |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6879_2EDA9D | 0 |00:00:00.06 | 15389 | 15385 |
|* 3 | TABLE ACCESS FULL | T1 | 100 |00:00:00.06 | 15388 | 15385 |
|* 4 | VIEW | | 100 |00:00:00.01 | 0 | 0 |
| 5 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6879_2EDA9D | 100 |00:00:00.01 | 0 | 0 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."C1"<=100)
4 - filter("C1"<=100)
아래는 앞선 쿼리의 10053 트레이스입니다. WCFPD 쿼리 변환이 표시되는 것을 확인할 수 있습니다.
-- 3
**************************
Predicate Move-Around (PM)
**************************
PM: PM bypassed: Outer query contains no views.
PM: PM bypassed: Outer query contains no views.
isReduceGrByValid: Group By Validation in query block SEL$1 (#0) (Failed).
isReduceGrByValid: Group By Validation in query block SEL$2 (#0) (Failed).
isReduceGrByValid: Group By Validation in query block SEL$1 (#0) (Failed).
isReduceGrByValid: Group By Validation in query block SEL$2 (#0) (Failed).
WCFPD: Considering simple filter push (pre rewrite) in query block SEL$1 (#0)
WCFPD: Current where clause predicates <empty predicate list>
WCFPD: Considering simple filter push (pre rewrite) in query block SEL$2 (#0)
WCFPD: Current where clause predicates "W1"."C1"<=100
WCFPD: Actual WC Reference Count:
==========================
SEL$1 -> 1
WC QB Visit Count Information:
==========================
SEL$1 -> 1
Push Predicates Information:
==========================
PredInfo : SEL$1 -->
Fro: W1 - "W1"."C1"<=100
==========================
참고로 이 기능은 _optimizer_wc_filter_pushdown 파라미터와 관련이 있습니다.
-- 4
NAME VALUE DEFAULT_VALUE DESCRIPTION
----------------------------- ----- ------------- ----------------------------------------------------
_optimizer_wc_filter_pushdown TRUE TRUE enable/disable with clause filter predicate pushdown
_optimizer_wc_filter_pushdown 파라미터를 FALSE로 설정하면 19.3 버전과 동일한 실행 계획이 생성됩니다.
-- 5
WITH w1 AS (SELECT /*+ MATERIALIZE */ * FROM t1)
SELECT /*+ OPT_PARAM('_optimizer_wc_filter_pushdown' 'false') */ * FROM w1 WHERE c1 <= 100;
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Reads | Writes |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 |00:00:00.78 | 24608 | 19953 | 4568 |
| 1 | TEMP TABLE TRANSFORMATION | | 100 |00:00:00.78 | 24608 | 19953 | 4568 |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D687C_2EDA9D | 0 |00:00:00.66 | 20029 | 15385 | 4568 |
| 3 | TABLE ACCESS FULL | T1 | 1000K|00:00:00.43 | 15388 | 15385 | 0 |
|* 4 | VIEW | | 100 |00:00:00.12 | 4573 | 4568 | 0 |
| 5 | TABLE ACCESS FULL | SYS_TEMP_0FD9D687C_2EDA9D | 1000K|00:00:00.08 | 4573 | 4568 | 0 |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("C1"<=100)