SQL Row Processing Analysis (vsqlrow.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 analyzes SQL statements focusing on row processing efficiency, showing the relationship between rows processed, executions, and resource consumption. It provides critical metrics for understanding data processing patterns, identifying inefficient queries, and optimizing data access strategies. The script helps identify statements that process large volumes of data and their corresponding resource utilization.

The Script

rem  vsqlrow.sql
rem
ttitle 'Shared SQL Area - Top SQL Statements'
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 999999 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 99999999 heading 'DISK|READS'
col phy_exe format 99999 heading 'PHY/|EXE'
col buffer_gets format 999999999 heading 'BUFFER|GETS'
col log_exe format 9999999 heading 'LOG/|EXE'
col sorts format 9 heading 'S'
col address format a8 heading 'ADDRESS'
col rows_processed format 99999999 heading 'ROWS|PROCESSED'
col rows_exe format 99999 heading 'ROWS/|EXE'
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,
       first_load_time,
       command_type,
       optimizer_mode,
       parsing_user_id,
       parsing_schema_id,
       hash_value,
       module,
       module_hash,
       action,
       action_hash,
       serializable_aborts,
       address,
*/
       rows_processed,
       rows_processed/decode(executions,0,null,executions) rows_exe,
       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 parse_calls >= &parse
    or executions >= &execute
    or disk_reads >= &physical
    or buffer_gets >= &logical
 order by buffer_gets desc;

Key Features

• Row Processing Focus: Emphasizes rows processed and per-execution ratios
• Data Efficiency Analysis: Shows relationship between rows and resource consumption
• Flexible Filtering: Multiple threshold options for comprehensive analysis
• I/O Correlation: Correlates row processing with I/O resource usage
• Execution Efficiency: Calculates per-execution processing ratios
• Sort Operation Tracking: Identifies sort-intensive operations
• Resource Impact Assessment: Shows buffer gets and disk reads per execution

Usage

@vsqlrow.sql

Input Parameters:

• parse: Minimum parse calls threshold
• execute: Minimum execution count threshold
• physical: Minimum disk reads threshold
• logical: Minimum buffer gets threshold

Required Privileges

SELECT on V$SQLAREA

Sample Output

                                    Shared SQL Area - Top SQL Statements

ROWS      ROWS/ S  EXECUTE PARSE LOG/ DISK READS PHY/ BUFFER GETS  LOG/
PROCESSED   EXE        CALLS  PHY            EXE             EXE
--------- ----- - -------- ----- ---- --------- ---- --------- -----
 98765432  1234 2  8000000 24000   45  2345678   29  105678901  1321
SQL Text: SELECT customer_id, order_total FROM orders WHERE order_date BETWEEN :1 AND :2

 45678901   456 1  1000000 15000   78  1234567   12   78901234   789
SQL Text: INSERT INTO daily_summary SELECT product_id, SUM(quantity) FROM sales_detail WHERE sale_date = :1 GROUP BY product_id

 23456789  2345 0   100000  2000   23   987654   98   45678901   456
SQL Text: UPDATE inventory SET quantity = quantity - :1 WHERE product_id = :2 AND warehouse_id = :3

 12345678   123 3  1000000  5000   89   456789    4   23456789   234
SQL Text: DELETE FROM temp_processing WHERE batch_id = :1 AND status = 'COMPLETE'

  9876543   987 0   100000  1000   67   234567   23   12345678   123
SQL Text: SELECT * FROM customer_profile cp, preference_data pd WHERE cp.customer_id = pd.customer_id

                    -------- ----- ---- --------- ---- --------- -----
sum                 10200000 32000        5258355         266161631

Key Output Columns

• ROWS PROCESSED: Total number of rows processed by the statement
• ROWS/EXE: Average number of rows processed per execution
• S: Number of sort operations performed
• EXECUTE: Total number of executions
• PARSE CALLS: Number of parse operations
• LOG/PHY: Ratio of logical reads to physical reads
• DISK READS: Total physical disk reads
• PHY/EXE: Average physical reads per execution
• BUFFER GETS: Total logical reads
• LOG/EXE: Average logical reads per execution
• SQL Text: The actual SQL statement

Understanding Row Processing Metrics

Row Processing Efficiency

Rows Per Execution Analysis

-- Row processing patterns:
-- High ROWS/EXE: Bulk data operations
-- Low ROWS/EXE: OLTP single-row operations
-- Very high ROWS/EXE: Potential full table scans
-- Zero ROWS/EXE: DDL or non-row-returning operations

Data Volume Assessment

-- Total rows processed indicates:
-- Overall data processing volume
-- Application workload characteristics
-- Data-intensive operation identification
-- Bulk processing patterns

Resource Efficiency Correlation

I/O per Row Efficiency

-- Calculate I/O efficiency:
-- Buffer gets per row processed
-- Physical reads per row processed
-- Efficiency of data access methods
-- Index utilization effectiveness

Execution Efficiency

-- Execution pattern analysis:
-- High executions with moderate rows: OLTP patterns
-- Low executions with high rows: Batch processing
-- High executions with high rows per execution: Inefficient queries

Common Use Cases

1. Data Processing Optimization

   • Identify inefficient data processing patterns
   • Optimize bulk data operations
   • Improve ETL process performance
   • Support data warehouse tuning

2. Query Efficiency Analysis

   • Analyze row-to-resource consumption ratios
   • Identify full table scan candidates
   • Support index optimization decisions
   • Improve data access strategies

3. Application Workload Analysis

   • Understand application data access patterns
   • Identify OLTP vs. analytical workloads
   • Support capacity planning efforts
   • Optimize application design

4. Performance Troubleshooting

   • Find queries processing excessive rows
   • Identify resource-intensive operations
   • Support response time optimization
   • Resolve scalability issues

Advanced Analysis

Row Processing Pattern Analysis

OLTP vs. Analytical Patterns

-- OLTP characteristics:
-- Low ROWS/EXE (typically 1-10)
-- High execution frequency
-- Low individual resource consumption
-- Consistent processing patterns

Batch Processing Patterns

-- Batch operation characteristics:
-- High ROWS/EXE (typically hundreds to millions)
-- Low execution frequency
-- High resource consumption per execution
-- Time-based processing patterns

Efficiency Ratio Analysis

Resource-to-Row Efficiency

-- Calculate efficiency metrics:
-- Buffer gets per row = BUFFER_GETS / ROWS_PROCESSED
-- Physical reads per row = DISK_READS / ROWS_PROCESSED
-- Optimal ratios for different operation types
-- Benchmark against similar operations

Processing Scalability

-- Scalability assessment:
-- Linear resource growth with row count
-- Exponential growth indicating inefficiency
-- Resource consumption predictability
-- Optimization opportunity identification

Sort Operation Impact

Sort-Intensive Operations

-- Sort operation analysis:
-- High sort count indicates ORDER BY, GROUP BY
-- Memory vs. disk-based sorting
-- Temporary tablespace usage
-- Sort optimization opportunities

Sort Efficiency

-- Sort performance factors:
-- Sort area size adequacy
-- Index availability for sorting
-- Data volume vs. memory allocation
-- Parallel sorting opportunities

Optimization Strategies

Row Processing Optimization

Bulk Operation Optimization

-- Bulk processing improvements:
-- Batch size optimization
-- Parallel processing implementation
-- Direct path operations
-- Bulk collect and forall usage

Single-Row Operation Efficiency

-- OLTP optimization:
-- Index optimization for single-row access
-- Bind variable usage improvement
-- Connection pooling optimization
-- Transaction scope minimization

Resource Efficiency Improvement

I/O Optimization

-- Reduce I/O per row through:
-- Better indexing strategies
-- Partitioning implementation
-- Data compression
-- Result set reduction

Memory Utilization

-- Memory optimization:
-- Sort area sizing
-- Buffer cache optimization
-- PGA memory allocation
-- Result caching strategies

Query Rewriting Opportunities

Data Access Optimization

-- Query improvement techniques:
-- Predicate pushdown
-- Join order optimization
-- Subquery optimization
-- Analytical function usage

Execution Plan Optimization

-- Plan improvement strategies:
-- Hint usage for plan control
-- Statistics refresh
-- Index hint application
-- Parallel execution consideration

Filtering Examples

High-Volume Data Processing

-- Find bulk processing operations:
Enter value for parse: 0
Enter value for execute: 1000
Enter value for physical: 10000
Enter value for logical: 100000

Frequent OLTP Operations

-- Find high-frequency operations:
Enter value for parse: 1000
Enter value for execute: 100000
Enter value for physical: 0
Enter value for logical: 0

I/O Intensive Analysis

-- Focus on I/O intensive statements:
Enter value for parse: 0
Enter value for execute: 0
Enter value for physical: 50000
Enter value for logical: 500000

Comprehensive Analysis

-- Broad analysis of significant statements:
Enter value for parse: 100
Enter value for execute: 1000
Enter value for physical: 1000
Enter value for logical: 10000

Performance Impact Assessment

System Resource Impact

Memory Pressure Assessment

-- Memory impact evaluation:
-- Buffer cache turnover rate
-- Sort operation memory usage
-- PGA memory consumption
-- Shared pool pressure

I/O Subsystem Load

-- I/O impact analysis:
-- Disk throughput utilization
-- I/O operations per second
-- Storage latency impact
-- Temporary tablespace usage

Application Performance

Response Time Correlation

-- Response time factors:
-- Row processing volume vs. response time
-- User experience impact
-- Batch processing window requirements
-- Service level agreement compliance

Scalability Assessment

-- Scalability considerations:
-- Resource growth with data volume
-- Concurrent user impact
-- Peak load handling capability
-- Infrastructure capacity limits

Integration with Performance Monitoring

Trend Analysis

Historical Processing Patterns

-- Track processing trends:
-- Row processing volume growth
-- Resource efficiency changes
-- Processing pattern evolution
-- Capacity requirement trends

Performance Regression Detection

-- Regression identification:
-- Efficiency ratio degradation
-- Resource consumption increases
-- Processing time growth
-- Query plan changes

Automated Monitoring

Alert Systems

-- Monitoring alert criteria:
-- Excessive rows per execution
-- Poor resource efficiency ratios
-- Processing volume spikes
-- Resource consumption anomalies

Dashboard Integration

-- Dashboard components:
-- Top row processing statements
-- Resource efficiency trends
-- Processing volume summaries
-- Performance health indicators

Best Practices

Analysis Methodology

Regular Assessment

-- Monitoring schedule:
-- Daily high-volume operation review
-- Weekly efficiency analysis
-- Monthly trend assessment
-- Quarterly optimization planning

Baseline Establishment

-- Performance baselines:
-- Typical row processing patterns
-- Expected resource consumption ratios
-- Normal execution frequencies
-- Efficiency benchmarks

Optimization Approach

Prioritization Strategy

-- Optimization priorities:
-- High-volume operations first
-- Poor efficiency ratios
-- Resource-intensive statements
-- Frequently executed queries

Validation Process

-- Optimization validation:
-- Test environment verification
-- Performance improvement measurement
-- Resource utilization monitoring
-- User impact assessment

Related Scripts

• vsqltop.sql - Comprehensive SQL analysis
• vsqlphy.sql - Physical I/O analysis
• vsqlexe.sql - Execution frequency analysis
• vsortusage.sql - Sort operation analysis

Troubleshooting Applications

High Row Processing Issues

Excessive Data Processing

-- For statements processing too many rows:
-- Evaluate query predicates
-- Check index usage effectiveness
-- Consider data partitioning
-- Optimize WHERE clause conditions

Inefficient Processing Patterns

-- For poor efficiency ratios:
-- Analyze execution plans
-- Review indexing strategies
-- Consider query rewriting
-- Evaluate join strategies

Resource Consumption Problems

Memory Pressure

-- For memory-related issues:
-- Optimize sort operations
-- Adjust memory parameters
-- Consider result caching
-- Implement bulk processing techniques

I/O Bottlenecks

-- For I/O-related problems:
-- Improve index utilization
-- Optimize data access patterns
-- Consider compression strategies
-- Implement partitioning

Summary

This script is essential for:

1. Data Processing Analysis - Understanding row processing patterns and efficiency
2. Resource Optimization - Optimizing resource consumption relative to data processing
3. Query Efficiency Assessment - Evaluating query effectiveness in processing data
4. Application Workload Understanding - Analyzing application data access characteristics
5. Performance Troubleshooting - Identifying and resolving data processing inefficiencies