问题背景：
今天公司的OA系统运行的时候，用户反馈有时候登陆到OA平台会显示登陆不了。OA管理员检查发现应用没有问题，但是连接到数据库的时候有时候会报错；
果不其然登陆到OEM cloudcontrol的时候发现数据库有相应的报错信息，马上对相应的报错进行排查；
数据库版本：11.2.0.1 64位
操作系统版本：centos5.9
报错信息：
Trace file /data/oracle/diag/rdbms/ekpj/ekpj/trace/ekpj_ora_6576.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /data/oracle/oracle11
System name: Linux
Node name: ekpdb01
Release: 2.6.18-348.el5
Version: #1 SMP Tue Jan 8 17:53:53 EST 2013
Machine: x86_64
Instance name: ekpj
Redo thread mounted by this instance: 1
Oracle process number: 141
Unix process pid: 6576, image: oracle@ekpdb01
*** 2014-03-17 10:09:34.361
*** SESSION ID:(647.3487) 2014-03-17 10:09:34.361
*** CLIENT ID:() 2014-03-17 10:09:34.361
*** SERVICE NAME:(SYS$USERS) 2014-03-17 10:09:34.361
*** MODULE NAME:(JDBC Thin Client) 2014-03-17 10:09:34.361
*** ACTION NAME:() 2014-03-17 10:09:34.361
--- PROTOCOL VIOLATION DETECTED ---
----- Dump Cursor sql_id=9svnzn8h7ac59 xsc=0x2b28a614b380 cur=0x2b28a5ae2d20 -----
LibraryHandle: Address=3f296060 Hash=206530a9 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=select joecostpay0_.fd_id as col_0_0_ from ekpj.joe_cost_payment_tem joecostpay0_ left outer join ekpj.joe_payment_tem_areader authallrea1_ on joecostpay0_.fd_id=authallrea1_.fd_doc_id left outer join ekpj.sys_org_element sysorgelem2_ on authallrea1_.fd_org_id=sysorgelem2_.fd_id where joecostpay0_.fd_parent_id=:1 and (joecostpay0_.auth_reader_flag=1 or sysorgelem2_.fd_id in (:2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 ))
FullHashValue=e67bc57b362e0f859c6e9fa2206530a9 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=543502505 OwnerIdn=85
Statistics: InvalidationCount=2 ExecutionCount=1066 LoadCount=33 ActiveLocks=1 TotalLockCount=1001 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=5 KeepHandle=5 BucketInUse=1000 HandleInUse=1000
Concurrency: DependencyMutex=3f296110(0, 0, 0, 0) Mutex=3f296188(647, 7157, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=3f2960f0[3f2960f0,3f2960f0]
Pin=3f296100[3f2960d0,3f2960d0]
Timestamp: Current=03-03-2014 08:47:44
LibraryObject: Address=db0db418 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
ChildTable: size='16'
Child: id='0' Table=db0dc318 Reference=db0dbd68 Handle=3ce65f10
Child: id='1' Table=db0dc318 Reference=db0dc168 Handle=3f11fe28
Child: id='2' Table=db0dc318 Reference=dbe58aa0 Handle=3fab70a0
Child: id='3' Table=db0dc318 Reference=dbe58e18 Handle=3ebec730
Child: id='4' Table=db0dc318 Reference=a271eed0 Handle=3e7ef458
Child: id='5' Table=db0dc318 Reference=a271f220 Handle=3e477970
Children:
Child: childNum='0'
LibraryHandle: Address=3ce65f10 Hash=0 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
Name: Namespace=SQL AREA(00) Type=CURSOR(00)
Statistics: InvalidationCount=1 ExecutionCount=2 LoadCount=9 ActiveLocks=0 TotalLockCount=75 TotalPinCount=78
Counters: BrokenCount=2 RevocablePointer=2 KeepDependency=0 KeepHandle=0 BucketInUse=0 HandleInUse=0
Concurrency: DependencyMutex=3ce65fc0(0, 0, 0, 0) Mutex=3f296188(647, 7157, 0, 6)
Flags=RON/PIN/PN0/EXP/[10012111]
WaitersLists:
Lock=3ce65fa0[3ce65fa0,3ce65fa0]
Pin=3ce65fb0[3ce65f80,3ce65f80]
LibraryObject: Address=a1e87c58 HeapMask=0000-0001-0001 Flags=EXS/RIV[0200] Flags2=[0000] PublicFlags=[0000]
NamespaceDump:
Child Cursor: Heap0=0x3a1e87d40 Heap6=0x43ce65f10 Heap0 Load Time=03-16-2014 11:18:12 Heap6 Load Time=03-16-2014 11:18:12
Child: childNum='1'
LibraryHandle: Address=3f11fe28 Hash=0 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
Name: Namespace=SQL AREA(00) Type=CURSOR(00)
Statistics: InvalidationCount=1 ExecutionCount=69 LoadCount=8 ActiveLocks=0 TotalLockCount=268 TotalPinCount=283
Counters: BrokenCount=3 RevocablePointer=3 KeepDependency=0 KeepHandle=0 BucketInUse=0 HandleInUse=0
Concurrency: DependencyMutex=3f11fed8(0, 0, 0, 0) Mutex=3f296188(647, 7157, 0, 6)
Flags=RON/PIN/PN0/EXP/[10012111]
WaitersLists:
Lock=3f11feb8[3f11feb8,3f11feb8]
Pin=3f11fec8[3f11fe98,3f11fe98]
LibraryObject: Address=a27e8c58 HeapMask=0000-0001-0001 Flags=EXS/RIV[0200] Flags2=[0000] PublicFlags=[0000]
NamespaceDump:
Child Cursor: Heap0=0x3a27e8d40 Heap6=0x3658a19c8 Heap0 Load Time=03-14-2014 13:10:31 Heap6 Load Time=03-15-2014 09:27:44
Child: childNum='2'
LibraryHandle: Address=3fab70a0 Hash=0 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
Name: Namespace=SQL AREA(00) Type=CURSOR(00)
Statistics: InvalidationCount=0 ExecutionCount=2 LoadCount=7 ActiveLocks=0 TotalLockCount=375 TotalPinCount=408
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=0 HandleInUse=0
Concurrency: DependencyMutex=3fab7150(0, 0, 0, 0) Mutex=3f296188(647, 7157, 0, 6)
Flags=RON/PIN/PN0/EXP/[10012111]
WaitersLists:
Lock=3fab7130[3fab7130,3fab7130]
Pin=3fab7140[3fab7110,3fab7110]
LibraryObject: Address=a095ca00 HeapMask=0000-0001-0001 Flags=EXS/RIV[0200] Flags2=[0000] PublicFlags=[0000]
NamespaceDump:
Child Cursor: Heap0=0x3a095cae8 Heap6=0x3646999c8 Heap0 Load Time=03-15-2014 09:12:52 Heap6 Load Time=03-15-2014 09:12:52
Child: childNum='3'
LibraryHandle: Address=3ebec730 Hash=0 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
Name: Namespace=SQL AREA(00) Type=CURSOR(00)
Statistics: InvalidationCount=0 ExecutionCount=22 LoadCount=5 ActiveLocks=0 TotalLockCount=238 TotalPinCount=254
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=0 HandleInUse=0
Concurrency: DependencyMutex=3ebec7e0(0, 0, 0, 0) Mutex=3f296188(647, 7157, 0, 6)
Flags=RON/PIN/PN0/EXP/[10012111]
WaitersLists:
Lock=3ebec7c0[3ebec7c0,3ebec7c0]
Pin=3ebec7d0[3ebec7a0,3ebec7a0]
LibraryObject: Address=db4a1060 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
NamespaceDump:
Child Cursor: Heap0=0x3db4a1148 Heap6=0x37aa71590 Heap0 Load Time=03-16-2014 12:08:07 Heap6 Load Time=03-17-2014 10:09:33
Child: childNum='4'
LibraryHandle: Address=3e7ef458 Hash=0 LockMode=N PinMode=S LoadLockMode=0 Status=VALD
Name: Namespace=SQL AREA(00) Type=CURSOR(00)
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=1 TotalLockCount=22 TotalPinCount=25
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=0 HandleInUse=0
Concurrency: DependencyMutex=3e7ef508(0, 0, 0, 0) Mutex=3f296188(647, 7157, 0, 6)
Flags=RON/PIN/PN0/EXP/[10012111]
WaitersLists:
Lock=3e7ef4e8[3e7ef4e8,3e7ef4e8]
Pin=3e7ef4f8[3e7ef4c8,3e7ef4c8]
LibraryObject: Address=d9412938 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=CCUR^206530a9 pins=0 Change=NONE
Heap=73b473c0 Pointer=d9412a20 Extent=d9412908 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=7.085938 Size=7.898438 LoadTime=6566111700
Block: #='6' name=SQLA^206530a9 pins=0 Change=NONE
Heap=a271ed70 Pointer=6490fc90 Extent=6490f030 Flags=I/-/P/A/-/E
FreedLocation=0 Alloc=23.523438 Size=23.742188 LoadTime=0
NamespaceDump:
Child Cursor: Heap0=0x3d9412a20 Heap6=0x36490fc90 Heap0 Load Time=03-17-2014 10:09:33 Heap6 Load Time=03-17-2014 10:09:33
Child: childNum='5'
LibraryHandle: Address=3e477970 Hash=0 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
Name: Namespace=SQL AREA(00) Type=CURSOR(00)
Statistics: InvalidationCount=0 ExecutionCount=112 LoadCount=1 ActiveLocks=0 TotalLockCount=104 TotalPinCount=115
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=0 HandleInUse=0
Concurrency: DependencyMutex=3e477a20(0, 0, 0, 0) Mutex=3f296188(647, 7157, 0, 6)
Flags=RON/PIN/PN0/EXP/[10010110]
WaitersLists:
Lock=3e477a00[3e477a00,3e477a00]
Pin=3e477a10[3e4779e0,3e4779e0]
ObjectFreed=last freed from HPD addn data CBK
NamespaceDump:
Child Cursor: Heap0=0xa99d540 Heap6=0x43e477970 Heap0 Load Time=20-70--5947 254:-1:-1 Heap6 Load Time=00-00--100-100 -1:-1:-1
NamespaceDump:
Parent Cursor: sql_id=9svnzn8h7ac59 parent=0x3db0db500 maxchild=6 plk=y ppn=n
Current Cursor Sharing Diagnostics Nodes:
Child Node: 1 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0
already processed:
Child Node: 1 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4
invalidation window(kglobitm): 1394899703
ksugctm(): 1394939892
Child Node: 5 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0
already processed:
Child Node: 5 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4
invalidation window(kglobitm): 1394501245
ksugctm(): 1394501311
Child Node: 3 ID=40 reason=Bind mismatch(33) size=2x4
init ranges in first pass: 1
selectivity: 0
Child Node: 0 ID=40 reason=Bind mismatch(25) size=0x0
extended cursor sharing:
Child Node: 2 ID=40 reason=Bind mismatch(25) size=0x0
extended cursor sharing:
Aged Out Cursor Sharing Diagnostic Nodes:
Child Node: 4 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4
invalidation window(kglobitm): 1394417045
ksugctm(): 1394417200
Child Node: 4 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0
already processed:
Child Node: 3 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4
invalidation window(kglobitm): 1394769934
ksugctm(): 1394772759
Child Node: 3 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0
already processed:
Child Node: 0 ID=40 reason=Bind mismatch(25) size=0x0
extended cursor sharing:
Child Node: 2 ID=40 reason=Bind mismatch(25) size=0x0
extended cursor sharing:
Child Node: 1 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4
invalidation window(kglobitm): 1394645200
ksugctm(): 1394669548
Child Node: 1 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0
already processed:
Child Node: 0 ID=40 reason=Bind mismatch(25) size=0x0
extended cursor sharing:
Child Node: 3 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4
invalidation window(kglobitm): 1394249253
ksugctm(): 1394249311
Child Node: 3 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0
already processed:
Child Node: 2 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4
invalidation window(kglobitm): 1394590329
ksugctm(): 1394590342
Child Node: 2 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0
already processed:
Child Node: 1 ID=40 reason=Bind mismatch(25) size=0x0
extended cursor sharing:
Child Node: 0 ID=40 reason=Bind mismatch(25) size=0x0
extended cursor sharing:
Child Node: 2 ID=40 reason=Bind mismatch(25) size=0x0
extended cursor sharing:
Child Node: 1 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4
invalidation window(kglobitm): 1394334468
ksugctm(): 1394348237
Child Node: 1 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0
already processed:
Child Node: 0 ID=40 reason=Bind mismatch(25) size=0x0
extended cursor sharing:
Child Node: 2 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4
invalidation window(kglobitm): 1394167585
ksugctm(): 1394167981
Child Node: 2 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0
already processed:
Child Node: 1 ID=40 reason=Bind mismatch(25) size=0x0
extended cursor sharing:
Child Node: 0 ID=40 reason=Bind mismatch(25) size=0x0
extended cursor sharing:
Child Node: 2 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0
already processed:
Child Node: 1 ID=40 reason=Bind mismatch(25) size=0x0
extended cursor sharing:
Child Node: 0 ID=40 reason=Bind mismatch(25) size=0x0
extended cursor sharing:
Child Node: 2 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4
invalidation window(kglobitm): 1393907782
ksugctm(): 1393910621
Child Node: 2 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0
already processed:
Child Node: 1 ID=40 reason=Bind mismatch(25) size=0x0
extended cursor sharing:
Child Node: 0 ID=40 reason=Bind mismatch(25) size=0x0
extended cursor sharing: kkscs=0x3db0dba08 nxt=0x3db0dbed8 flg=1b cld=0 hd=0x43ce65f10 par=0x3db0db500
Mutex 0x3db0dba08(0, 0) idn 3000000000
ct=1e hsh=0 unp=(nil) unn=0 hvl=a271fbd0 nhv=0 ses=(nil)
hep=0x3db0dba98 flg=80 ld=0 ob=(nil) ptr=(nil) fex=(nil)
kkscs=0x3db0dbed8 nxt=0x3dbe58810 flg=18 cld=1 hd=0x43f11fe28 par=0x3db0db500
Mutex 0x3db0dbed8(0, 0) idn 0
ct=1d hsh=0 unp=(nil) unn=0 hvl=a271fbb0 nhv=0 ses=(nil)
hep=0x3db0dbf68 flg=80 ld=1 ob=0x3a27e8c58 ptr=0x3658a19c8 fex=0x3658a0d68
kkscs=0x3dbe58810 nxt=0x3dbe58b88 flg=1a cld=2 hd=0x43fab70a0 par=0x3db0db500
Mutex 0x3dbe58810(0, 0) idn 300000000
ct=1c hsh=0 unp=(nil) unn=0 hvl=dbe58fe8 nhv=0 ses=(nil)
hep=0x3dbe588a0 flg=80 ld=1 ob=0x3a095ca00 ptr=0x3646999c8 fex=0x364698d68
kkscs=0x3dbe58b88 nxt=0x3a271ec40 flg=14 cld=3 hd=0x43ebec730 par=0x3db0db500
Mutex 0x3dbe58b88(0, 0) idn 300000000
ct=20 hsh=0 unp=(nil) unn=0 hvl=db0dc3b0 nhv=1 ses=0x448859c30
hsv[0]=0
hep=0x3dbe58c18 flg=80 ld=1 ob=0x3db4a1060 ptr=0x37aa71590 fex=0x37aa70930
kkscs=0x3a271ec40 nxt=0x3a271ef90 flg=18 cld=4 hd=0x43e7ef458 par=0x3db0db500
Mutex 0x3a271ec40(0, 0) idn 300000000
ct=21 hsh=0 unp=(nil) unn=0 hvl=db0dc2f8 nhv=0 ses=(nil)
hep=0x3a271ecd0 flg=80 ld=1 ob=0x3d9412938 ptr=0x36490fc90 fex=0x36490f030
kkscs=0x3a271ef90 nxt=(nil) flg=19 cld=5 hd=0x43e477970 par=0x3db0db500
Mutex 0x3a271ef90(0, 0) idn 300000000
ct=13 hsh=0 unp=(nil) unn=0 hvl=db0dc2d8 nhv=0 ses=(nil)
hep=0x3a271f020 flg=80 ld=0 ob=(nil) ptr=(nil) fex=(nil)
cursor instantiation=0x2b28a614b380 used=1395022173 exec_id=16778281 exec=1
child#4(0x43e7ef458) pcs=0x3a271ec40
clk=0x43a967e08 ci=0x3d9412a20 pn=0x43ba9a118 ctx=0x36490fc90
kgsccflg=0 llk[0x2b28a614b388,0x2b28a614b388] idx=0
xscflg=c0110676 fl2=5d000008 fl3=42222008 fl4=180
sharing failure(s)=800000040000
----- Bind Info (kkscoacd) -----
Bind#0
oacdty=01 mxl=128(64) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=852 siz=1536 off=0
kxsbbbfp=2b28a60537f0 bln=128 avl=00 flg=05
Bind#1
oacdty=01 mxl=128(64) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=852 siz=0 off=128
kxsbbbfp=2b28a6053870 bln=128 avl=00 flg=01
Bind#2
oacdty=01 mxl=128(64) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=852 siz=0 off=256
kxsbbbfp=2b28a60538f0 bln=128 avl=00 flg=01
Bind#3
oacdty=01 mxl=128(64) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=852 siz=0 off=384
kxsbbbfp=2b28a6053970 bln=128 avl=00 flg=01
Bind#4
oacdty=01 mxl=128(64) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=852 siz=0 off=512
kxsbbbfp=2b28a60539f0 bln=128 avl=00 flg=01
Bind#5
oacdty=01 mxl=128(64) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=852 siz=0 off=640
kxsbbbfp=2b28a6053a70 bln=128 avl=00 flg=01
Bind#6
oacdty=01 mxl=128(64) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=852 siz=0 off=768
kxsbbbfp=2b28a6053af0 bln=128 avl=00 flg=01
Bind#7
oacdty=01 mxl=128(64) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=852 siz=0 off=896
kxsbbbfp=2b28a6053b70 bln=128 avl=00 flg=01
Bind#8
oacdty=01 mxl=128(64) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=852 siz=0 off=1024
kxsbbbfp=2b28a6053bf0 bln=128 avl=00 flg=01
Bind#9
oacdty=01 mxl=128(64) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=852 siz=0 off=1152
kxsbbbfp=2b28a6053c70 bln=128 avl=00 flg=01
Bind#10
oacdty=01 mxl=128(64) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=852 siz=0 off=1280
kxsbbbfp=2b28a6053cf0 bln=128 avl=00 flg=01
Bind#11
oacdty=01 mxl=128(64) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=852 siz=0 off=1408
kxsbbbfp=2b28a6053d70 bln=128 avl=00 flg=01
Frames pfr 0x2b28a5b09500 siz=10080 efr 0x2b28a5b09440 siz=10064
kxscphp=0x2b28a5f0df68 siz=984 inu=552 nps=328
kxscbhp=0x2b28a5f7e5f8 siz=2544 inu=2032 nps=1560
kxscwhp=0x2b28a5f2e508 siz=4056 inu=56 nps=0
Starting SQL statement dump
SQL Information
user_id=85 user_name=EKPJ module=JDBC Thin Client action=
sql_id=9svnzn8h7ac59 plan_hash_value=-892795830 problem_type=4
----- Current SQL Statement for this session (sql_id=9svnzn8h7ac59) -----
select joecostpay0_.fd_id as col_0_0_ from ekpj.joe_cost_payment_tem joecostpay0_ left outer join ekpj.joe_payment_tem_areader authallrea1_ on joecostpay0_.fd_id=authallrea1_.fd_doc_id left outer join ekpj.sys_org_element sysorgelem2_ on authallrea1_.fd_org_id=sysorgelem2_.fd_id where joecostpay0_.fd_parent_id=:1 and (joecostpay0_.auth_reader_flag=1 or sysorgelem2_.fd_id in (:2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 ))
sql_text_length=448
sql=select joecostpay0_.fd_id as col_0_0_ from ekpj.joe_cost_payment_tem joecostpay0_ left outer join ekpj.joe_payment_tem_areader authallrea1_ on joecostpay0_.fd_id=authallrea1_.fd_doc_id left outer join ekpj.sys_org_element sysorgelem2_ on authallrea1_.fd_or
sql=g_id=sysorgelem2_.fd_id where joecostpay0_.fd_parent_id=:1 and (joecostpay0_.auth_reader_flag=1 or sysorgelem2_.fd_id in (:2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 ))
Compilation Environment Dump
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = true
parallel_query_forced_dop = 0
parallel_dml_forced_dop = 0
parallel_ddl_forced_degree = 0
parallel_ddl_forced_instances = 0
_query_rewrite_fudge = 90
optimizer_features_enable = 11.1.0.7
_optimizer_search_limit = 5
cpu_count = 16
active_instance_count = 1
parallel_threads_per_cpu = 2
hash_area_size = 131072
bitmap_merge_area_size = 1048576
sort_area_size = 65536
sort_area_retained_size = 0
_sort_elimination_cost_ratio = 0
_optimizer_block_size = 8192
_sort_multiblock_read_count = 2
_hash_multiblock_io_count = 0
_db_file_optimizer_read_count = 8
_optimizer_max_permutations = 2000
pga_aggregate_target = 5701632 KB
_pga_max_size = 1140320 KB
_query_rewrite_maxdisjunct = 257
_smm_auto_min_io_size = 56 KB
_smm_auto_max_io_size = 248 KB
_smm_min_size = 1024 KB
_smm_max_size = 570160 KB
_smm_px_max_size = 2850816 KB
_cpu_to_io = 0
_optimizer_undo_cost_change = 11.1.0.7
parallel_query_mode = enabled
parallel_dml_mode = disabled
parallel_ddl_mode = enabled
optimizer_mode = all_rows
sqlstat_enabled = false
_optimizer_percent_parallel = 101
_always_anti_join = choose
_always_semi_join = choose
_optimizer_mode_force = true
_partition_view_enabled = true
_always_star_transformation = false
_query_rewrite_or_error = false
_hash_join_enabled = true
cursor_sharing = exact
_b_tree_bitmap_plans = true
star_transformation_enabled = false
_optimizer_cost_model = choose
_new_sort_cost_estimate = true
_complex_view_merging = true
_unnest_subquery = true
_eliminate_common_subexpr = true
_pred_move_around = true
_convert_set_to_join = false
_push_join_predicate = true
_push_join_union_view = true
_fast_full_scan_enabled = true
_optim_enhance_nnull_detection = true
_parallel_broadcast_enabled = true
_px_broadcast_fudge_factor = 100
_ordered_nested_loop = true
_no_or_expansion = false
optimizer_index_cost_adj = 100
optimizer_index_caching = 0
_system_index_caching = 0
_disable_datalayer_sampling = false
query_rewrite_enabled = true
query_rewrite_integrity = enforced
_query_cost_rewrite = true
_query_rewrite_2 = true
_query_rewrite_1 = true
_query_rewrite_expression = true
_query_rewrite_jgmigrate = true
_query_rewrite_fpc = true
_query_rewrite_drj = true
_full_pwise_join_enabled = true
_partial_pwise_join_enabled = true
_left_nested_loops_random = true
_improved_row_length_enabled = true
_index_join_enabled = true
_enable_type_dep_selectivity = true
_improved_outerjoin_card = true
_optimizer_adjust_for_nulls = true
_optimizer_degree = 0
_use_column_stats_for_function = true
_subquery_pruning_enabled = true
_subquery_pruning_mv_enabled = false
_or_expand_nvl_predicate = true
_like_with_bind_as_equality = false
_table_scan_cost_plus_one = true
_cost_equality_semi_join = true
_default_non_equality_sel_check = true
_new_initial_join_orders = true
_oneside_colstat_for_equijoins = true
_optim_peek_user_binds = true
_minimal_stats_aggregation = true
_force_temptables_for_gsets = false
workarea_size_policy = auto
_smm_auto_cost_enabled = true
_gs_anti_semi_join_allowed = true
_optim_new_default_join_sel = true
optimizer_dynamic_sampling = 2
_pre_rewrite_push_pred = true
_optimizer_new_join_card_computation = true
_union_rewrite_for_gs = yes_gset_mvs
_generalized_pruning_enabled = true
_optim_adjust_for_part_skews = true
_force_datefold_trunc = false
statistics_level = typical
_optimizer_system_stats_usage = true
skip_unusable_indexes = true
_remove_aggr_subquery = true
_optimizer_push_down_distinct = 0
_dml_monitoring_enabled = true
_optimizer_undo_changes = false
_predicate_elimination_enabled = true
_nested_loop_fudge = 100
_project_view_columns = true
_local_communication_costing_enabled = true
_local_communication_ratio = 50
_query_rewrite_vop_cleanup = true
_slave_mapping_enabled = true
_optimizer_cost_based_transformation = linear
_optimizer_mjc_enabled = true
_right_outer_hash_enable = true
_spr_push_pred_refspr = true
_optimizer_cache_stats = false
_optimizer_cbqt_factor = 50
_optimizer_squ_bottomup = true
_fic_area_size = 131072
_optimizer_skip_scan_enabled = true
_optimizer_cost_filter_pred = false
_optimizer_sortmerge_join_enabled = true
_optimizer_join_sel_sanity_check = true
_mmv_query_rewrite_enabled = true
_bt_mmv_query_rewrite_enabled = true
_add_stale_mv_to_dependency_list = true
_distinct_view_unnesting = false
_optimizer_dim_subq_join_sel = true
_optimizer_disable_strans_sanity_checks = 0
_optimizer_compute_index_stats = true
_push_join_union_view2 = true
_optimizer_ignore_hints = false
_optimizer_random_plan = 0
_query_rewrite_setopgrw_enable = true
_optimizer_correct_sq_selectivity = true
_disable_function_based_index = false
_optimizer_join_order_control = 3
_optimizer_cartesian_enabled = true
_optimizer_starplan_enabled = true
_extended_pruning_enabled = true
_optimizer_push_pred_cost_based = true
_optimizer_null_aware_antijoin = true
_optimizer_extend_jppd_view_types = true
_sql_model_unfold_forloops = run_time
_enable_dml_lock_escalation = false
_bloom_filter_enabled = true
_update_bji_ipdml_enabled = 0
_optimizer_extended_cursor_sharing = udo
_dm_max_shared_pool_pct = 1
_optimizer_cost_hjsmj_multimatch = true
_optimizer_transitivity_retain = true
_px_pwg_enabled = true
optimizer_secure_view_merging = true
_optimizer_join_elimination_enabled = true
flashback_table_rpi = non_fbt
_optimizer_cbqt_no_size_restriction = true
_optimizer_enhanced_filter_push = true
_optimizer_filter_pred_pullup = true
_rowsrc_trace_level = 0
_simple_view_merging = true
_optimizer_rownum_pred_based_fkr = true
_optimizer_better_inlist_costing = all
_optimizer_self_induced_cache_cost = false
_optimizer_min_cache_blocks = 10
_optimizer_or_expansion = depth
_optimizer_order_by_elimination_enabled = true
_optimizer_outer_to_anti_enabled = true
_selfjoin_mv_duplicates = true
_dimension_skip_null = true
_force_rewrite_enable = false
_optimizer_star_tran_in_with_clause = true
_optimizer_complex_pred_selectivity = true
_optimizer_connect_by_cost_based = true
_gby_hash_aggregation_enabled = true
_globalindex_pnum_filter_enabled = true
_px_minus_intersect = true
_fix_control_key = 0
_force_slave_mapping_intra_part_loads = false
_force_tmp_segment_loads = false
_query_mmvrewrite_maxpreds = 10
_query_mmvrewrite_maxintervals = 5
_query_mmvrewrite_maxinlists = 5
_query_mmvrewrite_maxdmaps = 10
_query_mmvrewrite_maxcmaps = 20
_query_mmvrewrite_maxregperm = 512
_query_mmvrewrite_maxmergedcmaps = 50
_query_mmvrewrite_maxqryinlistvals = 500
_disable_parallel_conventional_load = false
_trace_virtual_columns = false
_replace_virtual_columns = true
_virtual_column_overload_allowed = true
_kdt_buffering = true
_first_k_rows_dynamic_proration = true
_optimizer_sortmerge_join_inequality = true
_optimizer_aw_stats_enabled = true
_bloom_pruning_enabled = true
result_cache_mode = MANUAL
_px_ual_serial_input = true
_optimizer_skip_scan_guess = false
_enable_row_shipping = true
_row_shipping_threshold = 80
_row_shipping_explain = false
transaction_isolation_level = read_commited
_optimizer_distinct_elimination = true
_optimizer_multi_level_push_pred = true
_optimizer_group_by_placement = true
_optimizer_rownum_bind_default = 10
_enable_query_rewrite_on_remote_objs = true
_optimizer_extended_cursor_sharing_rel = simple
_optimizer_adaptive_cursor_sharing = true
_direct_path_insert_features = 0
_optimizer_improve_selectivity = true
optimizer_use_pending_statistics = false
_optimizer_enable_density_improvements = true
_optimizer_aw_join_push_enabled = true
_optimizer_connect_by_combine_sw = true
_enable_pmo_ctas = 0
_optimizer_native_full_outer_join = force
_bloom_predicate_enabled = true
_optimizer_enable_extended_stats = true
_is_lock_table_for_ddl_wait_lock = 0
_pivot_implementation_method = choose
optimizer_capture_sql_plan_baselines = false
optimizer_use_sql_plan_baselines = true
_optimizer_star_trans_min_cost = 0
_optimizer_star_trans_min_ratio = 0
_with_subquery = OPTIMIZER
_optimizer_fkr_index_cost_bias = 10
_optimizer_use_subheap = true
parallel_degree_policy = manual
parallel_degree = 0
parallel_min_time_threshold = 10
_parallel_time_unit = 10
_optimizer_or_expansion_subheap = true
_optimizer_free_transformation_heap = true
_optimizer_reuse_cost_annotations = true
_result_cache_auto_size_threshold = 100
_result_cache_auto_time_threshold = 1000
_optimizer_nested_rollup_for_gset = 100
_nlj_batching_enabled = 1
parallel_query_default_dop = 0
is_recur_flags = 0
optimizer_use_invisible_indexes = false
flashback_data_archive_internal_cursor = 0
_optimizer_extended_stats_usage_control = 224
_parallel_syspls_obey_force = true
cell_offload_processing = true
_rdbms_internal_fplib_enabled = false
db_file_multiblock_read_count = 128
_bloom_folding_enabled = true
_mv_generalized_oj_refresh_opt = true
cell_offload_compaction = ADAPTIVE
parallel_degree_limit = 65535
parallel_force_local = false
parallel_max_degree = 32
total_cpu_count = 16
cell_offload_plan_display = AUTO
_optimizer_coalesce_subqueries = false
_optimizer_fast_pred_transitivity = false
_optimizer_fast_access_pred_analysis = false
_optimizer_unnest_disjunctive_subq = false
_optimizer_unnest_corr_set_subq = false
_optimizer_distinct_agg_transform = false
_aggregation_optimization_settings = 32
_optimizer_connect_by_elim_dups = false
_optimizer_eliminate_filtering_join = false
_connect_by_use_union_all = old_plan_mode
dst_upgrade_insert_conv = true
advanced_queuing_internal_cursor = 0
_optimizer_unnest_all_subqueries = true
_bloom_predicate_pushdown_to_storage = true
_bloom_vector_elements = 0
_bloom_pushing_max = 524288
parallel_autodop = 0
parallel_ddldml = 0
_parallel_cluster_cache_policy = adaptive
_parallel_scalability = 50
iot_internal_cursor = 0
_optimizer_instance_count = 0
_optimizer_connect_by_cb_whr_only = false
_suppress_scn_chk_for_cqn = nosuppress_1466
_optimizer_join_factorization = false
_optimizer_use_cbqt_star_transformation = false
_optimizer_table_expansion = false
_and_pruning_enabled = false
_deferred_constant_folding_mode = DEFAULT
_optimizer_distinct_placement = false
partition_pruning_internal_cursor = 0
parallel_hinted = none
_sql_compatibility = 0
_optimizer_use_feedback = false
_optimizer_try_st_before_jppd = false
====================== END SQL Statement Dump ======================
ttcdrvdmplocation: msg-32 ln-1004 reporting 12333
Dumping 'Buffer dump info:' addr=0x1e66bda0 size=713 bytes
Dump of memory from 0x1e66bda0 to 0x1e66c06c
01E66BDA0 02005E03 01002980 01BF0102 00000D01 [.^...)..........]
01E66BDB0 FFFFFF04 040A01FF FFFFFF7F 000C0101 [................]
01E66BDC0 00000000 00000000 00000001 40FE0000 [...............@]
01E66BDD0 656C6573 6A207463 6F63656F 61707473 [select joecostpa]
01E66BDE0 2E5F3079 695F6466 73612064 6C6F6320 [y0_.fd_id as col]
01E66BDF0 305F305F 7266205F 65206D6F 2E6A706B [_0_0_ from ekpj.]
01E66BE00 5F656F6A 74736F63 7961705F 746E656D [joe_cost_payment]
01E66BE10 65745F40 6F6A206D 736F6365 79617074 [@_tem joecostpay]
01E66BE20 6C205F30 20746665 6574756F 6F6A2072 [0_ left outer jo]
01E66BE30 65206E69 2E6A706B 5F656F6A 6D796170 [in ekpj.joe_paym]
01E66BE40 5F746E65 5F6D6574 61657261 20726564 [ent_tem_areader ]
01E66BE50 74754061 6C6C6168 31616572 6E6F205F [a@uthallrea1_ on]
01E66BE60 656F6A20 74736F63 30796170 64662E5F [ joecostpay0_.fd]
01E66BE70 3D64695F 68747561 726C6C61 5F316165 [_id=authallrea1_]
问题分析：
通过Metalink上的提示，得到造成该错误的可能原因：
1、部分版本的JDBC驱动会引起该错误：
Bug 9445675 NO MORE DATA TO READ FROM SOCKET WHEN USING END-TO-END METRICS
This bug does affect the JDBC driver. This bug may be the cause when all of the following conditions are met:
This bug is fixed in the 11.2.0.2 version of the JDBC driver and above. It is discussed in the following notes:
Note 9445675.8 Bug 9445675 - "No more data" / ORA-3137 using end to end metrics with JDBC Thin
Note 1081275.1 "java.sql.SQLRecoverableException: No more data to read from socket" is Thrown When End-to-end Metrics is Used
2、数据库自身的BUG也会导致该错误：
Unpublished Bug:9703463 - ORA-3137 [12333] or ORA-600 [kpobav-1] When Using Bind Peeking
This bug affects versions 11.1.0.6, 11.1.0.7, and 11.2.0.1 of the RDBMS. It is fixed in version 11.2.0.2 of the database.
It can also occur intermittently; similarly to unpublished Bug:8625762, this is a bind peeking bug.
==================================================
Unpublished Bug 9373370 DATA BASE RETURNS WRONG CURSORID WHEN THERE IS AN ORA-01013
This bug affects the 10.2, 11.1, 11.2.0.1, and 11.2.0.2 databases. It is discussed in the following note:
Note 9373370.8 Bug 9373370 - The wrong cursor may be executed by JDBC thin following a query timeout / ORA-3137 [12333]
While the bug primarily manifests in ORA-1006 or ORA-1008 errors, the problem may also result in ORA-600 [12333] or ORA-3137 [12333] errors appearing on the server side.
解决方法：
初步判断当前的错误由于jdbc导致的，刚好公司还有11.2.0.3的数据库，因此进入到目录/u01/app/oracle/product/OraDb11g_home1/jdbc/lib，然后下载ojdbc6.jar，更新即可；
-------------------------------------------------------------------------------------------------
IT技术员的理想发展曲线：技术员――技术管理――运营管理――企业战略
个人理念：不做单纯的技术，技术就做管理的技术
ORACLE技术管理QQ群：367875324
------------------------------------------------------------------------------------------------
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
