Bug 21135007 - Value of statistic "session cursor cache count" exceeds value of database parameter s

Bug 21135007 - Value of statistic "session cursor cache count" exceeds value of database parameter session_cached_cursors (文档 ID 21135007.8)

SQL> SELECT cach.value cache_hits, prs.value all_parses,round((cach.value/prs.value)*100,2) as "% found in cache"

  2  FROM  v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2

  3  WHERE cach.statistic# = nm1.statistic#

AND   nm1.name = 'session cursor cache hits'

  4    5  AND   prs.statistic#=nm2.statistic#

  6  AND   nm2.name= 'parse count (total)'

  7  AND   cach.sid= &sid and prs.sid= cach.sid;

Enter value for sid: 887

old   7: AND   cach.sid= &sid and prs.sid= cach.sid

new   7: AND   cach.sid= 887 and prs.sid= cach.sid

CACHE_HITS ALL_PARSES % found in cache

---------- ---------- ----------------

     31751    3256420              .98

 

 

set lines 120

col valuemax format 999

col valuemax for a10

SELECT a.value , p.value valuemax, s.sid, s.serial#

FROM v$sesstat a, v$statname b, v$session s, v$parameter2 p

WHERE a.statistic# = b.statistic# and s.sid=a.sid and a.sid=887

AND p.name='session_cached_cursors'

AND b.name = 'session cursor cache count';

 VALUE  VALUEMAX  SID    SERIAL#

----------  ---------- ----------         ----------

507      100               887      31001

 

 

Bug 21135007  Value of statistic "session cursor cache count" exceeds value of database parameter session_cached_cursors  This note gives a brief overview of bug 21135007.  The content was last updated on: 02-MAR-2018  Click here for details of each of the sections below. Affects: Product (Component) Oracle Server (Rdbms) Range of versions believed to be affected Versions BELOW 12.2 Versions confirmed as being affected 12.1.0.2 (Server Patch Set) 11.2.0.4 Platforms affected Generic (all / most platforms affected) Fixed: The fix for 21135007 is first included in 12.2.0.1 (Base Release) 12.1.0.2.171017 (Oct 2017) Bundle Patch for Windows Platforms Interim patches may be available for earlier versions - click here to check. Symptoms: Related To: (None Specified) Parallel Query (PQO) session_cached_cursors Description The value of the "session cursor cache count" statistic could exceed the value of the database parameter "session_cached_cursors" when parallelism is used. Rediscovery Notes If you see a unexpectedly high value for the "session cursor cache count" statistic and the session has been executing SQL statements in parallel, then you may be seeing the issue in this bug. Workaround None.   Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.   References Bug:21135007 (This link will only work for PUBLISHED bugs) Note:245840.1 Information on the sections in this article REFERENCES BUG:21135007 - SESSION CURSOR CACHE COUNT STATISTICS IS INCORRECT