V$SQL 뷰의 elapsed_time 열은 쿼리의 수행 시간이 누적 기록된다. elapsed_time 값을 executions 값으로 나누면 평균 수행 시간을 계산할 수 있다. 병렬 쿼리는 해당 방식으로 평균 수행 시간을 계산할 수 없다. 관련 내용을 살펴보자.
테스트를 위해 아래와 같이 테이블을 생성하자.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS SELECT LPAD ('X', 100, 'X') AS c1 FROM XMLTABLE ('1 to 10000000');
아래는 병렬을 사용하지 않은 쿼리다. 2.32초가 소요되었다.
-- 2-1
SELECT COUNT (*) AS c1 FROM t1;
------------------------------------------------
| Id | Operation | Name | A-Time |
------------------------------------------------
| 0 | SELECT STATEMENT | |00:00:02.32 |
| 1 | SORT AGGREGATE | |00:00:02.32 |
| 2 | TABLE ACCESS FULL| T1 |00:00:02.31 |
------------------------------------------------
-- 2-2
SELECT executions, px_servers_executions, ROUND (elapsed_time / 1e6, 2) AS elapsed_time
FROM v$sql
WHERE sql_id = ' buyynq5ph0rh7';
EXECUTIONS PX_SERVERS_EXECUTIONS ELAPSED_TIME
---------- --------------------- ------------
1 0 2.32 -- 2.32 / 1 = 2.32
1개의 행이 선택되었습니다.
아래는 병렬을 사용한 쿼리다. 2.24초가 소요되었지만 elapsed_time 값이 4.38초다. QC와 병렬 서버의 수행 시간이 합산되어 기록되기 때문이다. px_servers_executions 값을 executions 값으로 나누면 DOP를 구할 수 있고, elapsed_time 값을 DOP로 나누면 평균 수행 시간을 구할 수 있다. 2.19초로 계산된다.
-- 3-1
SELECT /*+ PARALLEL(2) */ COUNT(*) AS c1 FROM t1;
--------------------------------------------------------
| Id | Operation | Name | A-Time |
--------------------------------------------------------
| 0 | SELECT STATEMENT | |00:00:02.24 |
| 1 | SORT AGGREGATE | |00:00:02.24 |
| 2 | PX COORDINATOR | |00:00:02.24 |
| 3 | PX SEND QC (RANDOM) | :TQ10000 |00:00:00.01 |
| 4 | SORT AGGREGATE | |00:00:00.01 |
| 5 | PX BLOCK ITERATOR | |00:00:00.01 |
|* 6 | TABLE ACCESS FULL| T1 |00:00:00.01 |
--------------------------------------------------------
-- 3-2
SELECT executions, px_servers_executions, ROUND (elapsed_time / 1e6, 2) AS elapsed_time
FROM v$sql
WHERE sql_id = 'd0jvr41q5uy89';
EXECUTIONS PX_SERVERS_EXECUTIONS ELAPSED_TIME
---------- --------------------- ------------
1 2 4.38 -- 4.38 / 1 / (2 / 1) = 2.19
1개의 행이 선택되었습니다.
아래 병렬 쿼리는 MATERIALIZE된 서브 쿼리를 사용했다. px_servers_executions 값이 4다. px_servers_executions 열은 쿼리가 사용한 병렬 서버의 갯수가 저장되고, MATERIALIZE된 서브 쿼리는 별도의 병렬 서버에 의해 수행되기 때문이다. 직전 방식으로 계산하면 수행 시간이 1.10초로 왜곡된다.
-- 4-1
WITH w1 AS (SELECT /*+ MATERIALIZE */ COUNT(*) AS c1 FROM t1)
SELECT /*+ MONITOR PARALLEL(2) */ * FROM w1;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |00:00:02.22 |
| 1 | TEMP TABLE TRANSFORMATION | |00:00:02.22 |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6D5B_D784C58 |00:00:02.21 |
| 3 | SORT AGGREGATE | |00:00:02.21 |
| 4 | PX COORDINATOR | |00:00:02.21 |
| 5 | PX SEND QC (RANDOM) | :TQ10000 |00:00:00.01 |
| 6 | SORT AGGREGATE | |00:00:00.01 |
| 7 | PX BLOCK ITERATOR | |00:00:00.01 |
|* 8 | TABLE ACCESS FULL | T1 |00:00:00.01 |
| 9 | PX COORDINATOR | |00:00:00.01 |
| 10 | PX SEND QC (RANDOM) | :TQ20000 |00:00:00.01 |
| 11 | VIEW | |00:00:00.01 |
| 12 | PX BLOCK ITERATOR | |00:00:00.01 |
|* 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6D5B_D784C58 |00:00:00.01 |
--------------------------------------------------------------------------------------------
-- 4-2
SELECT executions, px_servers_executions, ROUND (elapsed_time / 1e6, 2) AS elapsed_time
FROM v$sql
WHERE sql_id = '0j0c6q3h2h3qa';
EXECUTIONS PX_SERVERS_EXECUTIONS ELAPSED_TIME
---------- --------------------- ------------
1 4 4.41 -- 4.41 / 1 / (4 / 1) = 1.10
1개의 행이 선택되었습니다.
SQL Monitor 레포트를 살펴보면 2개의 병렬 서버 그룹을 확인할 수 있다. 레포트의 Duration 값은 QC나 병렬 서버의 수행 시간과 무관하게 V$SQL_MONITOR 뷰의 sql_exec_start 값에서 last_refresh_time 값을 뺀 값이다. 병렬 쿼리의 정확한 수행 시간을 알 수 있다.
-- 5 SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR (SQL_ID => '0j0c6q3h2h3qa', TYPE => 'TEXT', report_level => 'ALL') FROM DUAL; Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : TUNA (132:24246) SQL ID : 0j0c6q3h2h3qa SQL Execution ID : 16777216 Execution Started : First Refresh Time : Last Refresh Time : Duration : 2s Module/Action : SQL*Plus/- Service : ora12cr2 Program : sqlplus.exe Fetch Calls : 2 Global Stats =========================================================================== | Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read | | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | =========================================================================== | 4.45 | 0.75 | 3.64 | 0.06 | 2 | 148K | 1187 | 1GB | =========================================================================== Parallel Execution Details (DOP=2 , Servers Allocated=4) ======================================================================================= | Name | Type | Group# | Server# | Elapsed | Cpu | IO | Other | | | | | | Time(s) | Time(s) | Waits(s) | Waits(s) | ======================================================================================= | PX Coordinator | QC | | | 0.01 | | | 0.01 | | p000 | Set 1 | 1 | 1 | 2.25 | 0.44 | 1.82 | | -- WITH | p001 | Set 1 | 1 | 2 | 2.19 | 0.31 | 1.83 | 0.05 | | p000 | Set 1 | 2 | 1 | 0.00 | | | 0.00 | -- SELECT | p001 | Set 1 | 2 | 2 | 0.00 | | | 0.00 | ======================================================================================= SQL Plan Monitoring Details (Plan Hash Value=3429451509) ===================================================================================================== | Id | Operation | Name | Time | Start | | | | | Active(s) | Active | ===================================================================================================== | 0 | SELECT STATEMENT | | 1 | +2 | | 1 | TEMP TABLE TRANSFORMATION | | 1 | +2 | | 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6D5B_D784C58 | 1 | +2 | | 3 | SORT AGGREGATE | | 1 | +2 | | 4 | PX COORDINATOR | | 1 | +2 | | 5 | PX SEND QC (RANDOM) | :TQ10000 | 1 | +2 | | 6 | SORT AGGREGATE | | 1 | +2 | | 7 | PX BLOCK ITERATOR | | 1 | +2 | | 8 | TABLE ACCESS FULL | T1 | 3 | +0 | | 9 | PX COORDINATOR | | 1 | +2 | | 10 | PX SEND QC (RANDOM) | :TQ20000 | 1 | +2 | | 11 | VIEW | | 1 | +2 | | 12 | PX BLOCK ITERATOR | | 1 | +2 | | 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6D5B_D784C58 | 1 | +2 | =====================================================================================================
MATERIALIZE된 서브 쿼리가 2개면 어떻게 될까? px_servers_executions 값이 6이어서 계산 값이 더욱 왜곡된다.
-- 6-1
WITH w1 AS (SELECT /*+ MATERIALIZE */ COUNT(*) AS c1 FROM t1)
, w2 AS (SELECT /*+ MATERIALIZE */ c1 FROM w1)
SELECT /*+ PARALLEL(2) */ * FROM w2;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |00:00:02.27 |
| 1 | TEMP TABLE TRANSFORMATION | |00:00:02.27 |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6D5C_D784C58 |00:00:02.25 |
| 3 | SORT AGGREGATE | |00:00:02.25 |
| 4 | PX COORDINATOR | |00:00:02.25 |
| 5 | PX SEND QC (RANDOM) | :TQ10000 |00:00:00.01 |
| 6 | SORT AGGREGATE | |00:00:00.01 |
| 7 | PX BLOCK ITERATOR | |00:00:00.01 |
|* 8 | TABLE ACCESS FULL | T1 |00:00:00.01 |
| 9 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6D5D_D784C58 |00:00:00.02 |
| 10 | SORT AGGREGATE | |00:00:00.02 |
| 11 | PX COORDINATOR | |00:00:00.02 |
| 12 | PX SEND QC (RANDOM) | :TQ20000 |00:00:00.01 |
| 13 | SORT AGGREGATE | |00:00:00.01 |
| 14 | VIEW | |00:00:00.01 |
| 15 | PX BLOCK ITERATOR | |00:00:00.01 |
|* 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6D5C_D784C58 |00:00:00.01 |
| 17 | PX COORDINATOR | |00:00:00.01 |
| 18 | PX SEND QC (RANDOM) | :TQ30000 |00:00:00.01 |
| 19 | VIEW | |00:00:00.01 |
| 20 | PX BLOCK ITERATOR | |00:00:00.01 |
|* 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6D5D_D784C58 |00:00:00.01 |
--------------------------------------------------------------------------------------------
-- 6-2
SELECT executions, px_servers_executions, ROUND (elapsed_time / 1e6, 2) AS elapsed_time
FROM v$sql
WHERE sql_id = 'f20nx8u7baad6';
EXECUTIONS PX_SERVERS_EXECUTIONS ELAPSED_TIME
---------- --------------------- ------------
1 6 4.50 -- 4.50 / 1 / (6 / 1) = 0.75
1개의 행이 선택되었습니다.
조인까지 포함되면 px_servers_executions 값이 8이다. 계산 값이 점점 더 왜곡된다.
-- 7-1
WITH w1 AS (SELECT /*+ MATERIALIZE */ COUNT(*) AS c1 FROM t1)
, w2 AS (SELECT /*+ MATERIALIZE */ c1 FROM w1)
SELECT /*+ PARALLEL(2) LEADING(A) PQ_DISTRIBUTE(B HASH HASH) */
*
FROM w1 a, w2 b
WHERE b.c1 = a.c1;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |00:00:02.63 |
| 1 | TEMP TABLE TRANSFORMATION | |00:00:02.63 |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6D8D_D784C58 |00:00:02.38 |
| 3 | SORT AGGREGATE | |00:00:02.25 |
| 4 | PX COORDINATOR | |00:00:02.25 |
| 5 | PX SEND QC (RANDOM) | :TQ10000 |00:00:00.01 |
| 6 | SORT AGGREGATE | |00:00:00.01 |
| 7 | PX BLOCK ITERATOR | |00:00:00.01 |
|* 8 | TABLE ACCESS FULL | T1 |00:00:00.01 |
| 9 | PX COORDINATOR | |00:00:00.05 |
| 10 | PX SEND QC (RANDOM) | :TQ20000 |00:00:00.01 |
| 11 | LOAD AS SELECT (TEMP SEGMENT MERGE) | SYS_TEMP_0FD9D6D8E_D784C58 |00:00:00.01 |
| 12 | VIEW | |00:00:00.01 |
| 13 | PX BLOCK ITERATOR | |00:00:00.01 |
|* 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6D8D_D784C58 |00:00:00.01 |
| 15 | PX COORDINATOR | |00:00:00.07 |
| 16 | PX SEND QC (RANDOM) | :TQ30002 |00:00:00.01 |
|* 17 | HASH JOIN BUFFERED | |00:00:00.01 |
| 18 | PX RECEIVE | |00:00:00.01 |
| 19 | PX SEND HASH | :TQ30000 |00:00:00.01 |
| 20 | VIEW | |00:00:00.01 |
| 21 | PX BLOCK ITERATOR | |00:00:00.01 |
|* 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6D8D_D784C58 |00:00:00.01 |
| 23 | PX RECEIVE | |00:00:00.01 |
| 24 | PX SEND HASH | :TQ30001 |00:00:00.01 |
| 25 | VIEW | |00:00:00.01 |
| 26 | PX BLOCK ITERATOR | |00:00:00.01 |
|* 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6D8E_D784C58 |00:00:00.01 |
--------------------------------------------------------------------------------------------
-- 7-2
SELECT executions, px_servers_executions, ROUND (elapsed_time / 1e6, 2) AS elapsed_time
FROM v$sql
WHERE sql_id = '3tmgv124wv0p0';
EXECUTIONS PX_SERVERS_EXECUTIONS ELAPSED_TIME
---------- --------------------- ------------
1 8 4.86 -- 4.86 / (8 / 1) = 0.60
1개의 행이 선택되었습니다.
MATERIALIZE된 서브 쿼리가 존재하거나, 조인이나 GROUP BY 절로 인해 2개의 병렬 서버 세트가 생성되는 경우 상기 방식으로 DOP를 계산할 수 없다. 결국 V$SQL_PLAN 뷰의 other_xml 열에서 DOP를 조회해야 한다. 아쉽게도 DML 문은 PARALLEL DML이 활성화된 경우에만 other_xml 열에 DOP 정보가 저장된다.
-- 8
SELECT sql_id
, NVL (XMLTYPE (other_xml).EXTRACT ('/other_xml/info[@type="dop"]/text()').GETNUMBERVAL (), 1) AS dop
FROM v$sql_plan
WHERE sql_id IN ('buyynq5ph0rh7', 'd0jvr41q5uy89', '0j0c6q3h2h3qa', 'f20nx8u7baad6', '3tmgv124wv0p0')
AND other_xml IS NOT NULL;
SQL_ID DOP
------------- ---
buyynq5ph0rh7 1 -- 2-1
d0jvr41q5uy89 2 -- 3-1
0j0c6q3h2h3qa 4 -- 4-1
f20nx8u7baad6 4 -- 5-1
3tmgv124wv0p0 4 -- 6-1
5 행이 선택되었습니다.
아래 쿼리로 병렬 쿼리의 대략적인 평균 수행 시간을 계산할 수 있지만, 병렬 쿼리에서 QC의 수행 시간 비중이 높다면 값이 왜곡될 수 있다. 아울러 PARALLEL_FORCE_LOCAL 파라미터가 FALSE인 경우에는 전체 인스턴스의 elapsed_time 값을 합산해야 한다. 인스턴스 별 평균 수행 시간을 계산할 수 없는 것이다.
-- 9
SELECT a.sql_id, a.executions, a.px_servers_executions, ROUND (a.elapsed_time / 1e6, 2) AS elapsed_time
, b.dop, ROUND (a.elapsed_time / a.executions / b.dop / 1e6, 2) AS actual_elapsed_time_pe
FROM (SELECT sql_id, executions, px_servers_executions, elapsed_time
FROM v$sql
WHERE sql_id IN ('buyynq5ph0rh7', 'd0jvr41q5uy89', '0j0c6q3h2h3qa', 'f20nx8u7baad6', '3tmgv124wv0p0')) a
, (SELECT sql_id
, NVL (XMLTYPE (other_xml).EXTRACT ('/other_xml/info[@type="dop"]/text()').GETNUMBERVAL (), 1) AS dop
FROM v$sql_plan
WHERE sql_id IN ('buyynq5ph0rh7', 'd0jvr41q5uy89', '0j0c6q3h2h3qa', 'f20nx8u7baad6', '3tmgv124wv0p0')
AND other_xml IS NOT NULL) b
WHERE b.sql_id = a.sql_id;
SQL_ID EXECUTIONS PX_SERVERS_EXECUTIONS ELAPSED_TIME DOP ACTUAL_ELAPSED_TIME_PE
------------- ---------- --------------------- ------------ --- ----------------------
buyynq5ph0rh7 1 0 2.20 1 2.20 -- 2-1
d0jvr41q5uy89 1 2 4.45 2 2.22 -- 3-1
0j0c6q3h2h3qa 1 4 4.42 2 2.21 -- 4-1
f20nx8u7baad6 1 6 4.70 2 2.35 -- 5-1
3tmgv124wv0p0 1 8 4.86 2 2.43 -- 6-1
5 행이 선택되었습니다.
근본적인 제약으로 인해 V$SQL 뷰와 elapsed_time 값과 DBA_HIST_SQLSTAT 뷰의 elapsed_time_delta 값으로는 병렬 쿼리의 평균 수행 시간을 계산하는 것은 거의 불가능에 가깝다. 앞서 살펴본 V$SQL_MONITOR 뷰로 수행 시간을 계산하는 편이 바람직하다.
'Oracle > Tuning' 카테고리의 다른 글
| 행 복제 성능 저하 사례 (0) | 2019.10.05 |
|---|---|
| 중복 표현식 (0) | 2019.10.02 |
| FBI 활용 사례 (0) | 2019.09.25 |
| DUAL CONNECT BY 쿼리 성능 개선 방안 (0) | 2019.09.11 |
| UPDATE 문 성능 개선 사례 (0) | 2019.09.03 |
