Oracle Database - Parallel Execution Monitoring
Table of Contents
1 - About
There are several ways to monitor parallel execution. This section discusses various options.
2 - Articles Related
3 - Data Dictionary
3.1 - The (G)V$ parallel execution views
Specific parallel execution performance views start with:
- (G)V$_PQ and
- the V$ views give you an instance-specific view,
- the GV$ views are useful in a Real Application Cluster (RAC) environment to extract cluster-wide information.
(The GV$ view contains only the instance ID as additional column compared to the V$view.)
3.2 - Example
3.2.1 - Parallel execution activity across a cluster
SELECT inst_id , STATUS , COUNT(1) px_servers# FROM gv$px_process GROUP BY inst_id, STATUS ORDER BY inst_id, STATUS ;
INST_ID STATUS PX_SERVERS# ---------- --------- ----------- 1 AVAILABLE 4 1 IN USE 12 2 AVAILABLE 8 2 IN USE 8 3 AVAILABLE 6 3 IN USE 10 4 AVAILABLE 2 4 IN USE 14
3.2.2 - Parallel Process Detail met DOP
SELECT DECODE(px.qcinst_id, NULL, username, ' – ' || LOWER(SUBSTR(pp.SERVER_NAME, LENGTH(pp.SERVER_NAME)-4,4) ) ) "Username", DECODE(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave", TO_CHAR( px.server_set) "SlaveSet", TO_CHAR(s.sid) "SID", TO_CHAR(px.inst_id) "Slave INST", DECODE(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) AS STATE, CASE sw.state WHEN 'WAITING' THEN SUBSTR(sw.event,1,30) ELSE NULL END AS wait_event, DECODE(px.qcinst_id, NULL ,TO_CHAR(s.sid) ,px.qcsid) "QC SID", TO_CHAR(px.qcinst_id) "QC INST", px.req_degree "Req. DOP", px.degree "Actual DOP" FROM gv$px_session px, gv$session s , gv$px_process pp, gv$session_wait sw WHERE px.sid =s.sid (+) AND px.serial# =s.serial#(+) AND px.inst_id = s.inst_id(+) AND px.sid = pp.sid (+) AND px.serial# =pp.serial#(+) AND sw.sid = s.sid AND sw.inst_id = s.inst_id ORDER BY DECODE(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID), px.QCSID, DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID, pp.SERVER_NAME
4 - Execution Plan
Starting with Oracle Database 10g, for a given query there is a single cursor that is executed by all parallel servers.
All parallel execution information is in the single execution plan that is used by every parallel server process.
The basic plan information will be the same for all these mechanisms, so we will discuss how to identify and interpret the most fundamental parallel execution optimization, namely a partition-wise join.
5 - Monitoring PARALLEL DML Executions
Check that you are running mapping in parallel mode by executing the following SQL statement to count the executed “Parallel DML/Query” statement
COLUMN name format a50 COLUMN VALUE format 999,999 SELECT NAME, VALUE FROM GV$SYSSTAT WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%' OR UPPER (NAME) LIKE '%PARALLELIZED%' OR UPPER (NAME) LIKE '%PX%' ;
If you are running procedure/package/mapping in parallel mode, you should see “DML statements parallelized ” increased by 1 every time the mapping was invoked. If not you do not see this increase, then the procedure/package/mapping was not invoked as “parallel DML”.
If you see “queries parallelized” increased by 1 (one) instead, then typically it means that the SELECT statement inside of the INSERT was parallelized but that INSERT itself was not.