|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
Each SQL statement is associated with a cursor. The cursor is a data structure that keeps track of all data that is needed to process the statement.
In PL/SQL, all SQL statements except the select statement create an implicit cursor. That is: the cursor is not explicitely declared. For select statements, an explicit cursor can be declared. Such an explicit cursor can be thought of as a pointer to a record in a table or record set that can be moved forward within this table or record set.
An explit cursor is declared and used, for example, like so:
declare cursor explic_cur is select table_name from user_tables; begin for r in explic_cur loop dbms_output.put_line(r.table_name); end loop; end; /
Note: not all select statements in PL/SQL are explicit. For example, the following creates in implicit cursor:
declare cnt number; begin select count(*) into cnt from user_tables; dbms_output.put_line( 'There are ' || cnt || ' tables in your schema' ); end; /
A cursor "points" into the shared pool where its parsed sql statement is stored. A cursor also occupies memory in the pga to store the values of bind variables and to keep information about the cursor's state (open, bound, executed, closed).
A cursor being open means that its associated statement is in the sql area. Parsing is not necessary for such cursors.
The initialization parameter session_cached_cursors specifies how many cursors are held open for a session. Such cached cursors are stored in the session cursor cache.
If a new cursor needs to me moved into the session cursor cache, the cursor that hasn't been used for the longest time will be removed from the session cursor cache.
The higher the value of session_cached_cursors is set to a high value, the more the shared pool might become fragmented.