This discussion is archived
4 Replies Latest reply: Jan 3, 2013 7:50 AM by marksmithusa RSS

Session_Cached_cursors

980508 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    use tag format please
    what is your DB version
    OS version
  • 2. Re: Session_Cached_cursors
    980508 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    Read
    http://osamamustafa.blogspot.com/2012/11/opencursor-in-oracle.html
  • 4. Re: Session_Cached_cursors
    marksmithusa Journeyer
    Currently Being Moderated
    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.

Legend

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