Oracle Scratchpad

March 27, 2013

Open Cursors

Filed under: Infrastructure,Oracle,Troubleshooting — Jonathan Lewis @ 6:13 pm BST Mar 27,2013

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
  • OPEN
  • OPEN-PL/SQL
  • OPEN-RECURSIVE
  • 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:

  • CACHED
  • KNT CACHED
  • KQD BUNDLE CACHED
  • KQD CACHED
  • KXCC CACHED
  • PL/SQL
  • PL/SQL CACHED
  • SYSTEM

3 Comments »

  1. I guess I must be getting old and grumpy. Why do they need varchar2(64) for 6 cursor types? (looking at an XE)

    Comment by jgarry — March 27, 2013 @ 8:19 pm BST Mar 27,2013 | Reply

  2. Hi Sir,

    I have query regarding v$OPEN_CURSOR but not exactly on CURSOR_TYPE…

    This is in 11.2.0.3

    In V$OPEN_CURSOR the USER_NAME column is the “User that is logged in to the session” a/c to the documentation.

    SQL> select * from v$open_cursor where *USER_NAME='SH'*;
     
    SADDR                   SID USER_NAME                      ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                                     LAST_SQL_ SQL_EXEC_ID CURSOR_TYPE
    ---------------- ---------- ------------------------------ ---------------- ---------- ------------- ------------------------------------------------------------ --------- ----------- ----------------------------------------------------------------
    0000000097BBCAF8         33 SH                             000000008C186458 4087094668 g4y6nw3tts7cc BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;                              DICTIONARY LOOKUP CURSOR CACHED
    0000000097BBCAF8         33 SH                             000000008E3E0EE8 3933222116 dyk4dprp70d74 SELECT DECODE('A','A','1','2') FROM DUAL                                           DICTIONARY LOOKUP CURSOR CACHED
    0000000097BF8418         13 SH                             000000008C0A91E0  649132870 gm9t6ycmb1yu6 delete from smon_scn_time where scn =  (select min(scn) from                       SESSION CURSOR CACHED
    0000000097BBCAF8         33 SH                             000000008C3B0178  914163366 4vs91dcv7u1p6 insert into sys.aud$( sessionid,entryid,statement,ntimestamp                       OPEN-RECURSIVE
    0000000097BBCAF8         33 SH                             000000008C22CF20  225524178 d6vwqbw6r2ffk SELECT USER FROM DUAL                                                              DICTIONARY LOOKUP CURSOR CACHED
    0000000097BF8418         13 SH                             000000008C07A960 4177627317 dma0vxbwh325p update smon_scn_time set time_mp=:1, time_dp=:2, scn=:3, scn                       SESSION CURSOR CACHED
    0000000097BBCAF8         33 SH                             000000008C05A850  616533857 cw6vxf0kbz3v1 SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE   (UPPER('                       DICTIONARY LOOKUP CURSOR CACHED
    0000000097BBCAF8         33 SH                             000000008C3DF6F0 4253530419 7hys3h7ysgf9m SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE F                       DICTIONARY LOOKUP CURSOR CACHED
    0000000097BBCAF8         33 SH                             0000000091B6D7D8 2212873362 cwnvyjq1ybj4k table_1_ff_208_0_0_0                                                               OPEN
    0000000097BAAD08         *39 SH                             0000000091B4B788  864012087 96g93hntrzjtr select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,                       BUNDLE DICTIONARY LOOKUP CACHED*
     
    10 rows selected.
    
    

    But it reality

    SQL> select sid,username from v$session where sid in (13,33,39);

    SID USERNAME
    ———- ——————————
    13
    33 SH
    *39 SYS*

    SID-39 is the SYS user but why it’s showing that for the user “SH”? If Oracle’s intention is to show all the sessions which are working on something(SYS is issuing the select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_c…) because of this session (SH), Why it’s not mentioned clearly in the documentation. It just says “User that is logged in to the session”

    CSM

    Comment by csmdba — March 28, 2013 @ 3:03 pm BST Mar 28,2013 | Reply

    • CSM,

      The easy answer to that is that I wouldn’t know, I didn’t write the specification, code, or manuals.

      However, my experience has been that the code changes faster than the manuals and the manuals accumulate lots of errors (or, at least, omissions and out of date details). Given that we can now see things like ‘dictionary’ SQL I would guess that the correct interpretation of the column is that in some cases, perhaps dependent on the cursor type, it identifies the schema that loaded the statement into the library cache. It wouldn’t be too difficult to devise a couple of tests to check whether this is a viable hypothesis.

      Comment by Jonathan Lewis — March 28, 2013 @ 3:35 pm BST Mar 28,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,530 other followers