Open Cursors Analysis (vsqlopen.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.

Open Cursors Analysis

This script provides comprehensive analysis of open cursors, combining cursor information with SQL performance metrics to identify high-activity SQL statements and their resource consumption patterns.

Script: vsqlopen.sql

rem  vsqlopen.sql
rem
set linesize 132
rem
ttitle 'Open Cursors'
rem
col sid format 9999 heading 'SID'
col executions format 99999999 heading 'EXECUTIONS'
col parse_calls format 9999999 heading 'PARSE|CALLS'
col disk_reads format 9999999 heading 'DISK|READS'
col phy_exe format 999999 heading 'PHY/|EXE'
col buffer_gets format 999999999 heading 'LOGICAL|READS'
col log_exe format 9999999 heading 'LOG/|EXE'
col sql_text format a60 heading 'SQL TEXT'
rem
break on report
compute sum of executions parse_calls disk_reads buffer_gets on report
rem
select sid,
       executions,
       parse_calls,
       disk_reads,
       disk_reads / decode( executions, 0, null, executions ) phy_exe,
       buffer_gets,
       buffer_gets / decode( executions, 0, null, executions ) log_exe,
       nvl( c.sql_text, s.sql_text ) sql_text
  from v$open_cursor c,
       v$sqlarea s
 where ( executions >= &execute
      or parse_calls >= &parse
      or disk_reads >= &physical
      or buffer_gets >= &logical )
   and c.address(+) = s.address
 order by buffer_gets desc, s.address, sid;
rem
set linesize 80

What This Script Does

This script provides detailed open cursor analysis by:

• Interactive Filtering: Prompts for threshold values to filter high-activity SQL
• Performance Metrics: Shows executions, parse calls, and I/O statistics
• Efficiency Ratios: Calculates I/O per execution for performance assessment
• Session Mapping: Links cursors to specific session IDs
• Resource Summary: Provides totals for key performance metrics
• SQL Text Display: Shows actual SQL statements for analysis
• Activity Ranking: Orders results by logical reads (buffer gets) for priority

Usage

-- Run the script and provide threshold values when prompted
@vsqlopen.sql
Enter value for execute: 1000
Enter value for parse: 500
Enter value for physical: 1000
Enter value for logical: 100000

-- Find all significantly active cursors
@vsqlopen.sql
Enter value for execute: 100
Enter value for parse: 50
Enter value for physical: 100
Enter value for logical: 10000

-- Focus on high I/O operations
@vsqlopen.sql
Enter value for execute: 0
Enter value for parse: 0
Enter value for physical: 5000
Enter value for logical: 0

Required Privileges

• SELECT privilege on V$OPEN_CURSOR
• SELECT privilege on V$SQLAREA
• Typically requires DBA role or SELECT_CATALOG_ROLE

Sample Output

                                   Open Cursors

 SID EXECUTIONS PARSE   DISK    PHY/   LOGICAL     LOG/  SQL TEXT
              CALLS   READS    EXE     READS      EXE
---- --------- ------- ------- ------ --------- ------- ------------------------------------------------------------
 156  12456789  123456  234567     19  45678901    3667 SELECT /*+ FIRST_ROWS */ * FROM sales_orders WHERE order_date
 234   8765432   87654  345678     39  34567890    3947 INSERT INTO order_items (order_id, product_id, quantity) VAL
 345   5432109   54321  123456     23  23456789    4318 UPDATE customers SET last_access = SYSDATE WHERE customer_id
 456   3210987   32109   87654     27  12345678    3847 SELECT COUNT(*) FROM products WHERE category_id = :1 AND act
 567   2109876   21098   65432     31   9876543    4677 DELETE FROM temp_calculations WHERE session_id = :1
 678   1098765   10987   43210     39   8765432    7982 SELECT o.order_id, c.customer_name FROM orders o, customers
 789    987654    9876   32109     33   7654321    7751 CREATE INDEX idx_orders_date ON orders(order_date) TABLESPAC
 890    876543    8765   21098     24   6543210    7467 ANALYZE TABLE sales_summary COMPUTE STATISTICS FOR ALL INDE
---- --------- ------- ------- ------ --------- ------- ------------------------------------------------------------
sum  34932155 3479610 953454          148887764

8 rows selected.

Key Output Columns

Session Information

Column	Description
SID	Session ID that has the cursor open

Execution Metrics

Column	Description
EXECUTIONS	Total number of times SQL has been executed
PARSE CALLS	Number of parse operations for this SQL

I/O Metrics

Column	Description
DISK READS	Physical disk reads performed
PHY/EXE	Physical reads per execution (efficiency indicator)
LOGICAL READS	Buffer gets (logical I/O operations)
LOG/EXE	Logical reads per execution (efficiency indicator)

SQL Information

Column	Description
SQL TEXT	Beginning of the SQL statement text

Understanding Performance Metrics

Execution Efficiency

• Low PHY/EXE: Efficient SQL with good buffer cache utilization
• High PHY/EXE: May indicate missing indexes or inefficient access paths
• High LOG/EXE: Possible inefficient SQL requiring optimization

Parse Activity

• High Parse Calls: May indicate hard parsing issues
• Parse/Execute Ratio: High ratio suggests bind variable issues
• Excessive Parsing: Can cause CPU and latch contention

Resource Consumption

• High Logical Reads: Major consumer of CPU and buffer cache
• High Physical Reads: I/O intensive operations affecting performance
• Combined High Values: Scripts needing immediate optimization attention

Common Use Cases

1. Identify Resource-Intensive SQL

-- Find high-resource consumption cursors
@vsqlopen.sql
-- execute: 1000
-- parse: 100
-- physical: 5000
-- logical: 100000

-- Focus on I/O intensive operations
@vsqlopen.sql
-- execute: 0
-- parse: 0
-- physical: 10000
-- logical: 500000

2. Parse Analysis

-- Find frequently parsed SQL
@vsqlopen.sql
-- execute: 0
-- parse: 1000
-- physical: 0
-- logical: 0

-- Check for hard parsing issues
SELECT sql_text, parse_calls, executions,
       ROUND(parse_calls/DECODE(executions,0,1,executions)*100,2) as parse_pct
FROM v$sqlarea
WHERE parse_calls > 100
ORDER BY parse_calls DESC;

3. Session-Specific Analysis

-- After running vsqlopen.sql, analyze specific sessions
SELECT s.sid, s.username, s.program, s.machine,
       COUNT(*) as open_cursors
FROM v$open_cursor oc, v$session s
WHERE oc.sid = s.sid
GROUP BY s.sid, s.username, s.program, s.machine
HAVING COUNT(*) > 10
ORDER BY COUNT(*) DESC;

4. Cursor Efficiency Analysis

-- Find inefficient cursors (high I/O per execution)
SELECT sql_text,
       executions,
       buffer_gets,
       disk_reads,
       ROUND(buffer_gets/DECODE(executions,0,1,executions)) as logical_per_exec,
       ROUND(disk_reads/DECODE(executions,0,1,executions)) as physical_per_exec
FROM v$sqlarea
WHERE executions > 100
  AND buffer_gets/DECODE(executions,0,1,executions) > 1000
ORDER BY buffer_gets/DECODE(executions,0,1,executions) DESC;

Advanced Analysis

1. Cursor Aging and Reuse

-- Analyze cursor lifecycle and reuse patterns
SELECT
  ROUND(first_load_time_hours) as hours_since_load,
  COUNT(*) as cursor_count,
  SUM(executions) as total_executions,
  ROUND(AVG(executions)) as avg_executions_per_cursor
FROM (
  SELECT sql_id,
         executions,
         ROUND((SYSDATE - TO_DATE(first_load_time, 'YYYY-MM-DD/HH24:MI:SS')) * 24) as first_load_time_hours
  FROM v$sqlarea
)
GROUP BY ROUND(first_load_time_hours)
ORDER BY hours_since_load;

2. Memory Usage Analysis

-- Analyze memory consumption of open cursors
SELECT
  s.sid,
  s.username,
  COUNT(*) as open_cursors,
  SUM(sa.sharable_mem) as total_sharable_mem,
  SUM(sa.persistent_mem) as total_persistent_mem,
  ROUND(AVG(sa.executions)) as avg_executions
FROM v$open_cursor oc,
     v$session s,
     v$sqlarea sa
WHERE oc.sid = s.sid
  AND oc.address = sa.address
GROUP BY s.sid, s.username
HAVING COUNT(*) > 5
ORDER BY SUM(sa.sharable_mem) DESC;

3. Application Pattern Analysis

-- Analyze cursor patterns by application/module
SELECT
  s.program,
  s.module,
  COUNT(DISTINCT oc.sql_id) as unique_sql_statements,
  COUNT(*) as total_open_cursors,
  ROUND(COUNT(*)/COUNT(DISTINCT oc.sql_id),1) as avg_cursors_per_sql,
  SUM(sa.executions) as total_executions
FROM v$open_cursor oc,
     v$session s,
     v$sqlarea sa
WHERE oc.sid = s.sid
  AND oc.address = sa.address
  AND s.username IS NOT NULL
GROUP BY s.program, s.module
ORDER BY total_executions DESC;

Performance Optimization Insights

1. Identify Optimization Candidates

-- Find SQL needing immediate attention
WITH cursor_efficiency AS (
  SELECT
    sql_id,
    sql_text,
    executions,
    buffer_gets,
    disk_reads,
    parse_calls,
    CASE
      WHEN executions > 0 THEN ROUND(buffer_gets/executions)
      ELSE 0
    END as logical_per_exec,
    CASE
      WHEN executions > 0 THEN ROUND(disk_reads/executions, 2)
      ELSE 0
    END as physical_per_exec,
    CASE
      WHEN executions > 0 THEN ROUND(parse_calls/executions * 100, 1)
      ELSE 0
    END as parse_percentage
  FROM v$sqlarea
  WHERE executions > 10
)
SELECT sql_id,
       SUBSTR(sql_text, 1, 80) as sql_text,
       executions,
       logical_per_exec,
       physical_per_exec,
       parse_percentage,
       CASE
         WHEN logical_per_exec > 10000 THEN 'High Logical I/O'
         WHEN physical_per_exec > 100 THEN 'High Physical I/O'
         WHEN parse_percentage > 50 THEN 'Excessive Parsing'
         ELSE 'Monitor'
       END as optimization_priority
FROM cursor_efficiency
WHERE logical_per_exec > 1000 OR physical_per_exec > 10 OR parse_percentage > 25
ORDER BY buffer_gets DESC;

2. Bind Variable Analysis

-- Check for potential bind variable issues
SELECT
  sql_text,
  version_count,
  executions,
  parse_calls,
  ROUND(parse_calls/DECODE(executions,0,1,executions)*100,1) as parse_pct,
  CASE
    WHEN version_count > 20 THEN 'Likely bind variable issue'
    WHEN parse_calls/DECODE(executions,0,1,executions) > 0.5 THEN 'High parse ratio'
    ELSE 'Normal'
  END as assessment
FROM v$sqlarea
WHERE executions > 100
  AND (version_count > 10 OR parse_calls/DECODE(executions,0,1,executions) > 0.1)
ORDER BY version_count DESC, parse_pct DESC;

Troubleshooting Common Issues

1. Too Many Open Cursors

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

-- Find sessions with most open cursors
SELECT s.sid, s.username, s.program, COUNT(*) as cursor_count
FROM v$open_cursor oc, v$session s
WHERE oc.sid = s.sid
GROUP BY s.sid, s.username, s.program
ORDER BY COUNT(*) DESC;

2. Memory Pressure from Cursors

-- Analyze shared pool impact
SELECT
  'Cursor Memory Usage' as metric,
  ROUND(SUM(sharable_mem)/1024/1024,1) as mb_used,
  COUNT(*) as cursor_count
FROM v$sqlarea
UNION ALL
SELECT
  'Open Cursor Memory',
  ROUND(SUM(sa.sharable_mem)/1024/1024,1),
  COUNT(*)
FROM v$open_cursor oc, v$sqlarea sa
WHERE oc.address = sa.address;

3. Cursor Leaks Detection

-- Find potential cursor leaks
SELECT s.sid, s.username, s.program, s.status,
       COUNT(*) as open_cursors,
       ROUND((SYSDATE - s.logon_time) * 24, 1) as hours_connected
FROM v$open_cursor oc, v$session s
WHERE oc.sid = s.sid
  AND s.status = 'INACTIVE'
GROUP BY s.sid, s.username, s.program, s.status, s.logon_time
HAVING COUNT(*) > 50
ORDER BY COUNT(*) DESC;

Integration with Other Scripts

This script works well with:

• Session Analysis (gvsess.sql) - For detailed session information
• SQL Performance Analysis (sql-id.sql) - For specific SQL tuning
• Library Cache Analysis (vlibcache.sql) - For shared pool analysis
• Top SQL Analysis (vsqltop.sql) - For overall SQL performance

Best Practices

Monitoring Guidelines

1. Regular Analysis: Run during peak business hours for realistic assessment
2. Threshold Tuning: Adjust filter values based on your environment’s scale
3. Trend Analysis: Track cursor efficiency metrics over time
4. Session Correlation: Connect cursor analysis with session and application analysis

Optimization Strategy

1. Priority Focus: Start with highest buffer gets SQL statements
2. Efficiency Ratios: Target SQL with high I/O per execution first
3. Parse Optimization: Address excessive parsing before I/O optimization
4. Application Review: Involve application teams for cursor management improvements

This script is essential for identifying SQL performance issues and understanding cursor usage patterns in Oracle databases.