Parallel Execution Session Direct Path I/O Statistics (vsessiodpx.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.

What This Script Does

This script queries Oracle views to provide database information via the vsessiodpx.sql script.

The Script

rem  vsessiod.sql
rem
ttitle 'Session Direct Path IO'
rem
col sid format 990 heading 'SID'
col logical_reads format 999,999,999,999 heading 'LOGICAL|READS'
col physical_reads format 999,999,999,999 heading 'PHYSICAL|READS'
col physical_reads_direct format 999,999,999,999 heading 'PHYSICAL|READS|DIRECT'
col physical_writes format 999,999,999,999 heading 'PHYSICAL|WRITES'
col physical_writes_direct format 999,999,999,999 heading 'PHYSICAL|WRITES|DIRECT'
col physical_writes_direct_lob format 999,999,999,999 heading 'PHYSICAL|WRITES|DIRECT|LOB'
rem
break on sid
rem
select
       s.sid,
       sum(decode(n.name, 'session logical reads', s.value, 0)) logical_reads,
       sum(decode(n.name, 'physical reads', s.value, 0)) physical_reads,
       sum(decode(n.name, 'physical reads direct', s.value, 0)) physical_reads_direct,
       sum(decode(n.name, 'physical writes', s.value, 0)) physical_writes,
       sum(decode(n.name, 'physical writes direct', s.value, 0)) physical_writes_direct,
       sum(decode(n.name, 'physical writes direct (lob)', s.value, 0)) physical_writes_direct_lob
  from v$sesstat s,
       v$statname n,
       v$px_session px
where px.qcsid like '&parent_sid'
  and s.sid = px.sid
  and s.value <> 0
  and (n.name like 'phy%' or n.name = 'session logical reads')
  and n.statistic# = s.statistic#
group by s.sid
order by 1
/

What This Script Does

This specialized script monitors direct path I/O statistics for parallel execution (PX) slave sessions associated with a specific query coordinator session. It provides detailed I/O metrics for each parallel slave, helping DBAs understand how parallel operations distribute I/O workload and identify potential performance bottlenecks in parallel execution environments.

Key Features

• Parallel Session Focus: Monitors only PX slave sessions for a specific coordinator
• Direct Path I/O Analysis: Tracks direct reads and writes bypassing buffer cache
• Complete I/O Metrics: Shows both conventional and direct path operations
• Per-Session Breakdown: Individual statistics for each parallel slave
• LOB Operations: Includes direct LOB write operations
• Active Sessions Only: Filters sessions with non-zero I/O values

Usage

@vsessiodpx.sql

Input Parameters:

• parent_sid: Query Coordinator Session ID (QC SID) of the parallel operation

Required Privileges

SELECT on V$SESSTAT
SELECT on V$STATNAME
SELECT on V$PX_SESSION

Sample Output

                                          Session Direct Path IO

 SID        LOGICAL     PHYSICAL      PHYSICAL    PHYSICAL      PHYSICAL      PHYSICAL
            READS        READS   READS DIRECT      WRITES  WRITES DIRECT WRITES DIRECT
                                                                                    LOB
---- ------------ ------------ ------------ ------------ ------------ ------------
 145    1,234,567      456,789      423,156       12,345       10,234            0
 147    1,198,432      441,223      409,887       11,967        9,876            0
 149    1,267,890      467,345      434,221       12,678       10,567            0
 151    1,223,445      449,567      416,332       12,123        9,998            0
 153    1,245,678      459,123      425,887       12,456       10,345            0
 155    1,189,234      438,901      406,554       11,892        9,723        1,234
 157    1,278,901      471,234      437,665       12,789       10,678            0
 159    1,234,567      456,321      422,998       12,345       10,234            0

Key Output Columns

• SID: Session ID of the parallel slave session
• LOGICAL READS: Total logical reads from buffer cache
• PHYSICAL READS: Total physical reads from disk (including buffer cache loads)
• PHYSICAL READS DIRECT: Direct path reads bypassing buffer cache
• PHYSICAL WRITES: Total physical writes to disk
• PHYSICAL WRITES DIRECT: Direct path writes bypassing buffer cache
• PHYSICAL WRITES DIRECT LOB: Direct LOB writes bypassing buffer cache

Understanding Parallel Execution I/O

Direct Path Operations

Direct Path Reads

-- Occurs during:
-- 1. Parallel full table scans
-- 2. Parallel index fast full scans
-- 3. Hash joins with large hash tables
-- 4. Sort operations exceeding PGA memory
-- 5. Parallel DML operations

Direct Path Writes

-- Occurs during:
-- 1. Parallel DML (INSERT, UPDATE, DELETE)
-- 2. CREATE TABLE AS SELECT (CTAS)
-- 3. Direct load operations (SQL*Loader)
-- 4. Temporary segment operations
-- 5. Sort spills to temporary tablespace

I/O Distribution Analysis

Balanced Distribution

-- Good parallel execution shows:
-- Similar I/O values across all slaves
-- Even workload distribution
-- Consistent performance across slaves
-- Minimal variance in operation counts

Unbalanced Distribution

-- Problems indicated by:
-- Significant variance in I/O between slaves
-- Some slaves with much higher I/O
-- Skewed data distribution
-- Potential parallel execution bottlenecks

Parallel Execution Analysis

Query Coordinator Identification

Finding Active Parallel Operations

-- Identify query coordinators:
SELECT DISTINCT qcsid, sql_id, degree
FROM v$px_session
WHERE qcsid IS NOT NULL
ORDER BY qcsid;

Session Hierarchy

-- Understand session relationships:
SELECT qcsid "Coordinator", sid "Slave", server_group, server_set
FROM v$px_session
WHERE qcsid = &parent_sid
ORDER BY server_group, server_set, sid;

Performance Assessment

I/O Efficiency Metrics

-- Calculate efficiency ratios:
-- Direct Read Ratio = Physical Reads Direct / Physical Reads
-- Should be high (>80%) for parallel operations

-- Buffer Cache Bypass Rate = Direct Reads / Total Reads
-- Indicates how much I/O bypasses buffer cache

Workload Balance

-- Assess distribution:
-- Standard deviation of I/O across slaves
-- Coefficient of variation
-- Maximum vs minimum slave I/O
-- Identify outlier slaves

Common Use Cases

1. Parallel Query Performance Tuning

   • Analyze I/O distribution across parallel slaves
   • Identify load balancing issues
   • Optimize parallel execution parameters
   • Monitor direct path operation efficiency

2. Parallel DML Monitoring

   • Track direct path write performance
   • Monitor parallel insert/update operations
   • Analyze temporary space usage
   • Assess parallel DML scalability

3. Data Warehouse Operations

   • Monitor ETL parallel operations
   • Analyze large table scan performance
   • Track CTAS operation efficiency
   • Optimize bulk loading operations

4. Troubleshooting Parallel Execution

   • Identify slow parallel slaves
   • Find I/O bottlenecks in parallel operations
   • Analyze parallel execution imbalances
   • Debug parallel query performance issues

Performance Analysis Techniques

Load Balance Assessment

Statistical Analysis

-- Calculate variance across slaves:
-- 1. Collect I/O values for all slaves
-- 2. Calculate mean, standard deviation
-- 3. Identify outliers (>2 std dev from mean)
-- 4. Determine coefficient of variation

Visual Analysis

-- Compare slave performance:
-- Plot I/O values across slaves
-- Look for significant variations
-- Identify consistent performers
-- Find problem slaves

Direct Path Efficiency

Direct vs. Conventional I/O Ratio

-- For parallel operations, expect:
-- High direct read percentage (>80%)
-- Minimal conventional buffer cache usage
-- Direct writes for DML operations
-- LOB operations using direct path

Buffer Cache Impact

-- Monitor buffer cache effects:
-- Logical reads should be minimal for large scans
-- Physical reads should be primarily direct
-- Buffer cache shouldn't be flooded
-- Memory allocation should be appropriate

Optimization Strategies

Parallel Degree Tuning

Assess Current Utilization

-- Check if all slaves are active:
-- Count active slaves vs. configured degree
-- Monitor slave utilization patterns
-- Identify unused parallel capacity

Right-sizing Parallel Degree

-- Factors to consider:
-- I/O subsystem capacity
-- Available parallel slaves
-- System load and resource availability
-- Query complexity and data distribution

Data Distribution Optimization

Partition-wise Operations

-- Improve load balance through:
-- Proper table partitioning
-- Partition-wise joins
-- Even data distribution across partitions
-- Appropriate partition elimination

Hash Distribution

-- For hash joins and aggregations:
-- Monitor hash bucket distribution
-- Check for data skew
-- Optimize hash join parameters
-- Consider alternative join methods

Advanced Monitoring

Real-time Analysis

-- Monitor during execution:
-- Run script multiple times during long operations
-- Track I/O progression over time
-- Identify performance degradation
-- Monitor resource utilization trends

Historical Comparison

-- Compare with previous executions:
-- Track I/O patterns over time
-- Identify performance regressions
-- Document optimization improvements
-- Plan capacity requirements

Integration with Other Metrics

-- Correlate with:
-- V$PX_PROCESS_SYSSTAT for process statistics
-- V$PQ_TQSTAT for table queue statistics
-- V$SESSION_LONGOPS for operation progress
-- V$SQL_MONITOR for SQL monitoring data

Troubleshooting Common Issues

Parallel Slave Imbalance

Symptoms

• Significant variance in I/O across slaves
• Some slaves finishing much earlier than others
• Overall query performance degradation
• Resource utilization imbalances

Resolution Strategies

-- Address through:
-- Improved data distribution (partitioning)
-- Parallel degree adjustment
-- Query rewriting for better parallelization
-- System resource optimization

Direct Path I/O Issues

Low Direct Path Percentage

-- Possible causes:
-- Small tables not qualifying for direct path
-- Inadequate PGA memory allocation
-- Buffer cache interference
-- Suboptimal parallel parameters

High Direct Write Activity

-- Monitor for:
-- Excessive temporary space usage
-- Sort/hash operations spilling to disk
-- Inadequate PGA memory
-- Need for PGA tuning

Related Scripts

• gvpq.sql - Global parallel query statistics
• vsessio.sql - General session I/O statistics
• gvsessab.sql - Session activity analysis
• vslop.sql - Long operation monitoring

Best Practices

1. Regular Monitoring

   • Monitor critical parallel operations
   • Establish I/O distribution baselines
   • Track performance trends over time
   • Document optimization results

2. Performance Optimization

   • Ensure balanced workload distribution
   • Optimize parallel degree settings
   • Monitor and tune PGA memory
   • Consider data partitioning strategies

3. Troubleshooting Approach

   • Start with coordinator session identification
   • Analyze I/O distribution patterns
   • Identify bottleneck slaves
   • Correlate with system resource usage