Top Physical I/O SQL Analysis (vsqlphy.sql)

What This Script Does

This script identifies SQL statements with the highest physical disk reads, focusing specifically on I/O-intensive queries that may benefit from optimization. It provides detailed I/O metrics, execution ratios, and cache efficiency indicators to help prioritize I/O optimization efforts. The script is essential for identifying and resolving I/O bottlenecks, optimizing storage performance, and improving overall system efficiency.

The Script

rem  vsqlphy.sql
rem
ttitle 'Shared SQL Area - Top Physical I/O'
rem
col sql_text format a80 heading 'SQL Text'
col sharable_mem format 999999 heading 'SHARED|MEMORY'
col persistent_mem format 999999 heading 'PERSIST|MEMORY'
col runtime_mem format 999999 heading 'RUNTIME|MEMORY'
col loads format 9999 heading 'LOADS'
col invalidations format 9999 heading 'INVALID'
col parse_calls format 99999 heading 'PARSE|CALLS'
col parse_pct format 999 heading 'PARSE|PCT'
col executions format 99999999 heading 'EXECUTE'
col log_phy format 9999 heading 'LOG/|PHY'
col disk_reads format 999999999 heading 'DISK|READS'
col phy_exe format 99999 heading 'PHY/|EXE'
col buffer_gets format 9999999999 heading 'BUFFER|GETS'
col log_exe format 9999999 heading 'LOG/|EXE'
col sorts format 99 heading 'S'
col address format a8 heading 'ADDRESS'
rem
break on report
compute sum of parse_calls executions disk_reads buffer_gets on report
rem
select
/*
       sharable_mem,
       persistent_mem,
       runtime_mem,
       version_count,
       loaded_versions,
       open_versions,
       kept_versions,
       users_opening,
       users_executing,
       loads,
       invalidations,
*/
       address,
       sorts,
       executions,
       parse_calls,
       parse_calls*100/decode(executions,0,null,executions) parse_pct,
       buffer_gets/decode(disk_reads,0,null,disk_reads) log_phy,
       disk_reads,
       disk_reads/decode(executions,0,null,executions) phy_exe,
       buffer_gets,
       buffer_gets/decode(executions,0,null,executions) log_exe,
       sql_text
  from v$sqlarea
 where disk_reads >= &physical
 order by disk_reads desc;

Key Features

Physical I/O Focus: Specifically targets high disk read operations
Configurable Threshold: Filter statements based on minimum physical reads
I/O Efficiency Metrics: Shows logical/physical read ratios and per-execution averages
Cache Hit Analysis: Calculates buffer cache effectiveness
Parse Efficiency: Displays parse call percentages
Comprehensive Sorting: Shows sort operations requiring disk I/O
Memory Context: Optional memory usage information

Usage

@vsqlphy.sql

Input Parameters:

physical: Minimum physical disk reads threshold

Required Privileges

SELECT on V$SQLAREA

Sample Output

                                    Shared SQL Area - Top Physical I/O

ADDRESS   S  EXECUTE PARSE PARSE LOG/ DISK READS PHY/ BUFFER GETS  LOG/
                      CALLS   PCT  PHY            EXE             EXE
-------- -- -------- ----- ----- ---- --------- ----- ---------- ----
1A2B3C4D  5  1234567 23456    19   85  9876543   800 8765432109  710
SQL Text: SELECT * FROM large_table lt, dimension_table dt WHERE lt.dim_key = dt.dim_key AND lt.date_col BETWEEN :1 AND :2

2B3C4D5E  2   234567  4567    19  125  5432109   231 6789012345  289
SQL Text: INSERT INTO archive_table SELECT * FROM transaction_table WHERE status = 'COMPLETED' AND created_date < :1

3C4D5E6F  0   123456  1234    10   45  3456789   280 1567890234  127
SQL Text: UPDATE customer_table SET last_access = SYSDATE WHERE customer_id IN (SELECT customer_id FROM session_log WHERE session_date = :1)

4D5E6F7G  8    98765   987    10   67  2345678   237 9876543210  100
SQL Text: SELECT SUM(amount), COUNT(*) FROM financial_data fd, account_master am WHERE fd.account_id = am.account_id GROUP BY am.region

5E6F7G8H  1    87654   876    10   89  1234567   141 8765432109   99
SQL Text: DELETE FROM temp_processing_table WHERE batch_id = :1 AND process_flag = 'COMPLETE'

                      -------- ----- --------- ---------- ----
sum                    1778399 31120  22369686 35764226007

Key Output Columns

ADDRESS: Memory address of the SQL statement
S: Number of sort operations performed
EXECUTE: Total number of executions
PARSE CALLS: Number of parse operations
PARSE PCT: Percentage of executions requiring parsing
LOG/PHY: Ratio of logical reads to physical reads (cache hit indicator)
DISK READS: Total physical disk reads
PHY/EXE: Average physical reads per execution
BUFFER GETS: Total logical reads (buffer gets)
LOG/EXE: Average logical reads per execution
SQL Text: The actual SQL statement

Understanding Physical I/O Metrics

I/O Efficiency Analysis

Cache Hit Ratio (LOG/PHY)

-- Cache effectiveness interpretation:
-- >100: Excellent cache utilization
-- 50-100: Good cache performance
-- 20-50: Fair cache utilization
-- <20: Poor cache efficiency (optimization needed)

Physical Reads Per Execution

-- PHY/EXE analysis:
-- <10: Efficient I/O pattern
-- 10-100: Moderate I/O requirements
-- 100-1000: High I/O operations
-- >1000: Very intensive I/O (optimization critical)

Sort Operations Impact

-- Sort operations indicate:
-- Temporary tablespace usage
-- Disk-based sorting operations
-- Memory shortage for sort operations
-- ORDER BY, GROUP BY, DISTINCT operations

Sort Optimization

-- Reduce sort I/O through:
-- Increasing SORT_AREA_SIZE
-- Optimizing ORDER BY clauses
-- Using indexes for sorting
-- Reviewing GROUP BY strategies

Common Use Cases

I/O Bottleneck Resolution
- Identify statements causing I/O pressure
- Prioritize I/O optimization efforts
- Support storage performance tuning
- Plan infrastructure improvements
Buffer Cache Optimization
- Analyze cache hit ratios
- Identify statements bypassing cache
- Support memory allocation tuning
- Optimize data access patterns
Index Optimization
- Find full table scan candidates
- Identify missing index opportunities
- Support index strategy development
- Validate indexing effectiveness
Application Performance Tuning
- Optimize data-intensive operations
- Improve query execution plans
- Support application design reviews
- Enhance user response times

Advanced Analysis

Physical I/O Pattern Analysis

Large Table Operations

-- High physical reads typically indicate:
-- Full table scans on large tables
-- Large joins without proper indexes
-- Bulk data processing operations
-- Data warehouse style queries

Inefficient Access Patterns

-- Poor I/O efficiency suggests:
-- Missing or unused indexes
-- Poor join strategies
-- Suboptimal WHERE clauses
-- Statistics out of date

Cache Utilization Assessment

Buffer Cache Effectiveness

-- Low LOG/PHY ratios indicate:
-- Data not staying in cache
-- Cache too small for workload
-- Large data sets exceeding cache
-- Random access patterns

Memory Optimization Opportunities

-- Improvement strategies:
-- Increase buffer cache size
-- Optimize SQL for cache-friendly access
-- Implement result caching
-- Consider data partitioning

Execution Pattern Analysis

High-Volume Operations

-- High execution count with high physical I/O:
-- Frequently run inefficient queries
-- Application hot spots
-- Batch processing bottlenecks
-- Prime optimization candidates

Low-Volume High-Impact

-- Low execution count with extreme I/O:
-- Complex analytical queries
-- Ad-hoc reporting statements
-- Data migration operations
-- Maintenance procedures

Optimization Strategies

Index Optimization

Missing Index Identification

-- Candidates for new indexes:
-- High physical reads with table scans
-- JOIN conditions without indexes
-- WHERE clause predicates
-- ORDER BY columns

Index Effectiveness Review

-- Validate existing indexes:
-- Unused index identification
-- Redundant index elimination
-- Composite index opportunities
-- Index maintenance overhead

Query Optimization

SQL Rewriting Opportunities

-- Common optimization techniques:
-- Subquery to JOIN conversion
-- EXISTS vs IN optimization
-- UNION vs UNION ALL selection
-- Hint usage for plan control

Execution Plan Analysis

-- Plan optimization focus areas:
-- Table access methods
-- Join algorithms
-- Sort operations
-- Parallel execution opportunities

System-Level Optimization

Storage Optimization

-- Storage-level improvements:
-- Tablespace and datafile placement
-- Storage array optimization
-- I/O load balancing
-- Compression strategies

Memory Allocation

-- Memory optimization:
-- Buffer cache sizing
-- Sort area allocation
-- PGA memory management
-- Result cache utilization

Filtering Examples

High I/O Threshold

-- Find extremely I/O intensive queries:
Enter value for physical: 100000

Moderate I/O Analysis

-- Analyze moderate I/O statements:
Enter value for physical: 10000

Low Threshold Analysis

-- Comprehensive I/O analysis:
Enter value for physical: 1000

Specific Workload Focus

-- Focus on specific I/O ranges:
Enter value for physical: 50000

Performance Impact Assessment

System Resource Impact

I/O Subsystem Load

-- Assess storage impact:
-- Total disk reads volume
-- I/O operations per second
-- Storage throughput utilization
-- Queue depth and latency

Memory Pressure

-- Buffer cache impact:
-- Cache turnover rate
-- Memory allocation efficiency
-- Cache hit rate degradation
-- Aging list management

Application Performance

Response Time Impact

-- User experience effects:
-- Query response time degradation
-- Application throughput reduction
-- Timeout and error rates
-- Service level violations

Concurrency Effects

-- Multi-user impact:
-- Resource contention
-- Wait event increases
-- Blocking and deadlocks
-- Scalability limitations

Integration with Monitoring

Automated Analysis

Performance Monitoring

-- Integration opportunities:
-- Automated threshold monitoring
-- Performance trend analysis
-- Alert generation systems
-- Dashboard integration

Historical Tracking

-- Long-term analysis:
-- I/O trend monitoring
-- Optimization impact validation
-- Capacity planning support
-- Performance regression detection

Tuning Tool Integration

Oracle Tuning Tools

-- Leverage with:
-- SQL Tuning Advisor
-- SQL Access Advisor
-- Automatic SQL Tuning
-- ADDM recommendations

Third-Party Tools

-- External tool integration:
-- Performance monitoring suites
-- Query optimization tools
-- Capacity planning systems
-- Application performance management

Best Practices

Regular Analysis

Monitoring Schedule

-- Establish routine reviews:
-- Daily I/O hotspot identification
-- Weekly optimization planning
-- Monthly trend analysis
-- Quarterly infrastructure review

Threshold Management

-- Adaptive threshold setting:
-- Baseline establishment
-- Workload-specific adjustments
-- Seasonal variation consideration
-- Growth trend accommodation

Optimization Methodology

Systematic Approach

-- Optimization workflow:
-- Impact assessment and prioritization
-- Test environment validation
-- Production implementation
-- Results monitoring and validation

Collaboration

-- Team coordination:
-- Development team engagement
-- Infrastructure team collaboration
-- Business stakeholder communication
-- Change management processes

vsqltop.sql - Comprehensive SQL analysis
vfile.sql - File I/O analysis
vio2.sql - System I/O statistics
random_io.sql - Random I/O analysis

Troubleshooting Applications

High Physical I/O Issues

Immediate Response

-- For critical I/O problems:
-- Identify top I/O consumers
-- Assess system resource impact
-- Consider temporary optimization
-- Plan comprehensive resolution

Root Cause Analysis

-- Systematic investigation:
-- Execution plan analysis
-- Index usage evaluation
-- Statistics currency validation
-- Application design review

Performance Degradation

Trend Analysis

-- Performance trend investigation:
-- Historical I/O comparison
-- Workload growth correlation
-- Infrastructure change impact
-- Application modification effects

Capacity Assessment

-- Resource capacity evaluation:
-- Storage throughput limits
-- Memory allocation adequacy
-- CPU processing capability
-- Network bandwidth sufficiency

Summary

This script is essential for:

I/O Optimization - Identifying and prioritizing high physical I/O SQL statements
Cache Efficiency Analysis - Understanding buffer cache utilization and optimization opportunities
Index Strategy Development - Supporting index creation and optimization decisions
Performance Troubleshooting - Resolving I/O-related performance issues and bottlenecks
Capacity Planning - Understanding I/O workload patterns and planning infrastructure improvements