Parallel Execution Session Monitor (vpx.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

Parallel Execution Session Monitor

This script provides detailed monitoring of Oracle parallel execution sessions, showing the relationship between query coordinators (QC) and their parallel slaves, along with degree of parallelism information.

Script: vpx.sql

col username for a12
col "QC SID" for A6
col SID for A6
col "QC/Slave" for A10
col "Requested DOP" for 9999
col "Actual DOP" for 9999
col "slave set" for  A10
col sql_hash_value format 9999999999999999 heading 'SQL Hash Value'
set lines 132
select
  decode(px.qcinst_id,NULL,username,
        ' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",
  decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
  to_char( px.server_set) "Slave Set",
  to_char(s.sid) "SID",
  decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
  px.req_degree "Requested DOP",
  px.degree "Actual DOP",
  s.SQL_HASH_VALUE
from
  v$px_session px,
  v$session s
where px.sid=s.sid (+)
and px.serial#=s.serial#
order by 5 , 1 desc
/

What This Script Does

This script monitors Oracle’s parallel execution infrastructure by:

• Identifying Query Coordinators: Shows which sessions are coordinating parallel operations
• Mapping Parallel Slaves: Displays which slave processes belong to each coordinator
• Showing DOP Information: Compares requested vs. actual degree of parallelism
• Grouping by Operation: Orders results by coordinator to group related slaves
• Tracking SQL Operations: Shows SQL hash values for correlation

Usage

-- Monitor current parallel operations
@vpx.sql

-- Run during heavy parallel workload for best results
-- Can be run repeatedly to monitor parallel execution patterns

Required Privileges

• SELECT privilege on V$PX_SESSION
• SELECT privilege on V$SESSION
• Typically requires DBA role or SELECT_CATALOG_ROLE

Sample Output

Username     QC/Slave   Slave Set  SID    QC SID Requested DOP Actual DOP  SQL Hash Value
------------ ---------- ---------- ------ ------ ------------- ----------- ----------------
SALES_USER   QC                    156    156               4           4  2034815321748235
 - ora       (Slave)    1          157    156               4           4  2034815321748235
 - ora       (Slave)    1          158    156               4           4  2034815321748235
 - ora       (Slave)    2          159    156               4           4  2034815321748235
 - ora       (Slave)    2          160    156               4           4  2034815321748235
HR_USER      QC                    203    203               8           6  8901234567823456
 - ora       (Slave)    1          204    203               8           6  8901234567823456
 - ora       (Slave)    1          205    203               8           6  8901234567823456
 - ora       (Slave)    1          206    203               8           6  8901234567823456
 - ora       (Slave)    2          207    203               8           6  8901234567823456
 - ora       (Slave)    2          208    203               8           6  8901234567823456
 - ora       (Slave)    2          209    203               8           6  8901234567823456

Key Output Columns

Column	Description
Username	User running the query (QC) or ’- ora’ for slave processes
QC/Slave	Role: ‘QC’ for Query Coordinator, ‘(Slave)’ for parallel slaves
Slave Set	Slave set number (1 or 2) for organizing parallel slaves
SID	Session ID of this particular session
QC SID	Session ID of the Query Coordinator for this group
Requested DOP	Degree of Parallelism requested by the query
Actual DOP	Actual Degree of Parallelism allocated by Oracle
SQL Hash Value	Hash value of the SQL statement being executed

Understanding Parallel Execution

Query Coordinator (QC)

• Role: Coordinates the parallel execution and merges results
• Identification: Shows actual username and ‘QC’ in QC/Slave column
• Resources: Uses minimal CPU, mainly for coordination

Parallel Slaves

• Role: Execute portions of the SQL statement in parallel
• Identification: Show ’- ora’ username and ‘(Slave)’ designation
• Organization: Grouped into slave sets (typically 1 and 2)
• Resources: Perform the actual work (scans, joins, sorts)

Slave Sets

• Set 1: First group of parallel slaves
• Set 2: Second group of parallel slaves (for operations requiring redistribution)
• Usage: Complex operations may use both sets for different phases

Common Use Cases

1. Monitor Parallel Query Performance

-- Run this script during heavy parallel workload
@vpx.sql

-- Check if DOP is being downgraded
SELECT DISTINCT qcsid, req_degree, degree
FROM v$px_session
WHERE req_degree != degree;

2. Identify Resource Contention

-- Find queries with reduced parallelism
SELECT px.qcsid, s.username, s.sql_id,
       px.req_degree as requested,
       px.degree as actual,
       px.req_degree - px.degree as shortfall
FROM v$px_session px, v$session s
WHERE px.sid = s.sid
  AND px.qcinst_id IS NULL  -- QC only
  AND px.req_degree > px.degree;

3. Analyze Parallel Execution History

-- Check recent parallel execution from AWR
SELECT sql_id, plan_hash_value,
       px_servers_requested, px_servers_allocated,
       executions, elapsed_time_delta/1000000 as elapsed_sec
FROM dba_hist_sqlstat
WHERE px_servers_requested > 1
  AND snap_id BETWEEN &start_snap AND &end_snap
ORDER BY elapsed_time_delta DESC;

4. Monitor Parallel Execution Waits

-- Check what parallel slaves are waiting for
SELECT s.sid, s.username, s.event, s.state,
       s.seconds_in_wait, px.server_set
FROM v$session s, v$px_session px
WHERE s.sid = px.sid
  AND px.qcinst_id IS NOT NULL  -- Slaves only
  AND s.status = 'ACTIVE'
ORDER BY px.qcsid, px.server_set, s.sid;

Troubleshooting Parallel Execution

DOP Downgrade Analysis

-- Analyze why DOP was reduced
SELECT parameter, value, isdefault
FROM v$parameter
WHERE name IN (
  'parallel_max_servers',
  'parallel_servers_target',
  'parallel_degree_limit',
  'parallel_adaptive_multi_user'
);

Resource Limitation Check

-- Check parallel server usage
SELECT servers_started, servers_shutdown,
       servers_highwater, servers_cleaned_up
FROM v$pq_sysstat
WHERE statistic = 'Servers Started';

-- Check if hitting parallel server limits
SELECT * FROM v$resource_limit
WHERE resource_name LIKE '%parallel%';

SQL Statement Analysis

-- Get SQL text for parallel operations
SELECT DISTINCT s.sql_id, s.sql_text
FROM v$session s, v$px_session px
WHERE s.sid = px.sid
  AND px.qcinst_id IS NULL  -- QC only
  AND s.sql_text IS NOT NULL;

Performance Optimization Tips

1. Optimal DOP Settings

• CPU-bound operations: DOP = number of CPU cores
• I/O-bound operations: DOP can exceed CPU cores
• Mixed workloads: Consider parallel_adaptive_multi_user=true

2. Monitoring Best Practices

• Run during peak hours: Get realistic parallel usage patterns
• Track DOP downgrades: Indicates resource constraints
• Monitor wait events: Identify parallel execution bottlenecks
• Check skew: Ensure work is evenly distributed

3. Common Issues

• DOP Downgrade: Insufficient parallel servers available
• Skewed Distribution: Uneven work distribution among slaves
• Interconnect Bottlenecks: High parallel execution waits
• Memory Pressure: PGA memory limits affecting parallel operations

Integration with Other Scripts

This script works well with:

• Session Monitoring (gvsess.sql) - For detailed session analysis
• Active Session History (gvsessactive.sql) - For session wait analysis
• Database Health Check (db.sql) - For overall system analysis
• System Metrics (sysmetric_cpu.sql) - For CPU utilization during parallel operations

Automation Example

-- Create a monitoring script for parallel execution
-- Save as monitor_parallel.sql

SET PAGESIZE 1000
SET LINESIZE 150
COLUMN timestamp FORMAT A20

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') as timestamp,
       COUNT(DISTINCT qcsid) as active_parallel_queries,
       SUM(CASE WHEN qcinst_id IS NULL THEN 1 ELSE 0 END) as coordinators,
       SUM(CASE WHEN qcinst_id IS NOT NULL THEN 1 ELSE 0 END) as slaves,
       AVG(degree) as avg_actual_dop,
       AVG(req_degree) as avg_requested_dop
FROM v$px_session;

-- Detailed parallel session information
@vpx.sql

This script is essential for DBAs managing environments with heavy parallel processing workloads and provides crucial insights into Oracle’s parallel execution infrastructure.