Top SQL Statements Analysis (vsqltop.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 identifies top-performing SQL statements in the shared SQL area based on configurable thresholds for parse calls, executions, disk reads, and buffer gets. It provides comprehensive performance metrics and ratios to help identify resource-intensive SQL statements that need optimization. The script is essential for SQL performance tuning, identifying problem statements, and understanding application workload characteristics.

The Script

rem  vsqltop.sql
rem
ttitle 'Shared SQL Area - Top SQL Statements'
rem
set lines 180
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 99999999 heading 'PARSE|CALLS'
col parse_pct format 999 heading 'PARSE|PCT'
col executions format 999999999 heading 'EXECUTE'
col log_phy format 999999 heading 'LOG/|PHY'
col disk_reads format 999999999 heading 'DISK|READS'
col phy_exe format 999999 heading 'PHY/|EXE'
col buffer_gets format 9999999999 heading 'BUFFER|GETS'
col log_exe format 99999999 heading 'LOG/|EXE'
col sorts format 99 heading 'S'
col address format a8 heading 'ADDRESS'
col hash_value format 99999999999 heading 'HASH VALUE'
col rows_processed format 99999999 heading 'ROWS|PROCESSED'
col rows_exe format 9999999 heading 'ROWS/|EXE'
col module format a40
col action format a39
rem
break on report
rem compute sum of parse_calls executions disk_reads buffer_gets on report
compute sum of 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,
       module,
       module_hash,
       action,
       action_hash,
       serializable_aborts,
       rows_processed,
       rows_processed/decode(executions, 0, 1, executions) rows_exe,
       address,
*/
       hash_value,
       sorts,
       executions,
       -- parse_calls,
       parse_calls*100/decode(executions,0,1,executions) parse_pct,
       buffer_gets/decode(disk_reads,0,1,disk_reads) log_phy,
       disk_reads,
       disk_reads/decode(executions,0,1,executions) phy_exe,
       buffer_gets,
       buffer_gets/decode(executions,0,1,executions) log_exe,
       module,
       action,
       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

• Configurable Thresholds: Filter SQL based on multiple performance criteria
• Comprehensive Metrics: Shows execution counts, I/O statistics, and efficiency ratios
• Performance Ratios: Calculates parse percentage, logical/physical ratios, and per-execution metrics
• Application Context: Shows module and action information for application correlation
• Memory Usage: Optional display of memory consumption metrics
• Sorting Flexibility: Ordered by buffer gets for resource impact prioritization

Usage

@vsqltop.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

 HASH VALUE S  EXECUTE PARSE LOG/ DISK READS PHY/ BUFFER GETS  LOG/ MODULE                                 ACTION
                         PCT  PHY            EXE             EXE
----------- -- -------- ----- ---- --------- ---- ---------- ---- --------------------------------------- -------
 1234567890  0  2345678    45  234    567890  0.24 9876543210  421 HR_APPLICATION                        UPDATE_EMP
 SQL Text: UPDATE employees SET salary = :1, last_update = SYSDATE WHERE employee_id = :2

 2345678901  1  1234567    89  123    345678  0.28 8765432109  710 SALES_MODULE                          QUERY_ORDERS
 SQL Text: SELECT o.order_id, o.order_date, c.customer_name FROM orders o, customers c WHERE o.customer_id = c.customer_id

 3456789012  0   987654    12  456    234567  0.24 7654321098  775 BATCH_PROCESS                         NIGHTLY_JOB
 SQL Text: INSERT INTO archive_table SELECT * FROM transaction_table WHERE transaction_date < :1

 4567890123  2   765432    23  789    123456  0.16 6543210987  854 REPORTING_APP                         MONTHLY_RPT
 SQL Text: SELECT SUM(amount), COUNT(*) FROM financial_data WHERE account_type = :1 AND date_range BETWEEN :2 AND :3

                                                                    ---------- ---------- ----------
sum                                                                   5332331  1271591 32839506376

Key Output Columns

• HASH VALUE: Unique identifier for the SQL statement
• S: Number of sorts performed
• EXECUTE: Total number of executions
• PARSE PCT: Percentage of executions that required parsing
• LOG/PHY: Ratio of logical reads to physical reads (buffer cache hit ratio)
• 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
• MODULE: Application module name
• ACTION: Specific action or operation name
• SQL Text: The actual SQL statement text

Understanding Performance Metrics

Execution Metrics

Execution Frequency

-- High execution counts indicate:
-- Frequently used SQL statements
-- Application hotspots
-- Potential optimization candidates
-- Cursor sharing opportunities

Parse Efficiency

-- Parse percentage analysis:
-- High parse percentage (>10%): Poor cursor sharing
-- Low parse percentage (<5%): Good cursor reuse
-- 100% parse rate: No cursor sharing at all
-- Optimization: Improve bind variable usage

I/O Performance Metrics

Logical vs Physical Reads

-- LOG/PHY ratio interpretation:
-- High ratio (>10): Good buffer cache hit rate
-- Low ratio (<5): Poor cache efficiency or large data sets
-- Ratio of 1: All reads from disk (poor performance)
-- Optimization target: Improve buffer cache efficiency

Per-Execution Efficiency

-- PHY/EXE and LOG/EXE ratios:
-- High values indicate resource-intensive statements
-- Optimization opportunities for I/O reduction
-- Index optimization candidates
-- Query rewriting opportunities

Common Use Cases

1. SQL Performance Tuning

   • Identify resource-intensive SQL statements
   • Prioritize optimization efforts
   • Analyze I/O efficiency patterns
   • Support systematic tuning initiatives

2. Application Performance Analysis

   • Correlate SQL performance with applications
   • Identify application-specific bottlenecks
   • Analyze workload distribution patterns
   • Support application optimization

3. Capacity Planning

   • Understand resource consumption patterns
   • Forecast system requirements
   • Plan infrastructure scaling
   • Support growth planning initiatives

4. Problem Diagnosis

   • Identify SQL causing performance issues
   • Analyze parse and execution patterns
   • Support incident resolution
   • Validate tuning effectiveness

Advanced Analysis

Parse Optimization Analysis

Cursor Sharing Assessment

-- Parse percentage thresholds:
-- 0-5%: Excellent cursor sharing
-- 5-10%: Good cursor sharing
-- 10-25%: Fair cursor sharing
-- >25%: Poor cursor sharing (needs attention)

Hard Parse Impact

-- High parse rates indicate:
-- Inadequate bind variable usage
-- Cursor_sharing parameter issues
-- Application design problems
-- Shared pool sizing issues

I/O Efficiency Analysis

Buffer Cache Effectiveness

-- LOG/PHY ratio analysis:
-- >20: Excellent cache hit rate
-- 10-20: Good cache performance
-- 5-10: Fair performance
-- <5: Poor cache utilization

Physical I/O Optimization

-- High physical reads indicate:
-- Large table scans
-- Missing or ineffective indexes
-- Poor join strategies
-- Buffer cache sizing issues

Resource Consumption Patterns

Memory Usage Analysis

-- Shared memory consumption:
-- High sharable_mem: Large SQL statements
-- Multiple versions: Plan instability
-- High runtime_mem: Complex execution plans
-- Optimization: SQL simplification

Execution Efficiency

-- Execution pattern analysis:
-- High execution with low resource usage: Efficient SQL
-- Low execution with high resources: Optimization candidates
-- Unbalanced patterns: Design review needed

Filtering Examples

High-Volume Queries

-- Find frequently executed statements:
Enter value for parse: 100
Enter value for execute: 10000
Enter value for physical: 0
Enter value for logical: 0

I/O Intensive Queries

-- Find high I/O statements:
Enter value for parse: 0
Enter value for execute: 0
Enter value for physical: 50000
Enter value for logical: 1000000

Parse-Heavy Queries

-- Find statements with parsing issues:
Enter value for parse: 1000
Enter value for execute: 100
Enter value for physical: 0
Enter value for logical: 0

Comprehensive Analysis

-- Analyze all significant statements:
Enter value for parse: 50
Enter value for execute: 100
Enter value for physical: 1000
Enter value for logical: 10000

Performance Optimization Applications

SQL Tuning Priorities

High-Impact Statements

-- Prioritize optimization for:
-- High buffer gets per execution
-- High physical reads per execution
-- High execution frequency
-- Poor parse efficiency

Optimization Strategies

-- Common optimization approaches:
-- Index creation or modification
-- Query rewriting
-- Hint usage optimization
-- Statistics refresh

Application-Specific Analysis

Module-Based Optimization

-- Analyze by application module:
-- Identify module-specific patterns
-- Correlate with application functionality
-- Plan targeted optimization
-- Support development teams

Action-Level Analysis

-- Detailed action analysis:
-- Specific operation optimization
-- Workflow efficiency assessment
-- User experience correlation
-- Performance SLA validation

Integration with Tuning Tools

SQL Tuning Workflow

Statement Identification

-- Use output to identify candidates for:
-- SQL Tuning Advisor
-- SQL Access Advisor
-- Automatic SQL Tuning
-- Manual optimization efforts

Plan Analysis

-- Further analysis with:
-- EXPLAIN PLAN
-- AUTOTRACE
-- SQL Monitor reports
-- AWR/ADDM analysis

Monitoring Integration

Automated Analysis

-- Integration opportunities:
-- Scheduled performance reviews
-- Threshold-based alerting
-- Trend analysis automation
-- Performance dashboard feeds

Historical Tracking

-- Track performance over time:
-- SQL performance evolution
-- Optimization impact validation
-- Regression detection
-- Capacity trend analysis

Best Practices

Regular Monitoring

Monitoring Schedule

-- Establish routine analysis:
-- Daily top SQL review
-- Weekly trend analysis
-- Monthly comprehensive assessment
-- Quarterly optimization planning

Threshold Management

-- Adjust thresholds based on:
-- System capacity and workload
-- Performance requirements
-- Historical patterns
-- Business priorities

Optimization Strategy

Systematic Approach

-- Optimization methodology:
-- Focus on high-impact statements first
-- Validate changes in test environment
-- Monitor performance improvement
-- Document optimization results

Collaboration

-- Work with development teams:
-- Share SQL performance insights
-- Provide optimization recommendations
-- Support application design reviews
-- Implement coding standards

Related Scripts

• vsqltopu.sql - SQL by username analysis
• vsqlexe.sql - SQL execution analysis
• vsqltime.sql - SQL timing analysis
• gvsqltop.sql - RAC-aware SQL analysis

Troubleshooting Applications

Performance Issues

High Resource Consumption

-- For high buffer gets or disk reads:
-- Analyze execution plans
-- Review indexing strategies
-- Consider query rewriting
-- Validate statistics currency

Parse Problems

-- For high parse percentages:
-- Review bind variable usage
-- Check cursor_sharing settings
-- Analyze application code
-- Consider connection pooling

System Impact

Resource Pressure

-- Monitor for:
-- Shared pool pressure
-- Buffer cache contention
-- I/O subsystem stress
-- CPU utilization spikes

Application Impact

-- Assess application effects:
-- Response time degradation
-- Throughput reduction
-- User experience issues
-- SLA violations

Summary

This script is essential for:

1. SQL Performance Analysis - Identifying top resource-consuming SQL statements
2. Optimization Planning - Prioritizing SQL tuning efforts based on impact
3. Application Monitoring - Understanding application-specific SQL performance patterns
4. Capacity Planning - Analyzing resource consumption trends and planning capacity
5. Problem Resolution - Supporting rapid identification and resolution of SQL performance issues