4 Replies Latest reply: Dec 10, 2009 5:29 PM by Joze Senegacnik RSS

    impact of setting SESSION_CACHED_CURSORS=0 ?

    740179
      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
          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
            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
              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 ?
                Joze Senegacnik
                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