Mastering Oracle Cursors: Views, Parameters, and Performance Tuning

1. What Is a Cursor in Oracle?

From a DBA perspective, a cursor is any executable object stored in the library cache, such as a SQL statement or a PL/SQL program unit. Developers usually consider only the SQL statement as a cursor, while Oracle also treats stored procedures as cursors in certain views.

2. Views That Expose Cursor Information

2.1 V$SQL

The V$SQL view lists each cursor (SQL statement) and provides columns such as:

SQL_TEXT – the text of the statement

SQL_FULLTEXT – the full text

SQL_ID – identifier of the statement

SHARABLE_MEM – shared memory used by the cursor

PERSISTENT_MEM, RUNTIME_MEM – fixed memory consumption

LOADS – number of times the cursor was (re)loaded into the library cache

FIRST_LOAD_TIME – timestamp of the first load (plan generation)

INVALIDATIONS – how many times the cursor became invalid

PARSE_CALLS – total parse operations (hard + soft)

DISK_READS, BUFFER_GETS – physical and logical reads

CPU_TIME, ELAPSED_TIME – CPU and total elapsed time (µs)

FETCHES, EXECUTIONS – fetch and execution counts

PX_SERVERS_EXECUTIONS – parallel executions

USERS_EXECUTING – sessions currently executing the cursor

MODULE, ACTION – application‑level identifiers set via DBMS_APPLICATION_INFO

PLAN_HASH_VALUE, HASH_VALUE – identifiers of the execution plan

OTHER METRICS – rows processed, optimizer mode, cost, wait times, etc.

For performance tuning, the four columns DISK_READS , BUFFER_GETS , CPU_TIME and ELAPSED_TIME are most critical. When overall database speed is unsatisfactory, DBA should identify statements with the highest values in these columns and focus optimization efforts there.

2.2 V$SQLAREA

V$SQLAREA aggregates data from all child cursors that share the same SQL text (i.e., belong to the same parent cursor). It shows the same columns as V$SQL, but values are summed across child cursors. An additional column, VERSION_COUNT , indicates how many child cursors exist for a given parent, revealing potential plan‑sharing inefficiencies.

2.3 V$OPEN_CURSOR

This view reports cursors currently opened in the session. When a cursor is parsed, Oracle places a library‑cache lock on its handle; the lock persists until the cursor is explicitly closed or the session ends. Excessive open cursors can exhaust the shared pool memory.

3. Open‑Cursor Limits (OPEN_CURSOR Parameter)

The OPEN_CURSOR initialization parameter limits the number of cursors a single session may have open simultaneously. Default values are 50 in Oracle 9i and 300 in Oracle 10g. If a session exceeds this limit, Oracle raises ORA‑01000: maximum open cursors exceeded . The limit protects the shared pool from being drained by forgotten cursors; cursors are automatically closed when the session disconnects.

4. Cursor Sharing (CURSOR_SHARING Parameter)

When many statements differ only by literal values (e.g., SELECT * FROM table WHERE id = 1, ... id = 2, …), they cannot share a cursor by default. The CURSOR_SHARING parameter controls whether Oracle attempts to share execution plans for such statements.

EXACT – default; only identical text shares a cursor.

SIMILAR – Oracle will share a cursor if the differing literals do not affect the optimal plan.

FORCE – Oracle shares a cursor whenever the text is the same except for literal values, regardless of plan optimality.

The setting can be changed at session level with ALTER SESSION SET CURSOR_SHARING = SIMILAR, affecting only that session.

5. Visual Illustrations

Three‑tier J2EE application example showing client, application server, and database server layers.

Cursor flow from Java application to database server when executing SELECT * FROM Test.

Illustration of parent and child cursor relationship.

Example of V$SQL and V$SQLAREA rows for identical statements with different execution plans.

6. Practical Takeaways for DBAs

Focus tuning on statements with the highest DISK_READS , BUFFER_GETS , CPU_TIME or ELAPSED_TIME .

Check VERSION_COUNT in V$SQLAREA to detect excessive child cursors that indicate poor plan sharing.

Monitor OPEN_CURSOR usage; increase the parameter only after confirming that the shared pool can accommodate more open cursors.

Consider setting CURSOR_SHARING = SIMILAR for workloads with many similar literals to reduce library‑cache pressure.

By understanding these views and parameters, a DBA can better diagnose performance bottlenecks, control memory consumption, and improve overall Oracle database efficiency.