Top Unbound SQL Statements Analysis (gvsqltopunb.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 and analyzes top-performing SQL statements across all RAC instances, focusing on unbound SQL statements that use literal values instead of bind variables. It uses a character translation technique to normalize numeric literals, helping identify SQL statements that should be converted to use bind variables for better performance and reduced library cache contention. The script orders results by buffer gets to highlight the most resource-intensive statements.

The Script

rem  vsqltop.sql
rem
set lines 132
rem
ttitle 'Shared SQL Area - Top Unbound SQL Statements'
rem
col inst_id format 9999
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 999999999 heading 'DISK|READS'
col phy_exe format 999999 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'
col hash_value format 99999999999 heading 'HASH VALUE'
col rows_processed format 99999999 heading 'ROWS|PROCESSED'
col rows_exe format 9999999 heading 'ROWS/|EXE'
rem
break on report
compute sum of parse_calls executions disk_reads buffer_gets on report
rem
select
inst_id,
/*
       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_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,
       sql_text
  from gv$sqlarea
 where upper(translate(sql_text, '0123456789', 'xxxxxxxxxx')) like upper('&sql_text')
 order by buffer_gets desc, sql_text;

Key Features

• RAC-Aware Analysis: Analyzes SQL across all instances in the cluster
• Unbound SQL Detection: Uses translation technique to identify literal-based SQL
• Performance Metrics: Shows execution statistics, I/O patterns, and resource usage
• Resource Impact Analysis: Focuses on buffer gets as primary performance indicator
• Memory Usage Tracking: Displays SQL statement memory consumption
• Flexible Pattern Matching: Supports SQL text pattern filtering
• Statistical Summaries: Provides computed totals for key metrics

Usage

@gvsqltopunb.sql

Input Parameters:

• sql_text: SQL text pattern to search for (use % for wildcards, press Enter for all)

Required Privileges

SELECT on GV$SQLAREA

Sample Output

                                    Shared SQL Area - Top Unbound SQL Statements

INST_ID      ROWS   ROWS/ ADDRESS  HASH VALUE  S  EXECUTE    PARSE LOG/ DISK     PHY/    BUFFER     LOG/ MODULE
           PROCESSED   EXE                        CALLS   PHY READS     EXE      GETS     EXE
------- ----------- ------- -------- ----------- -- --------- ----- ---- --------- ------- ---------- ------- --
      1   1,234,567     123 12AB34CD  3456789012  5    10,000 5,000   15   567,890      57 45,678,901   4,568 MyApp.exe
SELECT * FROM employees WHERE employee_id = 12345 AND department_id = 20

      2     987,654      98 56EF78GH  7890123456  3     8,500 4,250   12   234,567      28 23,456,789   2,759 Reports.exe
SELECT COUNT(*) FROM orders WHERE order_date = '2024-01-15' AND status = 'PENDING'

      1     654,321      65 9ABC12DE  1234567890  2     6,000 3,000    8   123,456      21 12,345,678   2,058 WebApp
UPDATE products SET price = 99.99 WHERE product_id = 54321

      2     456,789      46 34FG56HI  5678901234  1     4,500 2,250    7    89,012      20  8,901,234   1,978 DataLoader
INSERT INTO audit_log VALUES (98765, 'UPDATE', '2024-01-15 10:30:00')

sum                                                       29,000       1,014,925         90,381,602

Key Output Columns

• INST_ID: RAC instance where SQL is cached
• ROWS PROCESSED: Total number of rows processed by this SQL
• ROWS/EXE: Average rows processed per execution
• ADDRESS: Memory address of the SQL statement
• HASH VALUE: Hash value for SQL identification
• S: Number of sorts performed
• EXECUTE: Total number of executions
• PARSE CALLS: Number of parse calls (hard and soft)
• LOG/PHY: Ratio of logical to physical reads
• DISK READS: Total physical I/O operations
• PHY/EXE: Average physical reads per execution
• BUFFER GETS: Total logical I/O operations
• LOG/EXE: Average logical reads per execution
• MODULE: Application module that executed the SQL
• SQL Text: The SQL statement text

Understanding Unbound SQL Detection

Translation Technique

-- The script uses this translation:
upper(translate(sql_text, '0123456789', 'xxxxxxxxxx'))

-- This converts:
-- "WHERE emp_id = 12345" to "WHERE emp_id = xxxxx"
-- "WHERE dept = 20" to "WHERE dept = xx"
-- Allows pattern matching of similar SQL with different literals

Bind Variable Candidates

-- SQL statements that should use bind variables:
-- WHERE column = literal_value
-- INSERT INTO table VALUES (literal1, literal2, ...)
-- UPDATE table SET column = literal WHERE ...
-- Repeated patterns with only literal differences

Performance Impact

-- Problems with unbound SQL:
-- Library cache pollution
-- Excessive hard parsing
-- Memory fragmentation
-- Plan instability
-- Reduced scalability

Common Use Cases

1. SQL Optimization Analysis

   • Identify most resource-intensive SQL statements
   • Find candidates for bind variable conversion
   • Analyze SQL performance patterns
   • Support application optimization efforts

2. Library Cache Management

   • Identify library cache pollution sources
   • Find SQL causing excessive parsing
   • Analyze shared pool memory usage
   • Plan cursor sharing strategies

3. Application Code Review

   • Find hard-coded literal values in applications
   • Identify SQL generation patterns
   • Support development best practices
   • Plan application refactoring efforts

4. Performance Troubleshooting

   • Identify high-impact SQL statements
   • Analyze resource consumption patterns
   • Find scalability bottlenecks
   • Support performance tuning initiatives

Advanced Analysis

Resource Consumption Analysis

-- High buffer gets indicate:
-- Inefficient execution plans
-- Missing or poor indexes
-- Excessive data processing
-- Need for SQL optimization

Parse Ratio Analysis

-- High parse calls relative to executions:
-- Excessive hard parsing
-- Poor cursor reuse
-- Library cache pressure
-- Application design issues

I/O Efficiency Analysis

-- LOG/PHY ratio interpretation:
-- Low ratio = poor buffer hit ratio
-- High PHY/EXE = I/O intensive operations
-- Patterns indicate optimization needs

Bind Variable Conversion Strategies

Identifying Conversion Candidates

High-Impact Statements

-- Prioritize conversion based on:
-- High buffer gets
-- High execution count
-- High parse calls
-- Multiple similar versions

Pattern Recognition

-- Common patterns to convert:
-- WHERE id = literal
-- INSERT VALUES (literal, literal, ...)
-- UPDATE SET column = literal
-- Date/time literals in WHERE clauses

Conversion Techniques

Application Code Changes

-- Convert from:
SELECT * FROM employees WHERE emp_id = 12345

-- Convert to:
SELECT * FROM employees WHERE emp_id = ?
-- Or in Oracle:
SELECT * FROM employees WHERE emp_id = :emp_id

Dynamic SQL Conversion

-- Replace string concatenation:
-- "SELECT * FROM table WHERE id = " + id_value

-- With parameterized queries:
-- "SELECT * FROM table WHERE id = ?"
-- Plus parameter binding

Performance Impact Assessment

Memory Usage Analysis

-- Assess shared pool impact:
-- Multiple versions of similar SQL
-- Memory fragmentation
-- Cache aging issues
-- Parse time overhead

Scalability Assessment

-- Scalability indicators:
-- Parse CPU consumption
-- Library cache latch contention
-- Cursor cache efficiency
-- Connection scalability limits

Application Performance

-- Application impact:
-- Response time degradation
-- Throughput limitations
-- Resource contention
-- User experience issues

Filtering and Analysis Examples

Find Specific SQL Patterns

-- Search for SELECT statements:
Enter value for sql_text: SELECT%

-- Search for INSERT statements:
Enter value for sql_text: INSERT%

-- Search for UPDATE statements:
Enter value for sql_text: UPDATE%

Application-Specific Analysis

-- Search for specific table access:
Enter value for sql_text: %employees%

-- Search for specific operations:
Enter value for sql_text: %WHERE%id%=%

Pattern-Based Searches

-- Find date-based queries:
Enter value for sql_text: %xxxx-xx-xx%

-- Find numeric literal patterns:
Enter value for sql_text: %=% xxx%

Integration with Development Process

Code Review Integration

-- Use results for:
-- Development team education
-- Code review checklists
-- Architecture guidelines
-- Performance standards

Automated Detection

-- Schedule regular analysis:
-- Weekly unbound SQL reports
-- Performance regression detection
-- Library cache health monitoring
-- Application optimization tracking

Continuous Improvement

-- Track optimization progress:
-- Before/after performance metrics
-- Library cache efficiency improvement
-- Application response time gains
-- Resource utilization optimization

RAC-Specific Considerations

Instance Distribution

-- Analyze SQL distribution:
-- Uneven instance loading
-- Application connection patterns
-- Service routing effectiveness
-- Workload balancing opportunities

Cross-Instance Impact

-- Consider cluster effects:
-- Global cache coordination
-- Cross-instance SQL sharing
-- Library cache synchronization
-- Cluster resource utilization

Performance Variation

-- Monitor instance differences:
-- Performance variation by instance
-- Resource availability differences
-- Configuration inconsistencies
-- Load balancing effectiveness

Remediation Strategies

Short-Term Solutions

-- Immediate actions:
-- Increase shared pool size
-- Enable cursor sharing (use with caution)
-- Implement connection pooling
-- Schedule library cache flushes

Long-Term Solutions

-- Strategic improvements:
-- Application code refactoring
-- Development standards implementation
-- Architecture pattern adoption
-- Framework standardization

Prevention Measures

-- Preventive approaches:
-- Developer training programs
-- Code review processes
-- Automated testing for SQL patterns
-- Performance monitoring integration

Related Scripts

• gvsqltop.sql - Top SQL by various metrics
• vsqltopu.sql - Single-instance top SQL
• gvplanid.sql - SQL plan analysis
• vlibcache.sql - Library cache analysis

Best Practices

Regular Monitoring

-- Establish monitoring routine:
-- Weekly top SQL analysis
-- Monthly bind variable conversion review
-- Quarterly application optimization assessment
-- Annual development standards review

Documentation

-- Maintain optimization documentation:
-- Record conversion candidates
-- Track optimization progress
-- Document performance improvements
-- Share best practices

Team Collaboration

-- Foster collaboration:
-- DBA and development team coordination
-- Regular performance review meetings
-- Shared optimization goals
-- Knowledge transfer sessions

Automation Opportunities

Automated Detection

-- Automate analysis:
-- Schedule regular script execution
-- Generate automated reports
-- Alert on performance degradation
-- Track optimization metrics

Integration with CI/CD

-- Development pipeline integration:
-- Pre-deployment SQL analysis
-- Performance regression testing
-- Automated bind variable detection
-- Code quality gates

Summary

This script is essential for:

1. SQL Optimization - Identifying high-impact SQL statements for optimization
2. Bind Variable Analysis - Finding candidates for bind variable conversion
3. Library Cache Management - Managing shared pool efficiency and memory usage
4. Application Performance - Supporting application-level performance improvements
5. Development Standards - Enforcing SQL coding best practices and patterns