이번 글에서는 DUAL CONNECT BY 방식을 사용한 쿼리의 성능 개선 방안을 살펴보자.
테스트를 위해 아래와 같이 테이블을 생성하자.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS SELECT 100 AS c1 FROM XMLTABLE ('1 to 10000');
아래 쿼리는 t1 테이블을 100번 반복 액세스한다. 최악의 실행 계획으로 5,600개의 블록 I/O가 발생했다.
-- 2
SELECT COUNT (*)
FROM t1 a
, (SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL <= 100) b
WHERE b.lv <= a.c1;
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 2 | 5600 |
| 1 | SORT AGGREGATE | | 2 | 2 | 5600 |
| 2 | NESTED LOOPS | | 2 | 2000K| 5600 |
| 3 | VIEW | | 2 | 200 | 0 |
| 4 | CONNECT BY WITHOUT FILTERING| | 2 | 200 | 0 |
| 5 | FAST DUAL | | 2 | 2 | 0 |
|* 6 | TABLE ACCESS FULL | T1 | 200 | 2000K| 5600 |
----------------------------------------------------------------------------
아래는 t1 테이블로 b 인라인 뷰를 NL 조인한다. 블록 I/O가 56개로 감소했고, FAST DUAL로 인해 추가 블록 I/O가 발생하지 않았지만, 계층 전개(LEVEL)가 깊을수록 많은 시간이 소요될 수 있다.
-- 3
SELECT /*+ LEADING(A) USE_NL(B) */
COUNT (*)
FROM t1 a
, (SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL <= 100) b
WHERE b.lv <= a.c1;
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 2 | 56 |
| 1 | SORT AGGREGATE | | 2 | 2 | 56 |
| 2 | NESTED LOOPS | | 2 | 2000K| 56 |
| 3 | TABLE ACCESS FULL | T1 | 2 | 20000 | 56 |
|* 4 | VIEW | | 20000 | 2000K| 0 |
| 5 | CONNECT BY WITHOUT FILTERING| | 20000 | 2000K| 0 |
| 6 | FAST DUAL | | 20000 | 20000 | 0 |
----------------------------------------------------------------------------
범위 조건으로 인해 해시 조인이 불가능하므로 아래와 같이 소트 머지 조인을 사용하는 편이 바람직하다.
-- 4
SELECT /*+ LEADING(A) USE_MERGE(B) */
COUNT (*)
FROM t1 a
, (SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL <= 100) b
WHERE b.lv <= a.c1;
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 2 | 56 |
| 1 | SORT AGGREGATE | | 2 | 2 | 56 |
| 2 | MERGE JOIN | | 2 | 2000K| 56 |
| 3 | SORT JOIN | | 2 | 20000 | 56 |
| 4 | TABLE ACCESS FULL | T1 | 2 | 20000 | 56 |
|* 5 | SORT JOIN | | 20000 | 2000K| 0 |
| 6 | VIEW | | 2 | 200 | 0 |
| 7 | CONNECT BY WITHOUT FILTERING| | 2 | 200 | 0 |
| 8 | FAST DUAL | | 2 | 2 | 0 |
-----------------------------------------------------------------------------
위 쿼리에 PARALLEL 힌트를 추가해보면 병렬로 수행되지 않는 것을 확인할 수 있다. b 인라인 뷰의 카디널리티가 1로 예상되었기 때문이다.
-- 5
SELECT /*+ PARALLEL(2) LEADING(A) USE_MERGE(B) */
COUNT (*)
FROM t1 a
, (SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL <= 100) b
WHERE b.lv <= a.c1;
-------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | MERGE JOIN | |
| 3 | SORT JOIN | |
| 4 | TABLE ACCESS FULL | T1 |
|* 5 | SORT JOIN | |
| 6 | VIEW | |
| 7 | CONNECT BY WITHOUT FILTERING| |
| 8 | FAST DUAL | |
-------------------------------------------------
6-1번 쿼리처럼 CARDINALITY 힌트를 기술하거나, 6-2번 쿼리처럼 PQ_DISTRIBUTE 힌트를 통해 b 인라인 뷰를 BROADCAST하면 쿼리가 병렬로 수행되는 것을 확인할 수 있다.
-- 6-1
SELECT /*+ PARALLEL(2) LEADING(A) USE_MERGE(B) CARDINALITY(B 100) */
COUNT (*)
FROM t1 a
, (SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL <= 100) b
WHERE b.lv <= a.c1;
----------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | Q1,01 | PCWP | |
| 5 | MERGE JOIN | | Q1,01 | PCWP | |
| 6 | SORT JOIN | | Q1,01 | PCWP | |
| 7 | PX BLOCK ITERATOR | | Q1,01 | PCWC | |
|* 8 | TABLE ACCESS FULL | T1 | Q1,01 | PCWP | |
|* 9 | SORT JOIN | | Q1,01 | PCWP | |
| 10 | BUFFER SORT | | Q1,01 | PCWC | |
| 11 | PX RECEIVE | | Q1,01 | PCWP | |
| 12 | PX SEND BROADCAST | :TQ10000 | | S->P | BROADCAST |
| 13 | VIEW | | | | |
| 14 | CONNECT BY WITHOUT FILTERING| | | | |
| 15 | FAST DUAL | | | | |
----------------------------------------------------------------------------------------
-- 6-2
SELECT /*+ PARALLEL(2) LEADING(A) USE_MERGE(B) PQ_DISTRIBUTE(B NONE BROADCAST) */
COUNT (*)
FROM t1 a
, (SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL <= 100) b
WHERE b.lv <= a.c1;
----------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | Q1,01 | PCWP | |
| 5 | MERGE JOIN | | Q1,01 | PCWP | |
| 6 | SORT JOIN | | Q1,01 | PCWP | |
| 7 | PX BLOCK ITERATOR | | Q1,01 | PCWC | |
|* 8 | TABLE ACCESS FULL | T1 | Q1,01 | PCWP | |
|* 9 | SORT JOIN | | Q1,01 | PCWP | |
| 10 | BUFFER SORT | | Q1,01 | PCWC | |
| 11 | PX RECEIVE | | Q1,01 | PCWP | |
| 12 | PX SEND BROADCAST | :TQ10000 | | S->P | BROADCAST |
| 13 | VIEW | | | | |
| 14 | CONNECT BY WITHOUT FILTERING| | | | |
| 15 | FAST DUAL | | | | |
----------------------------------------------------------------------------------------
아래 쿼리는 XMLTABLE 테이블 함수를 사용했다. 병렬로 수행되지만 다수의 PIPELINED 함수를 동시에 사용하면 라이브러리 캐시 경합이 발생할 수 있으므로 주의가 필요하다.
-- 7
SELECT /*+ PARALLEL(2) LEADING(A) USE_MERGE(B) */
COUNT (*)
FROM t1 a
, (SELECT ROWNUM AS rn FROM XMLTABLE ('1 to 100')) b
WHERE b.rn <= a.c1;
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | Q1,01 | PCWP | |
| 5 | MERGE JOIN | | Q1,01 | PCWP | |
| 6 | SORT JOIN | | Q1,01 | PCWP | |
| 7 | PX BLOCK ITERATOR | | Q1,01 | PCWC | |
|* 8 | TABLE ACCESS FULL | T1 | Q1,01 | PCWP | |
|* 9 | SORT JOIN | | Q1,01 | PCWP | |
| 10 | BUFFER SORT | | Q1,01 | PCWC | |
| 11 | PX RECEIVE | | Q1,01 | PCWP | |
| 12 | PX SEND BROADCAST | :TQ10000 | | S->P | BROADCAST |
| 13 | VIEW | | | | |
| 14 | COUNT | | | | |
| 15 | COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE | | | |
-----------------------------------------------------------------------------------------------------------
6-1, 6-2, 7번 쿼리는 계층 전개와 ROWNUM 슈도 칼럼로 인해 PQ_REPLICATE가 동작하지 않았다. copy_t 테이블을 사용하거나 WITH 절과 MATERIALIZE 힌트를 사용하면 PQ_REPLICATE로 동작하는 것을 확인할 수 있다.
-- 8-1
DROP TABLE copy_t PURGE;
CREATE TABLE copy_t AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 100');
SELECT /*+ PARALLEL(2) LEADING(A) USE_MERGE(B) */
COUNT (*)
FROM t1 a
, copy_t b
WHERE b.c1 <= a.c1;
--------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | Q1,00 | PCWP | |
| 5 | MERGE JOIN | | Q1,00 | PCWP | |
| 6 | SORT JOIN | | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | |
|* 9 | SORT JOIN | | Q1,00 | PCWP | |
| 10 | TABLE ACCESS FULL | COPY_T | Q1,00 | PCWP | |
--------------------------------------------------------------------------
-- 8-2
WITH w1 AS (SELECT /*+ MATERIALIZE */ LEVEL AS lv FROM DUAL CONNECT BY LEVEL <= 100)
SELECT /*+ PARALLEL(2) LEADING(A) USE_MERGE(B) */
COUNT (*)
FROM t1 a
, w1 b
WHERE b.lv <= a.c1;
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6BC6_D784C58 | | | |
| 3 | CONNECT BY WITHOUT FILTERING | | | | |
| 4 | FAST DUAL | | | | |
| 5 | SORT AGGREGATE | | | | |
| 6 | PX COORDINATOR | | | | |
| 7 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 8 | SORT AGGREGATE | | Q1,00 | PCWP | |
| 9 | MERGE JOIN | | Q1,00 | PCWP | |
| 10 | SORT JOIN | | Q1,00 | PCWP | |
| 11 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | |
|* 12 | SORT JOIN | | Q1,00 | PCWP | |
| 13 | VIEW | | Q1,00 | PCWP | |
| 14 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
|* 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6BC6_D784C58 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------
'Oracle > Tuning' 카테고리의 다른 글
| 병렬 쿼리 평균 수행 시간 (0) | 2019.10.01 |
|---|---|
| FBI 활용 사례 (0) | 2019.09.25 |
| UPDATE 문 성능 개선 사례 (0) | 2019.09.03 |
| NO FPD 뷰 성능 개선 방안 (0) | 2019.09.02 |
| 고유 값에 대한 상관 서브 쿼리 성능 개선 방안 (0) | 2019.08.28 |
