open_cursors & session_cached_cursor

 Open_cursors &  session_cached_cursor & Cursor Sharing

A cursor is just a handle to a DML statement.

You need a cursor to execute any DML in Oracle - be it an insert/update/delete/select whatever.

It is just a "handle", sort of like opening a file. Think of a select statement like "open file". Once you open the file, you can read from it, once you execute the cursor - you can read from it.

You nominally need a single cursor - to which you can associate any number of statements (one at a time).

the cursor area is allocated in your sessions UGA (user global area) which is normally in the PGA - but might be in the SGA if you are using shared server connections.

v$open_cursor may be queried to see what cursors you have open.

Cursors are a programming thing - all programs that access Oracle will allocate a cursor to execute SQL statements.

open_cursors

So, if open_cursors was set to 1000 and you execute a plsql routine with 1000 statments -- peek at v$open_cursor -- you'll find its chock full of open statements. PLSQL caches them for us.

open_cursors = maximum number of concurrently opened (by you, explicitly) cursors.

eg:

begin

open l_cursor1 for select * from dual;

open l_cursor2 for select * from dual;

...

open l_cursorN for select * from dual;

end;

open_cursors will dictate the highest number N could be -- you cannot have more than N open_cursors (you have to close some).

SELECT
user_name, COUNT(*) "Cursors per session"
FROM v$open_cursor
GROUP BY user_name;

 SESSION_CACHED_CURSORS

session_cached_cursors is an optimization behind the scenes. We keep an "array of pointers to parsed sql in the shared pool" in your client application. When you close a cursor (assuming you open/closed it 3 times, we don't cache immediately) we keep a pointer to it. The next time you open that cursor, we check out what we are pointing to and see if it is still relevant. If so, away we go (so we skip lots of work). If not, we parse as normal.

Session cached cursors is useful for environments that don't (or can't) cache the cursors (eg: DYNAMIC SQL in plql CANNOT cache the cursor).

session cached_cursors does not count against your "open_cursors" count.

SELECT

sid,user_name, COUNT(*) "Cursors per session"

FROM v$open_cursor

GROUP BY sid,user_name  2    3    4  ;