This discussion is archived
9 Replies Latest reply: Oct 8, 2013 4:55 AM by Jan-Marten Spit RSS

high consistent reads for LOB insert

Jan-Marten Spit Explorer
Currently Being Moderated

Hello all,

 

Oracle 11.2.0.3, table with in-row LOB, 40% of rows stored out-of-row. Three indexes, on ID, USERKEY and CASEID, the latter two child of a foreign key constraint.

 

Application contineously does insert followed by an update on the same row (it's stupid but a given and consequence of using hibernate).

 

In DBA_HIST and 10046 traces i frequently see the insert requiring several thousands of consistent read gets, in the trace excerpt below 3140cr, 36cu, but also 1cr, 22cu.

 

the insert would cause oracle to enforce the FK's ofcourse, but both parent columns are indexed.

 

i cannot explain the source of cr gets. can it have something to do with the RETENTION of the LOB segment (4 hours). And if so, why does it require consistent gets? who will enlighten me?

 

thanks, Jan-Marten Spit

 

PARSING IN CURSOR #140247543831072 len=116 dep=0 uid=76 oct=2 lid=76 tim=1380548509250682 hv=2771882159 ad='4cf569500' sqlid='779p2n6kmg45g'

insert into CMFEVENT (EVENTDATA, DATEOCCURRED, EVENTTYPE, USERKEY, CASEID, ID) values (:1 , :2 , :3 , :4 , :5 , :6 )

END OF STMT

PARSE #140247543831072:c=0,e=472,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1380548509250681

BINDS #140247543831072:

Bind#0

  oacdty=01 mxl=4001(4108) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000010 frm=01 csi=873 siz=4000 off=0

  kxsbbbfp=7f8ded0bac48  bln=4000  avl=4001  flg=25

  value="^P241^K355215^?"...

Bind#1

  oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=09 pre=00

  oacflg=03 fl2=9000000 frm=00 csi=00 siz=2088 off=0

  kxsbbbfp=7f8ded07c9b0  bln=11  avl=11  flg=05

  value=30-SEP-13 03.41.49.199000000 PM

Bind#2

  oacdty=01 mxl=2000(496) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=16

  kxsbbbfp=7f8ded07c9c0  bln=2000  avl=124  flg=01

  value="/IMP-Inkomensregister/3000_Inkomensregister/3200_Gebeurtenistypes/3220_IR09_Registreren_Inkomengegevens/PS11 inplannen.bixml"

Bind#3

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=2016

  kxsbbbfp=7f8ded07d190  bln=22  avl=02  flg=01

  value=1

Bind#4

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=2040

  kxsbbbfp=7f8ded07d1a8  bln=22  avl=06  flg=01

  value=580429124

Bind#5

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=2064

  kxsbbbfp=7f8ded07d1c0  bln=22  avl=06  flg=01

  value=597880089

EXEC #140247543831072:c=0,e=1274,p=0,cr=1,cu=22,mis=1,r=1,dep=0,og=1,plh=0,tim=1380548509252004

STAT #140247543831072 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=170 us)'

WAIT #140247543831072: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1380548509252062

WAIT #140247543831072: nam='SQL*Net message from client' ela= 222 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1380548509252298

CLOSE #140247543831072:c=0,e=6,dep=0,type=0,tim=1380548509252337

 

PARSING IN CURSOR #140247544075264 len=116 dep=0 uid=76 oct=2 lid=76 tim=1380548510409225 hv=2771882159 ad='4cf569500' sqlid='779p2n6kmg45g'

insert into CMFEVENT (EVENTDATA, DATEOCCURRED, EVENTTYPE, USERKEY, CASEID, ID) values (:1 , :2 , :3 , :4 , :5 , :6 )

END OF STMT

PARSE #140247544075264:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1380548510409225

BINDS #140247544075264:

Bind#0

  oacdty=01 mxl=4001(20724) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000010 frm=01 csi=873 siz=4000 off=0

  kxsbbbfp=7f8ded07c650  bln=4000  avl=4001  flg=25

  value="270304355354215^?"...

Bind#1

  oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=09 pre=00

  oacflg=03 fl2=9000000 frm=00 csi=00 siz=2088 off=0

  kxsbbbfp=7f8ded0b6848  bln=11  avl=11  flg=05

  value=30-SEP-13 03.41.50.360000000 PM

Bind#2

  oacdty=01 mxl=2000(496) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=16

  kxsbbbfp=7f8ded0b6858  bln=2000  avl=124  flg=01

  value="/IMP-Inkomensregister/3000_Inkomensregister/3200_Gebeurtenistypes/3220_IR09_Registreren_Inkomengegevens/PS06 inplannen.bixml"

Bind#3

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=2016

  kxsbbbfp=7f8ded0b7028  bln=22  avl=02  flg=01

  value=1

Bind#4

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=2040

  kxsbbbfp=7f8ded0b7040  bln=22  avl=06  flg=01

  value=577524323

Bind#5

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000000 frm=01 csi=873 siz=0 off=2064

  kxsbbbfp=7f8ded0b7058  bln=22  avl=06  flg=01

  value=597880138

EXEC #140247544075264:c=8001,e=7824,p=0,cr=3140,cu=36,mis=0,r=1,dep=0,og=1,plh=0,tim=1380548510417087

STAT #140247544075264 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=3140 pr=0 pw=0 time=7655 us)'

WAIT #140247544075264: nam='SQL*Net message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1380548510417173

WAIT #140247544075264: nam='SQL*Net message from client' ela= 812 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1380548510418003

CLOSE #140247544075264:c=0,e=5,dep=0,type=0,tim=1380548510418041

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points