12.1 버전에 병렬 서버에서 서브 쿼리를 필터링할 수 있는 기능이 추가되었다. 해당 기능은 PQ_FILTER 힌트를 통해 제어할 수 있다.
PQ_FILTER 힌트의 구문은 아래와 같다.
/*+ PQ_FILTER(SERIAL | NONE | HASH | RANDOM) */
아래는 V$SQL_HINT 뷰를 조회한 결과다. PQ_FILTER 힌트는 블록 레벨(target_level=2)로 동작한다.
-- 1 SELECT name, sql_feature, class, inverse, target_level, version FROM v$sql_hint WHERE name = 'PQ_FILTER'; NAME SQL_FEATURE CLASS INVERSE TARGET_LEVEL VERSION --------- ----------- --------- ------- ------------ -------- PQ_FILTER QKSFM_PQ PQ_FILTER 2 12.1.0.1 1개의 행이 선택되었습니다.
테스트를 위해 아래와 같이 테이블을 생성하자.
-- 2
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
DROP TABLE t3 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1, 1 AS c2 FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 AS SELECT * FROM t1;
CREATE TABLE t3 AS SELECT * FROM t1;
CREATE INDEX t2_x1 ON t2 (c1);
CREATE INDEX t3_x1 ON t3 (c1);
아래 쿼리는 서브 쿼리가 UNNEST된 후 NL 세미 조인으로 처리되었다.
-- 3
SELECT *
FROM t1 a
WHERE EXISTS (SELECT /*+ UNNEST NL_SJ */
1
FROM t2 x
WHERE x.c1 = a.c1);
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS SEMI | |
| 2 | TABLE ACCESS FULL| T1 |
|* 3 | INDEX RANGE SCAN | T2_X1 |
------------------------------------
아래 쿼리는 서브 쿼리가 UNNEST되지 않아 FILTER 방식으로 처리되었다. Outline Data 항목에서 PQ_FILTER 힌트를 확인할 수 있다.
-- 4
SELECT *
FROM t1 a
WHERE EXISTS (SELECT /*+ NO_UNNEST */
1
FROM t2 x
WHERE x.c1 = a.c1);
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | TABLE ACCESS FULL| T1 |
|* 3 | INDEX RANGE SCAN | T2_X1 |
------------------------------------
Outline Data
-------------
PQ_FILTER(@"SEL$1" SERIAL)
아래 쿼리는 병렬로 수행되었다. 인수로 SERIAL를 기술하면 QC에서 서브 쿼리를 필터링한다. 11.2 버전까지의 기본 동작이다.
-- 5
SELECT /*+ PARALLEL(2) PQ_FILTER(SERIAL) */
*
FROM t1 a
WHERE EXISTS (SELECT /*+ NO_UNNEST */
1
FROM t2 x
WHERE x.c1 = a.c1);
-----------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | FILTER | | | | | -- FILTER
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | |
|* 6 | INDEX RANGE SCAN | T2_X1 | | | |
-----------------------------------------------------------------------
아래 쿼리는 NONE을 인수로 기술했다. 메인 쿼리를 분배하지 않았으며, 병렬 서버에서 서브 쿼리를 필터링했다. 병렬 서버가 t1 테이블을 블록 그래뉼로 조회하고, 조회 결과로 서브 쿼리를 필터링하여 QC로 결과를 전송하는(5->4->3->6->2) 방식이다.
-- 6
SELECT /*+ PARALLEL(2) PQ_FILTER(NONE) */
*
FROM t1 a
WHERE EXISTS (SELECT /*+ NO_UNNEST */
1
FROM t2 x
WHERE x.c1 = a.c1);
-----------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
|* 3 | FILTER | | Q1,00 | PCWC | | -- FILTER
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | |
|* 6 | INDEX RANGE SCAN | T2_X1 | | | |
-----------------------------------------------------------------------
아래 쿼리는 HASH를 인수로 기술했다. 메인 쿼리를 HASH 방식으로 분배한 후, 병렬 서버에서 서브 쿼리를 필터링했다. 병렬 서버가 t1 테이블을 블록 그래뉼로 조회하여 다른 병렬 서버 세트로 데이터를 분배하면(8->7->6), 수신한 병렬 서버에서 서브 쿼리를 필터링하고 결과를 버퍼링한 후 필터링이 완료되면 QC로 버퍼링한 내용을 전송하는(5->4->9->3->2) 방식이다. HASH 방식은 조인 조건인 c1 열의 해시 값으로 분배되기 때문에 서브 쿼리 캐싱 효율을 높이는 목적으로 활용할 수 있을 것 같다.
-- 7
SELECT /*+ PARALLEL(2) PQ_FILTER(HASH) */
*
FROM t1 a
WHERE EXISTS (SELECT /*+ NO_UNNEST */
1
FROM t2 x
WHERE x.c1 = a.c1);
-------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib | Used-Mem |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) | |
| 3 | BUFFER SORT | | Q1,01 | PCWP | | 206K (0)| -- BUFFER
|* 4 | FILTER | | Q1,01 | PCWP | | | -- FILTER
| 5 | PX RECEIVE | | Q1,01 | PCWP | | |
| 6 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH | | -- HASH
| 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | |
|* 8 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | |
|* 9 | INDEX RANGE SCAN | T2_X1 | | | | |
-------------------------------------------------------------------------------------
아래 쿼리는 RANDOM을 인수로 기술했다. 메인 쿼리를 RANDOM 방식으로 분배한 후, 병렬 서버에서 서브 쿼리를 필터링했다. HASH 방식과 동일하게 동작한다. RANDOM 방식은 read by other session 등의 블록 경합을 해소하는 목적으로 활용할 수 있을 것 같다.
-- 8
SELECT /*+ PARALLEL(2) PQ_FILTER(RANDOM) */
*
FROM t1 a
WHERE EXISTS (SELECT /*+ NO_UNNEST */
1
FROM t2 x
WHERE x.c1 = a.c1);
-------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib | Used-Mem |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) | |
| 3 | BUFFER SORT | | Q1,01 | PCWP | | 206K (0)| -- BUFFER
|* 4 | FILTER | | Q1,01 | PCWP | | | -- FILTER
| 5 | PX RECEIVE | | Q1,01 | PCWP | | |
| 6 | PX SEND ROUND-ROBIN| :TQ10000 | Q1,00 | P->P | RND-ROBIN | | -- RANDOM
| 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | |
|* 8 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | |
|* 9 | INDEX RANGE SCAN | T2_X1 | | | | |
-------------------------------------------------------------------------------------
아래 쿼리는 메인 쿼리에서 조인을 수행했다. PQ_FILTER 힌트는 블록 레벨로 동작하므로 조인 결과가 HASH 방식으로 분배되었다. 서브 쿼리 필터링은 메인 쿼리가 수행된 후 수행되는 것이 일반적이다.
-- 9
SELECT /*+ PARALLEL(2) PQ_FILTER(HASH) */
*
FROM t1 a, t3 b
WHERE EXISTS (SELECT /*+ NO_UNNEST */
1
FROM t2 x
WHERE x.c1 = a.c1)
AND b.c1 = a.c1;
---------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) |
| 3 | BUFFER SORT | | Q1,01 | PCWP | |
|* 4 | FILTER | | Q1,01 | PCWP | | -- FILTER
| 5 | PX RECEIVE | | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH | -- HASH
|* 7 | HASH JOIN | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
|* 9 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | |
| 10 | TABLE ACCESS FULL | T3 | Q1,00 | PCWP | |
|* 11 | INDEX RANGE SCAN | T2_X1 | | | |
---------------------------------------------------------------------------
Outline Data
-------------
PQ_DISTRIBUTE(@"SEL$1" "B"@"SEL$1" NONE BROADCAST)
PQ_REPLICATE(@"SEL$1" "B"@"SEL$1")
PQ_FILTER(@"SEL$1" HASH)
아래 쿼리는 다수의 서브 쿼리를 사용했다. 2개의 서브 쿼리 모두 병렬 서버에서 필터링되었다. ORDER_SUBQ 힌트로 서브 쿼리의 수행 순서를 조정할 수 있다.
-- 10
SELECT /*+ PARALLEL(2) PQ_FILTER(NONE) */
*
FROM t1 a
WHERE EXISTS (SELECT /*+ NO_UNNEST */
1
FROM t2 x
WHERE x.c1 = a.c1)
AND EXISTS (SELECT /*+ NO_UNNEST */
1
FROM t3 y
WHERE y.c1 = a.c1);
-----------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
|* 3 | FILTER | | Q1,00 | PCWC | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | |
|* 6 | INDEX RANGE SCAN | T2_X1 | | | |
|* 7 | INDEX RANGE SCAN | T3_X1 | | | |
-----------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
5 - SEL$1 / A@SEL$1
6 - SEL$2 / X@SEL$2
7 - SEL$3 / Y@SEL$3
Outline Data
-------------
ORDER_SUBQ(@"SEL$1" "SEL$2" "SEL$3")
지금까지 PQ_FILTER 힌트의 동작을 인수별로 살펴봤다. HASH 방식과 RANDOM 방식은 추가적인 버퍼링이 필요하므로 특별한 경우가 아니라면 NONE 방식으로 사용하는 것이 일반적일 것으로 판단된다.
'Oracle > Tuning' 카테고리의 다른 글
| Null-Aware 안티 조인 (1) | 2019.10.17 |
|---|---|
| OPTIMIZER_FEATURES_ENABLE (1) | 2019.10.17 |
| 누적합 계산 (0) | 2019.10.14 |
| In-Memory CDT 기능과 스칼라 서브 쿼리 (0) | 2019.10.11 |
| EXPRESSION EVALUATION 오퍼레이션 (0) | 2019.10.10 |
