351 积分	0 好友	8 主题

发消息

关于session_cached_cursors的诡异问题

1^#

发表于 2012-5-4 10:41:58 | 查看: 7004| 回复: 5

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'的值，按道理不应该会出现这种情况的吧？

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

2^#

发表于 2012-5-4 11:39:16

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

select * from v$version

回复只看该作者道具举报

gdpr-dba

3^#

发表于 2012-5-4 11:50:55

不好意思，我的版本是11.2.0.3.0

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2012-5-4 12:14:48

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

5^#

发表于 2012-5-4 12:22:48

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

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

回复只看该作者道具举报

Maclean Liu(刘相兵

6^#

发表于 2012-5-4 12:24:38

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

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

v$sesstat bug.png

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册