SQL statement parsing stages

SQL Parsing Flow Diagram:

 

NOTES

1. A cursor is an address on the client that points to the memory location of a SQL statement on the server. Multiple-client cursors may point at the same address on the server.

2. Remember that 'Client' and 'Server' sides may reside on the same machine - in which case Client/Server is a logical distinction.

3. If a cursor is open, then the statement will be in the sql_area, so no parsing is necessary. This is why locks may remain when a client is terminated abnormally (such as a PC Client being turned off without closing open cursors).

4. SESSION_CACHED_CURSORS is the initialisation parameter that specifies how many cursors to hold open for a particular session.
The open cursor request will still be sent to the server but it will not be executed once a matching cursor is found in the session cursor cache.

5. HOLD_CURSOR is an precompiler parameter that specifies that an individual cursor should be held open. See:

Change value of parameter to all connected sessions

The ALTER SYSTEM SET clause for this parameter is only valid with the SCOPE=SPFILE option, so you can not change the parameter value with this dynamically.

You can change the value for all other sessions, e.g as below:

begin
for c in (select sid, serial# from v$session) loop
   dbms_system.set_int_param_in_session(c.sid,c.serial#,'session_cached_cursors', 100);
end loop;
end;
/