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=9svnzn8h6ac59 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=9svnzn8h6ac59 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=9svnzn8h6ac59 plan_hash_value=-892795830 problem_type=4

----- Current SQL Statement for this session (sql_id=9svnzn8h6ac59) -----

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:

  1. 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
  2. You are using end-to-end metrics in your Java code
  3. 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,更新即可;ORACLE ORA-03137错误处理

 

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

IT技术员的理想发展曲线:技术员――技术管理――运营管理――企业战略

个人理念:不做单纯的技术,技术就做管理的技术

ORACLE技术管理QQ群:367875324

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

上一篇:js的动态加载、缓存、更新以及复用(二)


下一篇:Flutter文本标签TextTagWidget,搜索记录流式布局显示文本标签