Open Cursors by SQL Text (qopencur.sql)

What This Script Does

This Oracle script searches for open cursors based on SQL text patterns by querying the v$open_cursor view. It helps identify cursor leaks, analyze application cursor usage patterns, and troubleshoot memory-related issues caused by excessive open cursors.

The Script

rem  qopencur.sql
rem
ttitle 'Open Cursors by SQL Text'
rem
col sid format 9999 heading 'SID'
col hash_value format 999999999999 heading 'HASH VALUE'
col sql_text format a60 heading 'SQL TEXT'
rem
select sql_text,
       hash_value,
       sid
  from v$open_cursor c
 where upper(sql_text) like upper('&sql_text')
 order by sql_text, hash_value, sid
/

Usage

-- Run the script and enter SQL text pattern when prompted
@qopencur.sql
-- Enter value for sql_text: %SELECT%

-- Search for specific table references
@qopencur.sql
-- Enter value for sql_text: %EMPLOYEES%

-- Find all open cursors (use % wildcard)
@qopencur.sql
-- Enter value for sql_text: %

Required Privileges

SELECT privilege on v$open_cursor

Sample Output

                                                       Open Cursors by SQL Text

SQL TEXT                                                     HASH VALUE   SID
------------------------------------------------------------ ------------ ----
SELECT * FROM employees WHERE employee_id = :1               123456789   145
SELECT * FROM employees WHERE employee_id = :1               123456789   167
SELECT * FROM employees WHERE employee_id = :1               123456789   189
SELECT emp_id, name FROM employees WHERE dept_id = :1        987654321   145
SELECT emp_id, name FROM employees WHERE dept_id = :1        987654321   203
UPDATE employees SET salary = :1 WHERE employee_id = :2      555666777   145

Key Output Columns

SQL TEXT: The SQL statement text (first 60 characters)
HASH VALUE: Unique hash identifier for the SQL statement
SID: Session ID that has the cursor open

Understanding the Results

Cursor Patterns

Multiple Sessions, Same SQL
- Same hash_value across different SIDs
- Normal for shared SQL in connection pools
- May indicate popular queries
Multiple Cursors, Same Session
- Same SID with multiple different hash_values
- Could indicate cursor leak if excessive
- Normal for applications with varied SQL
Duplicate Hash Values
- Same SQL statement kept open multiple times
- Potential cursor leak or poor cursor management
- Application may not be closing cursors properly

Common Use Cases

1. Identify Cursor Leaks

-- Look for applications with excessive open cursors
@qopencur.sql
-- Enter value for sql_text: %
-- Count cursors per session to find potential leaks

2. Find Specific Application SQL

-- Search for cursors from specific application
@qopencur.sql
-- Enter value for sql_text: %MY_APP_TABLE%

3. Debug Memory Issues

-- Find cursors that might be consuming excessive memory
@qopencur.sql
-- Enter value for sql_text: %LARGE_TABLE%

4. Analyze Application Patterns

-- Look for SELECT patterns
@qopencur.sql
-- Enter value for sql_text: %SELECT%

-- Look for DML patterns
@qopencur.sql
-- Enter value for sql_text: %UPDATE%

Performance Analysis

Cursor Leak Detection

Count Cursors per Session

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

Find Sessions Near Limit

SELECT s.sid, s.username, COUNT(*) open_cursors,
       p.value max_cursors
FROM v$session s, v$open_cursor c, v$parameter p
WHERE s.sid = c.sid
AND p.name = 'open_cursors'
GROUP BY s.sid, s.username, p.value
HAVING COUNT(*) > p.value * 0.8
ORDER BY open_cursors DESC;

Memory Impact Analysis

Cursor Memory Usage
- Each open cursor consumes memory in the shared pool
- Multiple versions of same SQL waste memory
- Monitor for excessive cursor cache memory usage
Application Efficiency
- Look for unnecessary cursor duplication
- Identify applications not using bind variables
- Find opportunities for cursor sharing

Troubleshooting

High Open Cursor Counts

Check Application Code
- Verify cursors are properly closed
- Look for exception handling that skips cursor cleanup
- Check for loops that open cursors without closing

Parameter Tuning

-- Check current limit
SELECT name, value FROM v$parameter WHERE name = 'open_cursors';

-- Check session high-water marks
SELECT sid, value FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name = 'opened cursors cumulative';

Literal SQL vs Bind Variables
- Look for similar SQL with different literal values
- Identical statements should share same hash_value
- Multiple hash_values for similar logic indicates poor coding
Cursor Invalidation
- DDL operations can invalidate cursors
- Statistics gathering may cause cursor invalidation
- Check for frequent hard parsing

Active Session Analysis (gvsess.sql) - Overall session monitoring
Session Statistics (vsesstat.sql) - Session-level statistics
SQL Analysis (gvsqltop.sql) - Top SQL statements
Memory Analysis (vsgastat.sql) - Shared pool usage

Best Practices

Application Development

Proper Cursor Management

// Java example
try {
    PreparedStatement ps = conn.prepareStatement(sql);
    // Use the cursor
} finally {
    if (ps != null) ps.close(); // Always close
}

Use Bind Variables
- Promotes cursor sharing
- Reduces memory usage
- Improves performance
Connection Pool Configuration
- Set appropriate cursor limits
- Monitor cursor usage patterns
- Configure pool timeout settings

Monitoring Strategy

Regular Monitoring
- Check cursor counts during peak usage
- Monitor for trending increases
- Set up alerts for excessive counts
Performance Testing
- Include cursor usage in load testing
- Verify proper cleanup under stress
- Test exception scenarios

Notes

Uses UPPER() function for case-insensitive searches
Supports SQL wildcard patterns (% and _)
Output is ordered by SQL text for easy grouping
Truncates SQL text to 60 characters for readability
Hash value uniquely identifies each distinct SQL statement