4 Replies Latest reply: Jan 3, 2013 9:50 AM by marksmithusa RSS

    Session_Cached_cursors

    980508
      Hi all,

      I am checking this parameter for trying to get the ideal value.

      I run the script from metalink (ID 208857.1) and my results are:

      PARAMETER VALUE USAGE
      ---------------------- -------------------- -----
      session_cached_cursors 20 100%
      open_cursors 3000 3%

      Too, the following script:

      SELECT cach.value cache_hits, prs.value all_parses,round((cach.value/prs.value)*100,2) as "% found in cache"
      FROM v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
      WHERE cach.statistic# = nm1.statistic#
      AND nm1.name = 'session cursor cache hits'
      AND prs.statistic#=nm2.statistic#
      AND nm2.name= 'parse count (total)'
      AND cach.sid= &sid and prs.sid= cach.sid

      Results:

      CACHE_HITS ALL_PARSES % found in cache
      ---------- ---------- ----------------
      2659 7959 36.05


      I changed the session_cached_cursors to 100, and here the results:

      PARAMETER VALUE USAGE
      ---------------------- -------------------- -----
      session_cached_cursors 100 100%
      open_cursors 3000 6%


      CACHE_HITS ALL_PARSES % found in cache
      ---------- ---------- ----------------
      2869 7959 36.05

      I changed again to 300 but the results are similar. I noted that when I grow the session_cached_cursors the open_cursor grow too, Is it normal?

      Should I change again? which value is optimal?


      Thanks

      Edited by: ASP-SP on Jan 3, 2013 2:14 AM
        • 1. Re: Session_Cached_cursors
          Osama_Mustafa
          use tag format please
          what is your DB version
          OS version
          • 2. Re: Session_Cached_cursors
            980508
            Thank you for reply.

            BANNER
            ----------------------------------------------------------------
            Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
            PL/SQL Release 10.2.0.4.0 - Production
            CORE 10.2.0.4.0 Production
            TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
            NLSRTL Version 10.2.0.4.0 - Production

            OS: Win 2003 R2 server 64 bits

            Edited by: ASP-SP on Jan 3, 2013 2:20 AM
            • 3. Re: Session_Cached_cursors
              Osama_Mustafa
              Read
              http://osamamustafa.blogspot.com/2012/11/opencursor-in-oracle.html
              • 4. Re: Session_Cached_cursors
                marksmithusa
                A value of 20 seems very low. I think that Oracle advise that 500 is a decent estimate for a Production system.

                We have determined that we need a value of 2000 in one of our Production databases. We got to this by running volumetric tests against a Production-like environment. Increasing the values helps reduce parsing and, we discovered, actually managed to reduce the total phyiscal reads/writes during the batch.

                I think any formula you might see is a just meant as a guideline. You're better off trying it out for yourself in an appropriate test environment.