개요
Cursor Cache 데이터와 Real-time SQL Monitoring 데이터는 병렬 SQL 문를 포함하고 있습니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 AS SELECT * FROM t1;
아래의 네 가지 방식으로 병렬 SQL 문을 수행하겠습니다. 2-1번은 PARALLEL(2) 힌트, 2-2번은 PARALLEL 힌트를 사용합니다. 2-3번은 테이블의 PARALLEL 속성, 2-4번은 세션 레벨의 DOP를 설정합니다.
-- 2-1
SELECT /*+ PARALLEL(2) */ COUNT (*) FROM t1;
-- 2-2
SELECT /*+ PARALLEL */ COUNT (*) FROM t1;
-- 2-3
ALTER TABLE t2 PARALLEL 2;
SELECT /* TABLE */ COUNT (*) FROM t2;
ALTER TABLE t2 NOPARALLEL;
-- 2-4
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2;
SELECT /* SESSION */ COUNT (*) FROM t1;
ALTER SESSION ENABLE PARALLEL QUERY;
Cursor Cache
아래 쿼리는 V$SQL 뷰에서 병렬 SQL 문을 조회합니다. V$SQL 뷰의 px_servers_executions 칼럼은 PX 서버로 수행된 SQL 문의 실행 횟수를 나타냅니다. 직렬 SQL 문은 이 값이 0으로 표시됩니다.
-- 3
SELECT sql_id
, executions
, px_servers_executions
, px_servers_executions / executions AS px_pe
, sql_text
FROM v$sql
WHERE px_servers_executions > 0;
SQL_ID executions PX_SERVERS_EXECUTIONS PX_PE SQL_TEXT
------------- ----------- --------------------- ----- -------------------------------------------
3yv5qu2nct0aj 1 2 2 SELECT /*+ PARALLEL(2) */ COUNT (*) FROM t1
drnun0v2zur45 1 2 2 SELECT /*+ PARALLEL */ COUNT (*) FROM t1
8r0j2631ft5xd 1 2 2 SELECT /* TABLE */ COUNT (*) FROM t2
c3gpg3jn1ns5v 1 2 2 SELECT /* SESSION */ COUNT (*) FROM t1
4 행이 선택되었습니다.
아래 쿼리는 V$SQL_PLAN 뷰에서 병렬 SQL 문을 조회합니다. V$SQL_PLAN 뷰의 other_xml 칼럼에 병렬 SQL 문의 상세 정보가 저장되어 있습니다. 테이블의 PARALLEL 속성과 세션 레벨의 DOP를 설정한 SQL 문은 other_xml 칼럼에 병렬 정보가 저장되지 않습니다.
-- 4
SELECT a.sql_id
, b.dop_op_reason
, b.dop_reason
, b.dop
FROM v$sql_plan a
, XMLTABLE ('/other_xml'
PASSING XMLTYPE (a.other_xml)
COLUMNS
dop_op_reason VARCHAR2(255) PATH 'info[@type="dop_op_reason"]'
, dop_reason VARCHAR2(255) PATH 'info[@type="dop_reason"]'
, dop NUMBER PATH 'info[@type="dop"]'
) b
WHERE a.other_xml IS NOT NULL
AND b.dop > 1;
SQL_ID DOP_OP_REASON DOP_REASON DOP
------------- ---------------------- -------------- ---
3yv5qu2nct0aj hint 2
drnun0v2zur45 scan of object TUNA.T1 2
8r0j2631ft5xd table property 2
c3gpg3jn1ns5v session 2
4 행이 선택되었습니다.
아래는 DBMS_XPLAN.DISPLAY_CURSOR 함수로 커서를 조회한 결과입니다.
-- 5-1
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (sql_id => '3yv5qu2nct0aj', format => 'ADVANCED'));
Note
-----
- Degree of Parallelism is 2 because of hint
-- 5-2
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (sql_id => 'drnun0v2zur45', format => 'ADVANCED'));
Automatic Degree of Parallelism Information:
--------------------------------------------
- Degree of Parallelism of 2 is derived from scan of object TUNA.T1
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
-- 5-3
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (sql_id => '8r0j2631ft5xd', format => 'ADVANCED'));
Note
-----
- Degree of Parallelism is 2 because of table property
-- 5-4
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (sql_id => 'c3gpg3jn1ns5v', format => 'ADVANCED'));
Note
-----
- Degree of Parallelism is 2 because of session
AWR에서도 Cursor Cache와 유사한 방식으로 병렬 SQL 문을 조회할 수 있습니다.
-- 6
SELECT a.sql_id
, a.px_pe
, b.dop_op_reason
, b.dop_reason
, b.dop
, c.sql_text
FROM (SELECT DISTINCT
dbid
, sql_id
, px_servers_execs_delta / executions_delta AS px_pe
FROM sys.wrh$_sqlstat
WHERE px_servers_execs_delta > 0) a
, (SELECT a.dbid
, a.sql_id
, b.dop_op_reason
, b.dop_reason
, b.dop
FROM sys.wrh$_sql_plan a
, XMLTABLE ('/other_xml'
PASSING XMLTYPE (a.other_xml)
COLUMNS
dop_op_reason VARCHAR2(255) PATH 'info[@type="dop_op_reason"]'
, dop_reason VARCHAR2(255) PATH 'info[@type="dop_reason"]'
, dop NUMBER PATH 'info[@type="dop"]'
) b
WHERE a.other_xml LIKE '%dop%'
AND b.dop > 1) b
, sys.wrh$_sqltext c
WHERE b.sql_id(+) = a.dbid
AND b.sql_id(+) = a.sql_id
AND c.dbid(+) = a.dbid
AND c.sql_id(+) = a.sql_id;
Real-time SQL Monitoring
아래 쿼리는 V$SQL_MONITOR 뷰에서 병렬 SQL 문을 조회합니다.
-- 7
SELECT sql_id
, px_maxdop
, px_servers_requested
, px_servers_allocated
, CASE
WHEN elapsed_time < 1e6
THEN ROUND (elapsed_time / 1e6, 2)
ELSE ROUND ((last_refresh_time - sql_exec_start) * 24 * 60 * 60)
END AS duration
FROM v$sql_monitor
WHERE process_name = 'ora'
AND px_maxdop > 1;
12.1 이상 버전은 AWR에 Real-time SQL Monitor 정보를 저장합니다. 아래 쿼리는 DBA_HIST_REPORTS 뷰에서 병렬 SQL 문을 조회합니다.
-- 8: >= 12.1
SELECT b.sql_id
, b.px_maxdop
, b.px_servers_requested
, b.px_servers_allocated
, b.duration
FROM dba_hist_reports a
, XMLTABLE ('/report_repository_summary/sql'
PASSING XMLTYPE (a.report_summary)
COLUMNS
sql_id VARCHAR2(13) PATH '@sql_id'
, px_maxdop NUMBER PATH 'dop'
, px_servers_requested NUMBER PATH 'px_servers_requested'
, px_servers_allocated NUMBER PATH 'px_servers_allocated'
, duration NUMBER PATH 'stats/stat[@name="duration"]'
) b
WHERE a.component_name = 'sqlmonitor'
AND a.report_summary LIKE '%dop%'
AND b.px_maxdop > 1;