Top SQL Statements by Parse Count (vsqlpar.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 vsqlpar.sql script.

The Script

rem  vsqlpar.sql
rem
ttitle 'Shared SQL Area - Top Parse Count'
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 9999 heading 'LOG/|PHY'
col disk_reads format 9999999 heading 'DISK|READS'
col phy_exe format 9999 heading 'PHY/|EXE'
col buffer_gets format 9999999999 heading 'BUFFER|GETS'
col log_exe format 999999 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 parse_calls >= &parse
 order by parse_calls desc;

What This Script Does

This script identifies SQL statements with high parse counts, which can indicate inefficient cursor usage, lack of bind variables, or poor application design. Excessive parsing consumes CPU resources and can significantly impact database performance. The script helps DBAs identify opportunities to improve application efficiency through better cursor sharing and bind variable usage.

Key Features

• Parse Count Analysis: Identifies statements with excessive parsing overhead
• Parse Ratio Calculation: Shows parse-to-execution ratio percentage
• Performance Metrics: Displays I/O and buffer statistics per execution
• Memory Usage: Shows shared pool memory consumption
• Efficiency Ratios: Calculates logical/physical read ratios
• Threshold Filtering: Parameterized to focus on significant parse counts

Usage

@vsqlpar.sql

Input Parameters:

• parse: Minimum number of parse calls to display (e.g., 1000)

Required Privileges

SELECT on V$SQLAREA

Sample Output

                                      Shared SQL Area - Top Parse Count

ADDRESS   S    EXECUTE    PARSE PARSE LOG/ DISK     PHY/      BUFFER    LOG/ SQL TEXT
               CALLS     PCT  PHY READS    EXE        GETS    EXE
-------- -- --------- -------- ----- ---- ------- ----- ----------- ------ --------------------------------------------------------------------------------
A1B2C3D4  0   1,234,567 1,234,567  100    8   45,678    37  12,345,678  10 SELECT * FROM employees WHERE employee_id = :1
E5F6G7H8  1   2,345,678 1,876,543   80   12   23,456    10   8,765,432   4 SELECT COUNT(*) FROM orders WHERE order_date = :1
I9J0K1L2  0     987,654   876,543   89   15   34,567    35  11,223,344  11 UPDATE customers SET last_updated = SYSDATE WHERE customer_id = :1
M3N4O5P6  2   1,456,789   654,321   45    6   12,345     8   5,678,901   4 SELECT product_name, price FROM products WHERE category_id = :1
Q7R8S9T0  0     654,321   543,210   83   10   21,098    32   7,890,123  12 DELETE FROM temp_data WHERE session_id = :1

                              ======= ========== ========= ===========
sum                           6,678,009 5,185,184  137,144  45,903,478

Key Output Columns

• ADDRESS: Memory address of the SQL cursor in the shared pool
• S: Number of sorts performed by the statement
• EXECUTE: Total number of executions
• PARSE CALLS: Total number of parse operations
• PARSE PCT: Percentage of executions that required parsing (parse_calls/executions * 100)
• LOG/PHY: Ratio of logical reads to physical reads (buffer cache hit efficiency)
• DISK READS: Total physical disk reads
• PHY/EXE: Physical reads per execution
• BUFFER GETS: Total logical reads from buffer cache
• LOG/EXE: Logical reads per execution
• SQL TEXT: The SQL statement text (truncated)

Understanding Parse Performance Issues

Parse Ratio Analysis

Excellent Cursor Sharing (Parse PCT < 5%)

-- Good examples:
-- Parse PCT = 2%: Statement parsed once, executed 50 times
-- Parse PCT = 1%: Statement parsed once, executed 100 times
-- Indicates: Proper cursor reuse, good application design

Poor Cursor Sharing (Parse PCT > 50%)

-- Problem examples:
-- Parse PCT = 100%: Statement parsed every time it's executed
-- Parse PCT = 80%: Minimal cursor reuse
-- Indicates: Missing bind variables, cursor closing issues

Moderate Efficiency (Parse PCT 5-50%)

-- Improvement opportunities:
-- Parse PCT = 20%: Some reuse but room for improvement
-- Parse PCT = 10%: Good but could be better
-- Indicates: Partial cursor sharing, optimization potential

Common Parsing Problems

Missing Bind Variables

-- Problem: Literal values in SQL
SELECT * FROM orders WHERE order_id = 12345;
SELECT * FROM orders WHERE order_id = 12346;
SELECT * FROM orders WHERE order_id = 12347;

-- Solution: Use bind variables
SELECT * FROM orders WHERE order_id = :order_id;

Cursor Closing Issues

-- Problem: Application closes cursors unnecessarily
-- Each execution requires a new parse
-- Solution: Keep cursors open for reuse

Different SQL Text

-- Problem: Slight variations prevent sharing
SELECT * FROM employees WHERE dept_id = 10;
select * from employees where dept_id = 10;  -- Different case
SELECT * FROM employees WHERE  dept_id = 10; -- Extra space

-- Solution: Standardize SQL formatting

Performance Impact Assessment

CPU Impact Calculation

-- High parse counts indicate:
-- Excessive CPU usage for parsing
-- Increased latch contention
-- Library cache pressure
-- Potential scalability issues

Memory Impact

-- Multiple versions due to parsing:
-- Increased shared pool usage
-- More memory per statement
-- Potential aging out of other statements
-- ORA-4031 risks

Scalability Issues

-- Parse scaling problems:
-- Parse time increases with user count
-- Latch contention grows exponentially
-- Application response time degrades
-- Database becomes CPU bound

Analysis and Optimization

Immediate Actions

Identify High-Impact Statements

-- Focus on statements with:
-- 1. High parse counts AND high parse percentages
-- 2. Frequent execution with poor sharing
-- 3. High CPU statements with parsing overhead

Bind Variable Opportunities

-- Look for patterns indicating missing bind variables:
-- Similar SQL text with different literal values
-- High parse counts with low execution counts
-- Statement variations in application code

Application Code Review

Code Pattern Analysis

-- Review application for:
-- Dynamic SQL generation with literals
-- Cursor management practices
-- Connection pooling configuration
-- Statement preparation patterns

Framework Issues

-- Common framework problems:
-- ORM tools generating literal SQL
-- Connection pools not sharing cursors
-- Application servers closing cursors early
-- Transaction boundary issues

Common Use Cases

1. Application Performance Tuning

   • Identify parsing bottlenecks
   • Find bind variable opportunities
   • Optimize cursor usage patterns
   • Reduce CPU overhead

2. Scalability Analysis

   • Assess parsing impact on scalability
   • Plan for increased user loads
   • Identify application design issues
   • Optimize shared pool usage

3. Database Health Assessment

   • Monitor parsing trends over time
   • Identify regression after changes
   • Assess shared pool efficiency
   • Plan capacity requirements

4. Development Review

   • Code review assistance
   • Performance testing validation
   • Best practices verification
   • Framework evaluation

Advanced Analysis

Parse Trending

-- Run periodically to identify:
-- Increasing parse counts over time
-- New statements with poor sharing
-- Changes in parsing patterns
-- Application deployment impacts

Statement Clustering

-- Group similar statements:
-- Identify families of related SQL
-- Find opportunities for standardization
-- Plan systematic improvements
-- Prioritize development efforts

Historical Comparison

-- Compare with baseline data:
-- Parse count increases
-- New inefficient statements
-- Improved cursor sharing
-- Application optimization success

Optimization Strategies

Short-term Improvements

Bind Variable Implementation

-- Convert high-impact statements:
-- Replace literals with bind variables
-- Test cursor sharing improvement
-- Monitor parse count reduction
-- Verify performance gains

Cursor Management

-- Application changes:
-- Keep cursors open longer
-- Implement cursor caching
-- Review connection pooling
-- Optimize statement preparation

Long-term Solutions

Application Architecture

-- Design improvements:
-- Standard SQL patterns
-- Centralized SQL management
-- Bind variable standards
-- Cursor sharing guidelines

Development Practices

-- Process improvements:
-- Code review standards
-- Performance testing requirements
-- Bind variable guidelines
-- Shared pool monitoring

Monitoring and Alerting

Key Metrics

• Parse count growth rates
• Parse-to-execution ratios
• New high-parse statements
• Shared pool pressure

Threshold Setting

-- Alert thresholds:
-- Parse calls > 10,000 per hour
-- Parse percentage > 20%
-- New statements with poor sharing
-- Shared pool aging issues

Integration with Performance Tools

AWR/Statspack Integration

-- Correlate with:
-- Top SQL by parse calls
-- Library cache statistics
-- Shared pool advisor
-- CPU usage patterns

Real-time Monitoring

-- Combine with:
-- V$SQL_MONITOR for active statements
-- V$SESSION for current parsing activity
-- V$LIBRARY_CACHE for shared pool metrics

Related Scripts

• vsqltop.sql - Top SQL statements by various metrics
• vlibcache.sql - Library cache analysis
• vshpresrv.sql - Shared pool reserved area
• vsqlexe.sql - Top SQL by execution count

Best Practices

1. Regular Monitoring

   • Include in weekly performance reviews
   • Set parse count thresholds
   • Track improvements over time
   • Document optimization successes

2. Application Development

   • Always use bind variables
   • Implement proper cursor management
   • Test cursor sharing in development
   • Monitor parsing in production

3. Proactive Management

   • Identify parsing issues early
   • Plan application optimization
   • Monitor shared pool health
   • Prevent scalability problems