병렬 쿼리에 스칼라 서브 쿼리를 사용하면 쿼리의 성능이 저하될 수 있다. 이에 대한 개선책으로 12.1 버전에 EXPRESSION EVALUATION 오퍼레이션이 추가되었다. 해당 오퍼레이션은 병렬 서버가 스칼라 서브 쿼리를 수행한다.
테스트를 위해 아래와 같이 테이블을 생성하자.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
DROP TABLE t3 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 AS SELECT ROWNUM AS c1, 1 AS c2 FROM XMLTABLE ('1 to 10000');
CREATE TABLE t3 AS SELECT * FROM t2;
아래는 병렬 쿼리에 스칼라 서브 쿼리를 사용한 쿼리다. 11.2 버전의 실행계획에서 QC가 스칼라 서브 쿼리를 수행한 것을 확인할 수 있다. 수행에 12.10초가 소요되었고, 400K의 블록 I/O가 발생했다.
-- 2 : 11.2
SELECT /*+ PARALLEL(A 2) */
a.c1
, (SELECT x.c2 FROM t2 x WHERE x.c1 = a.c1) AS c2
, (SELECT x.c2 FROM t3 x WHERE x.c1 = a.c1) AS c3
FROM t1 a;
10000 행이 선택되었습니다.
경 과: 00:00:12.10
----------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | TABLE ACCESS FULL | T2 | | | | -- !
|* 2 | TABLE ACCESS FULL | T3 | | | | -- !
| 3 | PX COORDINATOR | | | | |
| 4 | PX SEND QC (RANDOM)| :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 5 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | |
----------------------------------------------------------------------
Global Stats
====================
| Elapsed | Buffer |
| Time(s) | Gets |
====================
| 11 | 400K |
====================
Parallel Execution Details (DOP=2 , Servers Allocated=2)
=======================================================
| Name | Type | Server# | Elapsed | Buffer |
| | | | Time(s) | Gets |
=======================================================
| PX Coordinator | QC | | 12 | 400K | -- !
| p000 | Set 1 | 1 | 0.02 | 32 |
| p001 | Set 1 | 2 | 0.02 | 32 |
=======================================================
SQL Plan Monitoring Details (Plan Hash Value=2939534272)
==================================================================================
| Id | Operation | Name | Time | Start | Execs | Rows |
| | | | Active(s) | Active | | (Actual) |
==================================================================================
| 0 | SELECT STATEMENT | | 13 | +0 | 10000 | 10000 |
| 1 | TABLE ACCESS FULL | T2 | 13 | +0 | 10000 | 10000 | -- !
| 2 | TABLE ACCESS FULL | T3 | 13 | +0 | 10000 | 10000 | -- !
| 3 | PX COORDINATOR | | 13 | +0 | 3 | 10000 |
| 4 | PX SEND QC (RANDOM) | :TQ10000 | 9 | +2 | 2 | 10000 |
| 5 | PX BLOCK ITERATOR | | 9 | +2 | 2 | 10000 |
| 6 | TABLE ACCESS FULL | T1 | 9 | +2 | 16 | 10000 |
==================================================================================
아래는 12.2 버전의 실행계획이다. EXPRESSION EVALUATION 오퍼레이션을 확인할 수 있다. 이상한 점은 수행에 14.47초가 소요되었다는 것이다. 블록 I/O도 1M만큼 발생했는데 이는 11.2 버전의 2배에 해당하는 수치다. 단순한 표현의 문제일 수 있지만 11.2 버전보다 많은 시간이 소요되었다는 점에서 오동작을 의심할 수 있다.
-- 3 : 12.2
SELECT /*+ PARALLEL(A 2) */
a.c1
, (SELECT x.c2 FROM t2 x WHERE x.c1 = a.c1) AS c2
, (SELECT x.c2 FROM t3 x WHERE x.c1 = a.c1) AS c3
FROM t1 a;
10000 행이 선택되었습니다.
경 과: 00:00:14.47
--------------------------------------------------------------------------
| 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 | EXPRESSION EVALUATION | | Q1,00 | PCWC | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | |
|* 6 | TABLE ACCESS FULL | T2 | | | | -- !
|* 7 | TABLE ACCESS FULL | T3 | | | | -- !
--------------------------------------------------------------------------
Global Stats
====================
| Elapsed | Buffer |
| Time(s) | Gets |
====================
| 21 | 1M |
====================
Parallel Execution Details (DOP=2 , Servers Allocated=2)
=======================================================
| Name | Type | Server# | Elapsed | Buffer |
| | | | Time(s) | Gets |
=======================================================
| PX Coordinator | QC | | 9.28 | 580K | -- !
| p000 | Set 1 | 1 | 5.56 | 305K |
| p001 | Set 1 | 2 | 5.92 | 275K |
=======================================================
SQL Plan Monitoring Details (Plan Hash Value=2064967019)
=====================================================================================
| Id | Operation | Name | Time | Start | Execs | Rows |
| | | | Active(s) | Active | | (Actual) |
=====================================================================================
| 0 | SELECT STATEMENT | | 13 | +2 | 3 | 10000 |
| 1 | PX COORDINATOR | | 11 | +4 | 3 | 10000 |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 12 | +2 | 2 | 10000 |
| 3 | EXPRESSION EVALUATION | | 12 | +2 | 2 | 10000 |
| 4 | PX BLOCK ITERATOR | | 12 | +2 | 2 | 10000 |
| 5 | TABLE ACCESS FULL | T1 | 12 | +2 | 16 | 10000 |
| 6 | TABLE ACCESS FULL | T2 | 14 | +1 | 20000 | 20000 | -- !
| 7 | TABLE ACCESS FULL | T3 | 13 | +2 | 20000 | 20000 | -- !
=====================================================================================
아울러 12.2 버전의 SQL Monitor 실행계획에서 스칼라 서브 쿼리의 Execs, Rows (Actual) 항목이 20000으로 표시되는데, V$SQL_PLAN_MONITOR 뷰를 조회해보면 QC에 값이 존재하는 것을 확인할 수 있다.
-- 4 PROCESS_NAME PLAN_LINE_ID PLAN_OPERATION PLAN_OPTIONS PLAN_OBJECT_NAME STARTS OUTPUT_ROWS ------------ ------------ -------------- ------------ ---------------- ------ ----------- ora 6 TABLE ACCESS FULL T2 10000 10000 -- ! p001 6 TABLE ACCESS FULL 4744 4744 p000 6 TABLE ACCESS FULL 5256 5256 PROCESS_NAME PLAN_LINE_ID PLAN_OPERATION PLAN_OPTIONS PLAN_OBJECT_NAME STARTS OUTPUT_ROWS ------------ ------------ -------------- ------------ ---------------- ------ ----------- ora 7 TABLE ACCESS FULL T3 10000 10000 -- ! p001 7 TABLE ACCESS FULL 4744 4744 p000 7 TABLE ACCESS FULL 5256 5256
아래 쿼리는 스칼라 서브 쿼리를 사용한 병렬 쿼리를 MATERIALIZE시켰다. 11.2 버전의 실행계획을 살펴보면 병렬 서버가 스칼라 서브 쿼리를 수행한 것을 확인할 수 있다. 임시 세그먼트를 병렬로 생성할 수 있기 때문에 이런 동작이 가능한 것으로 보인다. MATERIALIZE 작업이 추가되었지만 3번 쿼리의 절반인 7.03초만에 쿼리가 수행되었다. EXPRESSION EVALUATION 오퍼레이션의 오동작을 방증할 수 있는 예제로 볼 수 있다.
-- 5 : 11.2
WITH w1 AS (
SELECT /*+ MATERIALIZE PARALLEL(A 2) */
a.c1
, (SELECT x.c2 FROM t2 x WHERE x.c1 = a.c1) AS c2
, (SELECT x.c2 FROM t3 x WHERE x.c1 = a.c1) AS c3
FROM t1 a)
SELECT * FROM w1;
10000 행이 선택되었습니다.
경 과: 00:00:07.03
----------------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 4 | LOAD AS SELECT | | Q1,00 | PCWP | |
|* 5 | TABLE ACCESS FULL | T2 | Q1,00 | PCWP | | -- !
|* 6 | TABLE ACCESS FULL | T3 | Q1,00 | PCWP | | -- !
| 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | |
| 9 | PX COORDINATOR | | | | |
| 10 | PX SEND QC (RANDOM) | :TQ20000 | Q2,00 | P->S | QC (RAND) |
| 11 | VIEW | | Q2,00 | PCWP | |
| 12 | PX BLOCK ITERATOR | | Q2,00 | PCWC | |
|* 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6609_ED7EAB9 | Q2,00 | PCWP | |
----------------------------------------------------------------------------------------------
Global Stats
============================
| Elapsed | Buffer | Write |
| Time(s) | Gets | Bytes |
============================
| 14 | 401K | 3MB |
============================
Parallel Execution Details (DOP=2 , Servers Allocated=4)
========================================================================
| Name | Type | Group# | Server# | Elapsed | Buffer | Write |
| | | | | Time(s) | Gets | Bytes |
========================================================================
| PX Coordinator | QC | | | 0.02 | 194 | 1MB |
| p000 | Set 1 | 1 | 1 | 6.54 | 190K | 1MB | -- !
| p001 | Set 1 | 1 | 2 | 6.92 | 210K | 1MB | -- !
| p000 | Set 1 | 2 | 1 | 0.02 | 191 | . |
| p001 | Set 1 | 2 | 2 | 0.03 | 270 | . |
========================================================================
SQL Plan Monitoring Details (Plan Hash Value=4163721023)
=========================================================================================================
| Id | Operation | Name | Time | Start | Execs | Rows |
| | | | Active(s) | Active | | (Actual) |
=========================================================================================================
| 0 | SELECT STATEMENT | | 1 | +7 | 1 | 10000 |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | +7 | 1 | 10000 |
| 2 | PX COORDINATOR | | 1 | +7 | 3 | 2 |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | +7 | 2 | 2 |
| 4 | LOAD AS SELECT | | 6 | +2 | 2 | 2 |
| 5 | TABLE ACCESS FULL | T2 | 7 | +1 | 10000 | 10000 | -- !
| 6 | TABLE ACCESS FULL | T3 | 6 | +2 | 10000 | 10000 | -- !
| 7 | PX BLOCK ITERATOR | | 6 | +2 | 2 | 10000 |
| 8 | TABLE ACCESS FULL | T1 | 6 | +2 | 16 | 10000 |
| 9 | PX COORDINATOR | | 1 | +7 | 3 | 10000 |
| 10 | PX SEND QC (RANDOM) | :TQ20000 | 1 | +7 | 2 | 10000 |
| 11 | VIEW | | 1 | +7 | 2 | 10000 |
| 12 | PX BLOCK ITERATOR | | 1 | +7 | 2 | 10000 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6609_ED7EAB9 | 1 | +7 | 26 | 10000 |
=========================================================================================================
아래는 5번 쿼리의 12.2 버전 실행계획이다. 수행에 6.20초가 소요되었으며, 581K의 블록 I/O가 발생했다. 11.2 버전과 큰 차이가 없는 것을 확인할 수 있다. 아울러 4번 오퍼레이션에서 TEMP SEGMENT MERGE 옵션을 확인할 수 있는데 이는 다수의 임시 세그먼트를 생성하고, 생성이 완료된 후 익스텐트 맵 정보를 수정하여 세그먼트는 병합하는 동작으로 알려져 있다.
-- 6 : 12.2
WITH w1 AS (
SELECT /*+ MATERIALIZE PARALLEL(A 2) */
a.c1
, (SELECT x.c2 FROM t2 x WHERE x.c1 = a.c1) AS c2
, (SELECT x.c2 FROM t3 x WHERE x.c1 = a.c1) AS c3
FROM t1 a)
SELECT * FROM w1;
10000 행이 선택되었습니다.
경 과: 00:00:06.20
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 4 | LOAD AS SELECT (TEMP SEGMENT MERGE)| SYS_TEMP_0FD9D6648_E03C2CE | Q1,00 | PCWP | |
|* 5 | TABLE ACCESS FULL | T2 | Q1,00 | PCWP | | -- !
|* 6 | TABLE ACCESS FULL | T3 | Q1,00 | PCWP | | -- !
| 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | |
| 9 | PX COORDINATOR | | | | |
| 10 | PX SEND QC (RANDOM) | :TQ20000 | Q2,00 | P->S | QC (RAND) |
| 11 | VIEW | | Q2,00 | PCWP | |
| 12 | PX BLOCK ITERATOR | | Q2,00 | PCWC | |
|* 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6648_E03C2CE | Q2,00 | PCWP | |
----------------------------------------------------------------------------------------------------------
Global Stats
============================
| Elapsed | Buffer | Write |
| Time(s) | Gets | Bytes |
============================
| 12 | 581K | 3MB |
============================
Parallel Execution Details (DOP=2 , Servers Allocated=4)
========================================================================
| Name | Type | Group# | Server# | Elapsed | Buffer | Write |
| | | | | Time(s) | Gets | Bytes |
========================================================================
| PX Coordinator | QC | | | 0.03 | 227 | 1MB |
| p000 | Set 1 | 1 | 1 | 6.06 | 305K | 1MB | -- !
| p001 | Set 1 | 1 | 2 | 5.53 | 275K | 1MB | -- !
| p000 | Set 1 | 2 | 1 | 0.02 | 317 | . |
| p001 | Set 1 | 2 | 2 | 0.04 | 378 | . |
========================================================================
SQL Plan Monitoring Details (Plan Hash Value=2778567849)
======================================================================================================================
| Id | Operation | Name | Time | Start | Execs | Rows |
| | | | Active(s) | Active | | (Actual) |
======================================================================================================================
| 0 | SELECT STATEMENT | | 1 | +6 | 1 | 10000 |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | +6 | 1 | 10000 |
| 2 | PX COORDINATOR | | 1 | +6 | 3 | 4 |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 2 | +5 | 2 | 4 |
| 4 | LOAD AS SELECT (TEMP SEGMENT MERGE) | SYS_TEMP_0FD9D6648_E03C2CE | 5 | +2 | 2 | 4 |
| 5 | TABLE ACCESS FULL | T2 | 6 | +1 | 10000 | 10000 | -- !
| 6 | TABLE ACCESS FULL | T3 | 6 | +1 | 10000 | 10000 | -- !
| 7 | PX BLOCK ITERATOR | | 5 | +2 | 2 | 10000 |
| 8 | TABLE ACCESS FULL | T1 | 5 | +2 | 16 | 10000 |
| 9 | PX COORDINATOR | | 1 | +6 | 3 | 10000 |
| 10 | PX SEND QC (RANDOM) | :TQ20000 | 1 | +6 | 2 | 10000 |
| 11 | VIEW | | 1 | +6 | 2 | 10000 |
| 12 | PX BLOCK ITERATOR | | 1 | +6 | 2 | 10000 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6648_E03C2CE | 1 | +6 | 26 | 10000 |
======================================================================================================================
결론은 병렬 쿼리의 EXPRESSION EVALUATION 오퍼레이션은 검증이 필요하며, 이에 대한 개선안으로 MATERIALIZE를 고려할 수 있다는 것이다. 더불어 MATERIALIZE를 활용하면 11.2 버전의 병렬 스칼라 서브 쿼리에 대한 고민도 함께 해결할 수 있을 것으로 보인다.
'Oracle > Tuning' 카테고리의 다른 글
| 누적합 계산 (0) | 2019.10.14 |
|---|---|
| In-Memory CDT 기능과 스칼라 서브 쿼리 (0) | 2019.10.11 |
| POWER 함수의 CPU 연산 #1 (0) | 2019.10.08 |
| 아우터 OR 조인 조건 #1 (0) | 2019.10.06 |
| 행 복제 성능 저하 사례 (0) | 2019.10.05 |
