This discussion is archived
4 Replies Latest reply: Dec 10, 2009 3:29 PM by joc RSS

impact of setting SESSION_CACHED_CURSORS=0 ?

740179 Newbie
Currently Being Moderated
what will be the impact of setting SESSION_CACHED_CURSORS=0 ? in terms of performance of the db

Thanks
  • 1. Re: impact of setting SESSION_CACHED_CURSORS=0 ?
    sb92075 Guru
    Currently Being Moderated
    what will be the impact of setting SESSION_CACHED_CURSORS=0 ? in terms of performance of the db
    It would likely result in some SQL running slower.
  • 2. Re: impact of setting SESSION_CACHED_CURSORS=0 ?
    739135 Newbie
    Currently Being Moderated
    setting SESSION_CACHED_CURSORS to a value greater than 0 can reduce soft parsing, If oracle server process finds a open cached cursor in the shared pool then it will by pass hard parse and soft parse both so the parsing will be literally free.
  • 3. Re: impact of setting SESSION_CACHED_CURSORS=0 ?
    611948 Newbie
    Currently Being Moderated
    AFAIK, I'd define it as number of cursors to cache in library cache for a session. If set to zero, no cursors will be cached, hence, lot of latching, ultimately lot of CPU overhead.
  • 4. Re: impact of setting SESSION_CACHED_CURSORS=0 ?
    joc Oracle ACE Director
    Currently Being Moderated
    We can check the efficiency of setting session_cached_cursors parameters with event 10270 ("Debug shared cursors“) at level 10. Here is the result from the trace file (Oracle 10g):

    {code}
    SQL> alter session set events '10270 trace name context forever, level 10';
    {code}

    {code}
    session_cached_cursors = 0
    #1: checking for already pinned child
    #1: no valid child pinned
    #1: pinning parent in shared mode to search 68509d68 694dcce0
    #1: ksfbc: calling kksSearchChildList outside while loop
    #1: kkslce: next child is #1
    #1: kkslce: pinning child #1 in shared mode 6832a12c 69f44490
    #1: kksCheckCriteria: calling kksauc
    #1: kksCheckCriteria: unpinning the parent
    {code}

    {code}
    session_cached_cursors > 0
    #1: checking for already pinned child
    #1: no valid child pinned
    #1: child already locked 68509d68, checking for validity
    {code}
    Each line means one step in performing soft parse. When this parameter is set to values bigger than 0 then the soft parse is even “softer”.

    I would suggest you to read the following Oracle white paper: http://www.oracle.com/technology/products/manageability/database/pdf/ow05/PS_S003_274003_106-1_FIN_v2.pdf - see page 17 to get very detailed information regarding session_cached_cursors.

    HTH, Joze

    Co-author of the forthcoming book "Expert Oracle Practices"
    http://www.apress.com/book/view/9781430226680

    Oracle related blog: http://joze-senegacnik.blogspot.com/
    Blog about flying: http://jsenegacnik.blogspot.com/
    Blog about Building Ovens, Baking and Cooking: http://senegacnik.blogspot.com

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points