关于session_cached_cursors的诡异问题

gdpr-dba

SELECT VALUE
          FROM v$parameter
         WHERE NAME = 'session_cached_cursors';

VALUE
----------
200

SELECT count(s.SID)         
        FROM v$statname n, v$sesstat s
         WHERE n.NAME = 'session cursor cache count'
           AND s.statistic# = n.statistic#
           AND s.VALUE > 200;

COUNT(S.SID)
---------------------
71

为什么有这么多会话的'session cursor cache count'大于参数'session_cached_cursors'的值，按道理不应该会出现这种情况的吧？

Maclean Liu(刘相兵

再次强调一下 提问 至少说明一下版本：


select * from v$version

gdpr-dba

不好意思，我的版本是11.2.0.3.0

Maclean Liu(刘相兵

Bug 8587035: 'SESSION CURSOR CACHE COUNT' INCORRECT IN V$SYSSTAT

Hdr: 8587035 10.2.0.4 RDBMS 10.2.0.4 VOS GEN SVCS PRODID-5 PORTID-214
Abstract: 'SESSION CURSOR CACHE COUNT' INCORRECT IN V$SYSSTAT


PROBLEM:
--------
Customer is seeing a very high values fo 'session cursor cache count'  in
v$sysstat on one RAC node, using the following query:


    select s.value  used
    from v$statname  n, v$sesstat  s
    where n.name = 'session cursor cache count'
    and s.statistic# = n.statistic#;


DIAGNOSTIC ANALYSIS:
--------------------
The issue reproduces on one RAC node, but it's ok on the other
node.

WORKAROUND:
-----------
Nona

RELATED BUGS:
-------------
Bug:5545735



'session cursor cache count' of v$sysstat is a current value based on Bug
1785201 and statspack script, but it seems a cumulative value since db
startup.





v$sesstat 或 V$SYSSTAT 可能 显示 session cursor cache count 统计项的累计值 而非 当前值  ，或者由于其他bug 导致该项
session cursor cache count 统计不准确

建议：

1. 忽略该问题， v$sesstat 或 V$SYSSTAT 中的 'session cursor cache count '记录仍能大致反应游标缓存情况，或者参考AWR中session cursor cache count 信息


2. 通过 errorstack 实际观察 session cached cursor dump


SQL> SELECT s.value
  2    FROM v$statname n, v$sesstat s,(select distinct sid from  v$mystat) m
  3   WHERE n.NAME = 'session cursor cache count'
  4     AND s.statistic# = n.statistic#
  5     AND m.sid = s.sid;

     VALUE
----------
         3

SQL> oradebug setmypid;
已处理的语句
SQL> oradebug dump errorstack 4;
已处理的语句
SQL> oradebug tracefile_name;
d:\app\maclean.liu\diag\rdbms\prod\prod\trace\prod_ora_3956.trc


FROM errorstack TRACE FILE:

----- Session Cached Cursor Dump -----
----- Generic Session Cached Cursor Dump -----
-----------------------------------------------------------
-------------- Generic Session Cached Cursors Dump --------
-----------------------------------------------------------
hash table=092C2664 cnt=3 LRU=08EA723C cnt=3 hit=8 max=3 NumberOfTypes=6



这里可以看到  max=3  count=3






type#0 name=DICTION count=0
type#1 name=BUNDLE  count=0
type#2 name=SESSION count=3
type#3 name=PL/SQL  count=0
type#4 name=CONSTRA count=0
type#5 name=REPLICA count=0
Bucket#002 seg=092C269C nit=2 nal=2 ips=2 sz=32 flg=3 ucnt=0
Bucket#025 seg=092C297C nit=2 nal=2 ips=2 sz=32 flg=3 ucnt=1
   0 cob=092C9FC8 idx=19 flg=0 typ=2 cur=092C9AF4 lru=1 fl=1
Bucket#033 seg=092C2A7C nit=2 nal=2 ips=2 sz=32 flg=3 ucnt=0
Bucket#056 seg=092C2D5C nit=2 nal=2 ips=2 sz=32 flg=3 ucnt=0
Bucket#109 seg=092C33FC nit=2 nal=2 ips=2 sz=32 flg=3 ucnt=1
   0 cob=092C7FB8 idx=6d flg=0 typ=2 cur=092C8004 lru=1 fl=1
Bucket#123 seg=092C35BC nit=2 nal=2 ips=2 sz=32 flg=3 ucnt=0
Bucket#127 seg=092C363C nit=2 nal=2 ips=2 sz=32 flg=3 ucnt=0
Bucket#130 seg=092C369C nit=2 nal=2 ips=2 sz=32 flg=3 ucnt=0
Bucket#162 seg=092C3A9C nit=2 nal=2 ips=2 sz=32 flg=3 ucnt=0
Bucket#180 seg=092C3CDC nit=2 nal=2 ips=2 sz=32 flg=3 ucnt=0
Bucket#195 seg=092C3EBC nit=2 nal=2 ips=2 sz=32 flg=3 ucnt=0
Bucket#197 seg=092C3EFC nit=2 nal=2 ips=2 sz=32 flg=3 ucnt=0
Bucket#203 seg=092C3FBC nit=2 nal=2 ips=2 sz=32 flg=3 ucnt=0
Bucket#222 seg=092C421C nit=2 nal=2 ips=2 sz=32 flg=3 ucnt=1
   0 cob=092CA014 idx=de flg=0 typ=2 cur=092C9D44 lru=1 fl=1
Bucket#227 seg=092C42BC nit=2 nal=2 ips=2 sz=32 flg=3 ucnt=0
Bucket#248 seg=092C455C nit=2 nal=2 ips=2 sz=32 flg=3 ucnt=0
-----------------------------------------------------------
------Finished Dumping Generic Session Cached Cursors------
-----------------------------------------------------------
----- RDBMS Session Cached Cursor Dump -----
----------------------------------------
----- Dump Cursor sql_id=grwydz59pu6mc xsc=0x092C8004 cur=0x00000000 -----

LibraryHandle:  Address=c91ad028 Hash=535d1a6c LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
  ObjectName:  Name=select text from view$ where rowid=:1

    FullHashValue=f0cfa2e63e1fda46fbf3cdf9535d1a6c Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=1398610540 OwnerIdn=0
  Statistics:  InvalidationCount=1 ExecutionCount=34 LoadCount=4 ActiveLocks=1 TotalLockCount=22 TotalPinCount=1
  Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=2 KeepHandle=2 BucketInUse=21 HandleInUse=21
  Concurrency:  DependencyMutex=c91ad090(0, 0, 0, 0) Mutex=c91ad0dc(50, 140, 0, 6)
  Flags=RON/PIN/TIM/PN0/DBN/[10012841]
  WaitersLists:  
    Lock=c91ad080[c91ad080,c91ad080]
    Pin=c91ad088[c91ad070,c91ad070]
  Timestamp:  Current=05-04-2012 11:52:26
  LibraryObject:  Address=c56ce060 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
    ChildTable:  size='16'
      Child:  id='0' Table=c56cefb0 Reference=c56ce66c Handle=c91acd94
      Child:  id='1' Table=c56cefb0 Reference=c56ce9dc Handle=c47c3b9c
    Children:  
      Child:  childNum='0'
        LibraryHandle:  Address=c91acd94 Hash=0 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
          Name:  Namespace=SQL AREA(00) Type=CURSOR(00)
          Statistics:  InvalidationCount=1 ExecutionCount=10 LoadCount=2 ActiveLocks=0 TotalLockCount=13 TotalPinCount=33
          Counters:  BrokenCount=2 RevocablePointer=2 KeepDependency=0 KeepHandle=0 BucketInUse=0 HandleInUse=0
          Concurrency:  DependencyMutex=c91acdfc(0, 0, 0, 0) Mutex=c91ad0dc(50, 140, 0, 6)
          Flags=RON/PIN/PN0/EXP/[10012111]
          WaitersLists:  
            Lock=c91acdec[c91acdec,c91acdec]
            Pin=c91acdf4[c91acddc,c91acddc]
          LibraryObject:  Address=c5654060 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
          NamespaceDump:  
            Child Cursor:  Heap0=C56540E4 Heap6=C4B6BE10 Heap0 Load Time=05-04-2012 11:52:27 Heap6 Load Time=05-04-2012 11:52:27
      Child:  childNum='1'
        LibraryHandle:  Address=c47c3b9c Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
          Name:  Namespace=SQL AREA(00) Type=CURSOR(00)
          Statistics:  InvalidationCount=0 ExecutionCount=22 LoadCount=1 ActiveLocks=1 TotalLockCount=13 TotalPinCount=48
          Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=0 HandleInUse=0
          Concurrency:  DependencyMutex=c47c3c04(0, 0, 0, 0) Mutex=c91ad0dc(50, 140, 0, 6)
          Flags=RON/PIN/PN0/EXP/[10012111]
          WaitersLists:  
            Lock=c47c3bf4[c47c3bf4,c47c3bf4]
            Pin=c47c3bfc[c47c3be4,c47c3be4]
          LibraryObject:  Address=c543f9d8 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
          NamespaceDump:  
            Child Cursor:  Heap0=C543FA5C Heap6=C4CE5FF0 Heap0 Load Time=05-04-2012 11:52:33 Heap6 Load Time=05-04-2012 11:52:33
  NamespaceDump:  
    Parent Cursor:  sql_id=grwydz59pu6mc parent=C56CE0E4 maxchild=2 plk=y ppn=n
      Current Cursor Sharing Diagnostics Nodes:  
        Child Node: 0  ID=3 reason=Optimizer mismatch(12) size=2x204
          parameters(cursor): 000000000000005a00a826e10000000500020000001000000001000000000000
000000000000200000800002000007d000380101000000f80000040000019000
000af0000000000000a826e1efa664e50003239307fff1002f3fac00f2501d67
00b275b2000200000001817df81b0000003fc0db000000000000000b0000000a
00000fde00000000028a55281f43220001d42bfc35d00b0a10000009c002840a
0000000100000064000003e8df0100645c0003fb000000030000000000080000
3f0001900003000000000000  
          parameters(current): 000000000000005a00a826e10000000500020000001000000001000000000000
000000000000200000800002000007d000380101000000f80000040000023000
000af0000000000000a826e1efa664e50003239307fff1002f3fac00f2501d67
00b275b2000200000001817df81b0000003fc0db000000000000000b0000000a
00000fde00000000028a55281f43220001d42bfc35d00b0a10000009c002840a
0000000100000064000003e8df0100645c0003fb000000030000000000080000
3f0001900003000000000000     kkscs=C56CE450 nxt=C56CE85C flg=18 cld=0 hd=C91ACD94 par=C56CE0E4
   Mutex C56CE450(0, 0) idn 0
   ct=1 hsh=0 unp=00000000 unn=0 hvl=c56cefa0 nhv=0 ses=00000000
   hep=C56CE4A4 flg=80 ld=1 ob=C5654060 ptr=C4B6BE10 fex=C4B6B02C
   kkscs=C56CE85C nxt=00000000 flg=18 cld=1 hd=C47C3B9C par=C56CE0E4
   Mutex C56CE85C(0, 0) idn 0
   ct=2 hsh=0 unp=00000000 unn=0 hvl=c56ceffc nhv=0 ses=00000000
   hep=C56CE8B0 flg=80 ld=1 ob=C543F9D8 ptr=C4CE5FF0 fex=C4CE520C
cursor instantiation=0x092C8004 used=1336103979 exec_id=0 exec=3
child#1(0xC47C3B9C) pcs=0xC56CE85C
  clk=0xC96E0890 ci=0xC543FA5C pn=0x00000000 ctx=0xC4CE5FF0
kgsccflg=1 llk[0x092C9D48,0x08EA723C] idx=6d
xscflg=100410 fl2=1000401 fl3=22100 fl4=0
----- Bind Byte Code (IN) -----
  Opcode = 1   Unoptimized
  Offsi = 36, Offsi = 0
----- Bind Info (kkscoacd) -----
Bind#0
  oacdty=11 mxl=16(00) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
  No bind buffers allocated
Frames pfr 0x00000000 siz=1452 efr 0x00000000 siz=1444
Cursor frame dump
  enxt: 3.0x00000140  enxt: 2.0x00000014  enxt: 1.0x00000450
  pnxt: 1.0x00000008
----------------------------------------
----- Dump Cursor sql_id=03u7j8fq6fz6x xsc=0x092C9D44 cur=0x00000000 -----

LibraryHandle:  Address=c46f9d58 Hash=ac677cdd LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
  ObjectName:  Name=select /* maclean */ 123 from tab where rownum=1

    FullHashValue=ef3d6929e711428e01e8f143ac677cdd Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=2892463325 OwnerIdn=0
  Statistics:  InvalidationCount=0 ExecutionCount=5 LoadCount=3 ActiveLocks=1 TotalLockCount=4 TotalPinCount=1
  Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=2 KeepHandle=2 BucketInUse=3 HandleInUse=3
  Concurrency:  DependencyMutex=c46f9dc0(0, 0, 0, 0) Mutex=c46f9e0c(50, 56, 0, 6)
  Flags=RON/PIN/TIM/PN0/DBN/[10012841]
  WaitersLists:  
    Lock=c46f9db0[c46f9db0,c46f9db0]
    Pin=c46f9db8[c46f9da0,c46f9da0]
  Timestamp:  Current=05-04-2012 11:59:38
  LibraryObject:  Address=c52f99d8 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
    ChildTable:  size='16'
      Child:  id='0' Table=c52fa928 Reference=c52f9fe4 Handle=c46f9ac4
      Child:  id='1' Table=c52fa928 Reference=c52fa1b8 Handle=c46f8984
    Children:  
      Child:  childNum='0'
        LibraryHandle:  Address=c46f9ac4 Hash=0 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
          Name:  Namespace=SQL AREA(00) Type=CURSOR(00)
          Statistics:  InvalidationCount=0 ExecutionCount=1 LoadCount=1 ActiveLocks=0 TotalLockCount=3 TotalPinCount=3
          Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=0 HandleInUse=0
          Concurrency:  DependencyMutex=c46f9b2c(0, 0, 0, 0) Mutex=c46f9e0c(50, 56, 0, 6)
          Flags=RON/PIN/PN0/EXP/[10012111]
          WaitersLists:  
            Lock=c46f9b1c[c46f9b1c,c46f9b1c]
            Pin=c46f9b24[c46f9b0c,c46f9b0c]
          LibraryObject:  Address=c52f89d8 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
          NamespaceDump:  
            Child Cursor:  Heap0=C52F8A5C Heap6=C3D6AE34 Heap0 Load Time=05-04-2012 11:59:38 Heap6 Load Time=05-04-2012 11:59:38
      Child:  childNum='1'
        LibraryHandle:  Address=c46f8984 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
          Name:  Namespace=SQL AREA(00) Type=CURSOR(00)
          Statistics:  InvalidationCount=0 ExecutionCount=4 LoadCount=1 ActiveLocks=1 TotalLockCount=3 TotalPinCount=5
          Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=0 HandleInUse=0
          Concurrency:  DependencyMutex=c46f89ec(0, 0, 0, 0) Mutex=c46f9e0c(50, 56, 0, 6)
          Flags=RON/PIN/PN0/EXP/[10012111]
          WaitersLists:  
            Lock=c46f89dc[c46f89dc,c46f89dc]
            Pin=c46f89e4[c46f89cc,c46f89cc]
          LibraryObject:  Address=c52f59d8 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
          NamespaceDump:  
            Child Cursor:  Heap0=C52F5A5C Heap6=C3D60E34 Heap0 Load Time=05-04-2012 11:59:39 Heap6 Load Time=05-04-2012 11:59:39
  NamespaceDump:  
    Parent Cursor:  sql_id=03u7j8fq6fz6x parent=C52F9A5C maxchild=2 plk=y ppn=n
      Current Cursor Sharing Diagnostics Nodes:  
        Child Node: 0  ID=3 reason=Optimizer mismatch(13) size=3x4
          kxscflg: 32
          kxscfl4: 4194560
          dnum_kksfcxe: 0    kkscs=C52F9DC8 nxt=C52FA038 flg=1a cld=0 hd=C46F9AC4 par=C52F9A5C
   Mutex C52F9DC8(0, 0) idn 0
   ct=0 hsh=0 unp=00000000 unn=0 hvl=c52fa974 nhv=0 ses=00000000
   hep=C52F9E1C flg=80 ld=1 ob=C52F89D8 ptr=C3D6AE34 fex=C3D6A050
   kkscs=C52FA038 nxt=00000000 flg=18 cld=1 hd=C46F8984 par=C52F9A5C
   Mutex C52FA038(0, 0) idn 0
   ct=1 hsh=0 unp=00000000 unn=0 hvl=c52fa918 nhv=0 ses=00000000
   hep=C52FA08C flg=80 ld=1 ob=C52F59D8 ptr=C3D60E34 fex=C3D60050
cursor instantiation=0x092C9D44 used=1336103981 exec_id=0 exec=2
child#1(0xC46F8984) pcs=0xC52FA038
  clk=0xC96E04BC ci=0xC52F5A5C pn=0x00000000 ctx=0xC3D60E34
kgsccflg=1 llk[0x092C9AF8,0x092C8008] idx=de
xscflg=10001c fl2=80000001 fl3=22000 fl4=0
Frames pfr 0x00000000 siz=12392 efr 0x00000000 siz=12384
Cursor frame dump
  enxt: 4.0x00000584  enxt: 3.0x00000fec  enxt: 2.0x00000afc  enxt: 1.0x00000ff4
  pnxt: 1.0x00000008
----------------------------------------
----- Dump Cursor sql_id=101vpbnr0p9hs xsc=0x092C9AF4 cur=0x00000000 -----

LibraryHandle:  Address=c46f873c Hash=2e0aa618 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
  ObjectName:  Name=SELECT s.value
  FROM v$statname n, v$sesstat s,(select distinct sid from  v$mystat) m
WHERE n.NAME = 'session cursor cache count'
   AND s.statistic# = n.statistic#
   AND m.sid = s.sid

    FullHashValue=ba32c29647b2d1aa1007755d2e0aa618 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=772449816 OwnerIdn=0
  Statistics:  InvalidationCount=0 ExecutionCount=3 LoadCount=2 ActiveLocks=1 TotalLockCount=3 TotalPinCount=1
  Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 KeepHandle=1 BucketInUse=2 HandleInUse=2
  Concurrency:  DependencyMutex=c46f87a4(0, 0, 0, 0) Mutex=c46f87f0(50, 38, 0, 6)
  Flags=RON/PIN/TIM/PN0/DBN/[10012841]
  WaitersLists:  
    Lock=c46f8794[c46f8794,c46f8794]
    Pin=c46f879c[c46f8784,c46f8784]
  Timestamp:  Current=05-04-2012 12:00:04
  LibraryObject:  Address=c52f49d8 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
    ChildTable:  size='16'
      Child:  id='0' Table=c52f5928 Reference=c52f4fe4 Handle=c46f84a8
    Children:  
      Child:  childNum='0'
        LibraryHandle:  Address=c46f84a8 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
          Name:  Namespace=SQL AREA(00) Type=CURSOR(00)
          Statistics:  InvalidationCount=0 ExecutionCount=3 LoadCount=1 ActiveLocks=1 TotalLockCount=3 TotalPinCount=4
          Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=0 HandleInUse=0
          Concurrency:  DependencyMutex=c46f8510(0, 0, 0, 0) Mutex=c46f87f0(50, 38, 0, 6)
          Flags=RON/PIN/PN0/EXP/[10012111]
          WaitersLists:  
            Lock=c46f8500[c46f8500,c46f8500]
            Pin=c46f8508[c46f84f0,c46f84f0]
          LibraryObject:  Address=c52f39d8 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
          NamespaceDump:  
            Child Cursor:  Heap0=C52F3A5C Heap6=C3D58E34 Heap0 Load Time=05-04-2012 12:00:04 Heap6 Load Time=05-04-2012 12:00:04
  NamespaceDump:  
    Parent Cursor:  sql_id=101vpbnr0p9hs parent=C52F4A5C maxchild=1 plk=y ppn=n    kkscs=C52F4DC8 nxt=00000000 flg=18 cld=0 hd=C46F84A8 par=C52F4A5C
   Mutex C52F4DC8(0, 0) idn 0
   ct=0 hsh=0 unp=00000000 unn=0 hvl=c52f5974 nhv=0 ses=00000000
   hep=C52F4E1C flg=80 ld=1 ob=C52F39D8 ptr=C3D58E34 fex=C3D58050
cursor instantiation=0x092C9AF4 used=1336104382 exec_id=0 exec=1
child#0(0xC46F84A8) pcs=0xC52F4DC8
  clk=0xC96E06EC ci=0xC52F3A5C pn=0x00000000 ctx=0xC3D58E34
kgsccflg=1 llk[0x08EA723C,0x092C9D48] idx=19
xscflg=100014 fl2=80000001 fl3=22000 fl4=0
Frames pfr 0x00000000 siz=7596 efr 0x00000000 siz=7588
Cursor frame dump
  enxt: 3.0x00000b48  enxt: 2.0x0000025c  enxt: 1.0x00001000
  pnxt: 1.0x00000008
----- PL/SQL Runtime State -----

gdpr-dba

DIAGNOSTIC ANALYSIS:
--------------------
The issue reproduces on one RAC node, but it's ok on the other
node.

刘大，我的问题和描述的有点不一样，我的两个节点都出现了同样的情况。

Maclean Liu(刘相兵

v$sesstat 或 v$sysstat 统计值不准确 的现象非常普遍
如果 你想得到精确值 可以做errorstack  来观察
对于 性能诊断 或者一般性的演示 则仍可以信任 v$sesstat或 V$SYSSTAT


可以查到的已记录在案的 v$sesstat的 bug: