ORACLE ORA-03137错误处理
问题背景：
今天公司的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:
- You are using the 10.1.x.x or the 11.2.0.1 JDBC driver; the bug does not affect 10.2.x.x, or 11.1.x.x versions of the driver, nor versions 11.2.0.2 or above
- You are using end-to-end metrics in your Java code
- The server side ORA-3137 [12333] error is accompanied by the client side Java exception "No more data to read from socket"
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
------------------------------------------------------------------------------------------------
