Here’s a little detail that appeared in 11gR2 that may help you answer questions about open cursors. Oracle has added a “cursor type” column to the view v$open_cursor, so you can now see which cursors have been held open because of the pl/sql cursor cache, which have been held by the session cursor cache, and various other reasons why Oracle may take a short-cut when you fire a piece of SQL at it.
The following is the output showing the state of a particular session just after it has started up in SQL*Plus and called a PL/SQL procedure to run a simple count:
select cursor_type, sql_text from V$open_cursor where sid = 17 order by cursor_type, sql_text ; CURSOR_TYPE SQL_TEXT -------------------------------- ------------------------------------------------------------ DICTIONARY LOOKUP CURSOR CACHED BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END; BEGIN DBMS_OUTPUT.DISABLE; END; BEGIN DBMS_OUTPUT.ENABLE(1000000); END; BEGIN dbms_random.seed(0); END; SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE F SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER(' SELECT USER FROM DUAL select /*+ connect_by_filtering */ privilege#,level from sys select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECON select metadata from kopm$ where name='DB_FDO' select privilege# from sysauth$ where (grantee#=:1 or grante select to_char(sysdate,'hh24miss') time_now from dual select value$ from props$ where name = 'GLOBAL_DB_NAME' OPEN BEGIN spin_1; END; table_1_ff_208_0_0_0 OPEN-RECURSIVE insert into sys.aud$( sessionid,entryid,statement,ntimestamp PL/SQL CURSOR CACHED SELECT COUNT(*) X FROM KILL_CPU CONNECT BY N > PRIOR N START SESSION CURSOR CACHED BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; SELECT DECODE('A','A','1','2') FROM DUAL
Variations are left to the user.
There are a few other cursor types – here’s the list given in the 11.2 Server Reference manual under the definition of v$open_cursor:
- BUNDLE DICTIONARY LOOKUP CACHED
- CONSTRAINTS CURSOR CACHED
- DICTIONARY LOOKUP CURSOR CACHED
- PL/SQL CURSOR CACHED
- REPLICATION TRIGGER CURSOR CACHED
- SESSION CURSOR CACHED
It’s an interesting exercise to consider why there are so many types, and then create some tests to confirm or refute your hypothesis. I haven’t checked, but here are a few ideas:
- replication trigger cursor cached: I don’t remember which version introduced the change, but once upon a time the triggers updating the materialized view logs were real after insert/update/delete triggers, but now they’re “pre-compiled” – so it’s not surprising they form a special case.
- dictionary lookup cursor cached: are these, perhaps, the statements that are currently cached in the “_row_cache_cursors” cache for data dictionary access; the parameter was once set to 10, but currently defaults to 20.
- bundle dictionary lookup cached: why would there be a special case of dictionary lookup ? perhaps this is the set of cursors needed to read the first few tables in the data dictionary that allow the optimizer to do its work (how do you optimize a query against tab$ if you need to query syn$, obj$ and tab$ to discover that tab$ is a table ?)
- constraints cursor cached: probably something to do with the SQL (internal, or externalised) that Oracle has to run to check or implement details of referential integrity constraints.
Footnote (28th March):
By a strange coincidence a note came up on OTN today that pointed to a different version of the Oracle manual where the possible cursor types are listed under their “internal” names – but I’m not sure if there’s a version of Oracle where you’d see them looking like this:
- KNT CACHED
- KQD BUNDLE CACHED
- KQD CACHED
- KXCC CACHED
- PL/SQL CACHED