Top SQL Statements for Open Cursors by Session (vopencurtop.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 vopencurtop.sql script.

The Script

rem  vopencurtop.sql
rem
set lines 132
rem
ttitle 'Top Open Cursor SQL Statements for a SID'
rem
col sql_text format a132 heading 'SQL Text' wrap
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 a16 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
/*
       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,
*/
       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,
       elapsed_time,
       elapsed_time / decode(executions,0,1,executions) elapsed_per_exec,
       sql_text
  from v$sqlarea
 where (address, hash_value) in
         (select address, hash_value
            from v$open_cursor
           where sid like nvl( '&sid', '%')
             and upper(sql_text) like upper(nvl('&text','%')))
 order by
       -- buffer_gets/decode(executions,0,1,executions) desc,
       buffer_gets desc
/

What This Script Does

This script identifies SQL statements that currently have open cursors for specific sessions, providing detailed performance metrics including I/O statistics, execution counts, and resource usage. It’s essential for detecting cursor leaks, analyzing session SQL patterns, and understanding memory usage by open cursors.

Key Features

• Open Cursor Focus: Shows only SQL with currently open cursors
• Session-Specific: Analyzes cursors for specific sessions
• Performance Metrics: Comprehensive SQL performance statistics
• Resource Usage: Memory and I/O consumption details
• Cursor Leak Detection: Helps identify applications not closing cursors
• SQL Text Filtering: Filter by SQL text patterns

Usage

Run the script and provide filter criteria when prompted:

@vopencurtop.sql

Input Parameters:

• SID: Session ID (specific session or % for all sessions)
• Text: SQL text pattern to search for (specific pattern or % for all)

Required Privileges

SELECT on V$SQLAREA
SELECT on V$OPEN_CURSOR

Sample Output

                                Top Open Cursor SQL Statements for a SID

ROWS     ROWS/              HASH VALUE    S  EXECUTE    LOG/ DISK      PHY/    BUFFER      LOG/    ELAPSED ELAPSED SQL TEXT
PROCESSED   EXE ADDRESS                      CALLS      PHY  READS     EXE      GETS       EXE      TIME PER_EXEC
--------- ------- ---------------- ------------- ---- --------- ---- --------- ------- ---------- ------- -------- --------
    5,234    52.3 0000000085A2B3C8  12345678901   15     1,000   45    12,456   124.6   45,789,123   45,789  2,456,789   2,457
SELECT emp_id, first_name, last_name, salary FROM employees WHERE department_id = :1 AND status = 'A'

    1,245   124.5 0000000085A2B4D9  23456789012    8       100   89     5,678    56.8   12,345,678  123,457    845,123   8,451
SELECT COUNT(*) FROM orders WHERE order_date >= :1 AND order_date < :2 AND customer_id = :3

      987    98.7 0000000085A2C5EA  34567890123    3        10  156     1,234   123.4    3,456,789  345,679    234,567  23,457
INSERT INTO audit_log (user_id, action, timestamp, details) VALUES (:1, :2, :3, :4)

      456     4.6 0000000085A2D6FB  45678901234   12       100   12       987     9.9    1,234,567   12,346    156,789   1,568
UPDATE customer_profiles SET last_login = SYSDATE WHERE customer_id = :1

                                                           ---------      ---------         ----------
sum                                                           1,210         20,355         62,826,157

Key Output Columns

• ROWS PROCESSED: Total rows returned by the SQL statement
• ROWS/EXE: Average rows per execution
• ADDRESS: Memory address of the SQL statement
• HASH VALUE: SQL statement hash value for identification
• S: Number of sorts performed
• EXECUTE: Total number of executions
• LOG/PHY: Logical to physical I/O ratio
• DISK READS: Total physical disk reads
• PHY/EXE: Physical reads per execution
• BUFFER GETS: Total logical I/O operations
• LOG/EXE: Logical I/O per execution
• ELAPSED TIME: Total elapsed time (microseconds)
• ELAPSED PER_EXEC: Average elapsed time per execution
• SQL TEXT: The actual SQL statement

Understanding Open Cursor Analysis

Cursor Leak Detection

High Execution vs Low Cursor Count

• Many executions but few open cursors: Good cursor management
• Few executions but many open cursors: Potential cursor leak
• Multiple identical statements: Application not reusing prepared statements

Memory Usage Patterns

• High memory per cursor: Complex SQL statements
• Many small cursors: Potential for optimization through statement consolidation
• Growing memory usage: Monitor for memory leaks

Performance Analysis

I/O Efficiency

-- Analyze I/O ratios:
-- LOG/PHY ratio > 100: Good buffer cache hit ratio
-- LOG/PHY ratio < 10: Poor buffer cache efficiency
-- PHY/EXE > 100: High physical I/O per execution

Execution Patterns

-- Review execution metrics:
-- High EXECUTE count: Frequently used statements
-- High ELAPSED_PER_EXEC: Slow-performing statements
-- High ROWS/EXE: Statements returning large result sets

Common Use Cases

1. Cursor Leak Investigation

   • Identify sessions with excessive open cursors
   • Find applications not properly closing cursors
   • Monitor cursor usage patterns

2. Memory Usage Analysis

   • Understand cursor memory consumption
   • Identify statements consuming excessive shared pool memory
   • Plan memory allocation optimization

3. Session Performance Analysis

   • Analyze SQL patterns for specific sessions
   • Identify resource-intensive statements
   • Understand application behavior

4. Application Optimization

   • Find candidates for cursor caching
   • Identify statements needing performance tuning
   • Optimize cursor usage patterns

Advanced Analysis

Cursor Leak Detection Process

1. Identify High Cursor Sessions

   SELECT sid, username, program, COUNT(*) cursor_count
   FROM v$open_cursor
   GROUP BY sid, username, program
   HAVING COUNT(*) > 100
   ORDER BY cursor_count DESC;

2. Analyze Cursor Patterns

   -- Run vopencurtop.sql for high cursor sessions
   -- Look for repeated similar statements
   -- Check for statements with low reuse

3. Monitor Over Time

   -- Track cursor counts periodically
   -- Identify growing cursor usage
   -- Correlate with application activities

Performance Optimization

Statement Consolidation

• Multiple similar statements with different literals
• Candidates for bind variable implementation
• Opportunities for statement caching

Resource Optimization

• High memory usage statements
• Statements with poor I/O efficiency
• Long-running operations

Filter Examples

Analyze Specific Session

Enter value for sid: 156
Enter value for text: %

Find Specific SQL Patterns

Enter value for sid: %
Enter value for text: SELECT%FROM employees%

Check for INSERT/UPDATE/DELETE

Enter value for sid: %
Enter value for text: INSERT%

Troubleshooting Common Issues

Excessive Open Cursors

• Symptoms: ORA-01000 maximum open cursors exceeded
• Analysis: Use this script to identify cursor-heavy sessions
• Solutions: Improve application cursor management, increase OPEN_CURSORS parameter

Memory Issues

• Symptoms: Shared pool fragmentation or exhaustion
• Analysis: Look for statements with high memory usage
• Solutions: Optimize SQL statements, increase shared pool size

Performance Problems

• Symptoms: Slow application response
• Analysis: Focus on statements with high elapsed time per execution
• Solutions: SQL tuning, indexing, query optimization

Script Customization

Add Memory Details

-- Uncomment memory columns for detailed analysis:
sharable_mem,
persistent_mem,
runtime_mem,

Different Sort Orders

-- Sort by different metrics:
order by executions desc           -- Most executed
order by elapsed_time desc         -- Longest total time
order by elapsed_per_exec desc     -- Slowest per execution
order by disk_reads desc           -- Most I/O intensive

Additional Filters

-- Add WHERE clauses for specific analysis:
and executions > 100               -- Frequently executed
and elapsed_per_exec > 1000000     -- Slow statements (>1 second avg)
and buffer_gets > 1000             -- High logical I/O

Related Scripts

• vopencur.sql - Open cursor analysis
• vsqlexe.sql - Top SQL by execution count
• gvsess.sql - Active session analysis
• sqltext.sql - SQL text retrieval and analysis