Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: A Tale of Two SQLs

RE: A Tale of Two SQLs

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Mon, 3 May 2004 16:22:12 -0400
Message-ID: <42BBD772AC30EA428B057864E203C99911400A@MSGBOSCLF2WIN.DMN1.FMR.COM> 





Could be confirmed by looking for sequential reads from RBS files.



Waleed



-----Original Message-----


From: Daniel Fink [mailto:Daniel.Fink_at_Sun.COM]
Sent: Monday, May 03, 2004 4:06 PM


To: oracle-l_at_freelists.org


Subject: A Tale of Two SQLs




It was the best of sql executions; it was the worst of sql
executions.



We have a process that periodically slows down. So far, we have
not been able to pinpoint a root cause, but we continue to
pester the development/application support folks for more info.
I apologize for the limited information, but I'm hoping that we
are aiming in the right direction.



We are smart enough that we captured a 10046 trace during a good
execution as a baseline. I have tagged the lines from this trace
file as (good). This morning, the query began slowing down
(eventually it will get a 1555 error). We turned on tracing and
those lines are tagged as (bad).



To make sure I am comparing apples to apples, the statements are
identical, even the bind variables are identical. I'm not going
to post the 2 trace files (almost 500m), so I'm including the
relevant parts. There are no waits in these execution/fetches
(you read it correctly, no db file waits, no sql*net waits).
I've also changed the statments to remove any table/column
names.



What I have found is that the first FETCH of the bad execution
is significantly (300 - 700x) slower than the good execution. It
also performs significantly more (500x) consistent reads.
Subsequent FETCHes are nearly identical to each other (in terms
of good =3D bad). These FETCHes are performed tens of thousands of
times as this query is re-executed with different bind variables
(also note the time difference in the EXECs, but it pales in
comparison).



My theory is that the high number of crs in the first FETCH of
each execution are the result of building a read-consistent
version of the data. Again, we are working with the dev/app
folks to confirm that one of these tables is being modified, but
have not had a response yet.=20



Comments on my theory?



'Tis a far, far better thing I do...



Daniel





Bad SQL


=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
PARSING IN CURSOR #10(bad) len=3D295 dep=3D1 uid=3D86 oct=3D3 lid=3D86
tim=3D746385804887 hv=3D3414111617 ad=3D'93b1bcf0'
SELECT t1.col1


from table1 t1, table2 t2


where t2.col2 =3D :b1 and t2.col2 =3D t1.col2
union


select max(t3.col3)


from table3 t3


where t3.col4 =3D substr(:b1, 1, 6) and t3.col3 < :b1
END OF STMT


Good SQL


=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
PARSING IN CURSOR #7(good) len=3D295 dep=3D1 uid=3D63 oct=3D3 lid=3D63
tim=3D26349629222266 hv=3D3414111617 ad=3D'8c9aee70'
SELECT t1.col1


from table1 t1, table2 t2


where t2.col2 =3D :b1 and t2.col2 =3D t1.col2
union


select max(t3.col3)


from table3 t3


where t3.col4 =3D substr(:b1, 1, 6) and t3.col3 < :b1
END OF STMT


EXEC



#10(bad):c=3D0,e=3D701,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D4,t=

im=3D746385804872


EXEC



#7(good):c=3D0,e=3D536,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D4,t=

im=3D26349629222259



FETCH



#10(bad):c=3D110000,e=3D107303,p=3D0,cr=3D7699,cu=3D0,mis=3D0,r=3D1,dep=3D=

1,og=3D4,tim=3D746


385912781


FETCH



#7(good):c=3D0,e=3D406,p=3D0,cr=3D16,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,=

tim=3D2634962922297


6



FETCH



#10(bad):c=3D0,e=3D30,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D4,ti=

m=3D746385913106


FETCH



#7(good):c=3D0,e=3D31,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D4,ti=

m=3D26349629223153



BINDS #10(bad):


 bind 0: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D96 offset=3D0


   bfp=3Dffffffff7c4b7c38 bln=3D32 avl=3D09 flg=3D05
   value=3D"104539-08"


 bind 1: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D0 offset=3D32


   bfp=3Dffffffff7c4b7c58 bln=3D32 avl=3D09 flg=3D01
   value=3D"104539-08"


 bind 2: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D0 offset=3D64


   bfp=3Dffffffff7c4b7c78 bln=3D32 avl=3D09 flg=3D01
   value=3D"104539-08"


BINDS #7(good):


 bind 0: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D96 offset=3D0


   bfp=3Dffffffff7c4aeac0 bln=3D32 avl=3D09 flg=3D05
   value=3D"104539-08"


 bind 1: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D0 offset=3D32


   bfp=3Dffffffff7c4aeae0 bln=3D32 avl=3D09 flg=3D01
   value=3D"104539-08"


 bind 2: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D0 offset=3D64


   bfp=3Dffffffff7c4aeb00 bln=3D32 avl=3D09 flg=3D01
   value=3D"104539-08"



EXEC



#10(bad):c=3D0,e=3D619,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D4,t=

im=3D746385914651


EXEC



#7(good):c=3D0,e=3D496,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D4,t=

im=3D26349629224220



FETCH



#10(bad):c=3D110000,e=3D102469,p=3D0,cr=3D7698,cu=3D0,mis=3D0,r=3D1,dep=3D=

1,og=3D4,tim=3D746


386017309


FETCH



#7(good):c=3D0,e=3D206,p=3D0,cr=3D16,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,=

tim=3D2634962922449


9



FETCH



#10(bad):c=3D0,e=3D12,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,ti=

m=3D746386017582


FETCH



#7(good):c=3D0,e=3D6,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=

=3D26349629224619



FETCH



#10(bad):c=3D0,e=3D8,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=

=3D746386017774


FETCH



#7(good):c=3D0,e=3D5,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=

=3D26349629224734



FETCH



#10(bad):c=3D0,e=3D9,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=

=3D746386017967


FETCH



#7(good):c=3D0,e=3D5,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=

=3D26349629224849



FETCH



#10(bad):c=3D0,e=3D8,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=

=3D746386018157


FETCH



#7(good):c=3D0,e=3D5,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=

=3D26349629224961



FETCH



#10(bad):c=3D0,e=3D26,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D4,ti=

m=3D746386018363


FETCH



#7(good):c=3D0,e=3D14,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D4,ti=

m=3D26349629225081



BINDS #10(bad):


 bind 0: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D96 offset=3D0


   bfp=3Dffffffff7c4b7c38 bln=3D32 avl=3D09 flg=3D05
   value=3D"104539-07"


 bind 1: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D0 offset=3D32


   bfp=3Dffffffff7c4b7c58 bln=3D32 avl=3D09 flg=3D01
   value=3D"104539-07"


 bind 2: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D0 offset=3D64


   bfp=3Dffffffff7c4b7c78 bln=3D32 avl=3D09 flg=3D01
   value=3D"104539-07"


BINDS #7(good):


 bind 0: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D96 offset=3D0


   bfp=3Dffffffff7c4aeac0 bln=3D32 avl=3D09 flg=3D05
   value=3D"104539-07"


 bind 1: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D0 offset=3D32


   bfp=3Dffffffff7c4aeae0 bln=3D32 avl=3D09 flg=3D01
   value=3D"104539-07"


 bind 2: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D0 offset=3D64


   bfp=3Dffffffff7c4aeb00 bln=3D32 avl=3D09 flg=3D01
   value=3D"104539-07"



EXEC



#10(bad):c=3D0,e=3D684,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D4,t=

im=3D746386019967


EXEC



#7(good):c=3D0,e=3D463,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D4,t=

im=3D26349629226116



FETCH



#10(bad):c=3D100000,e=3D101693,p=3D0,cr=3D7697,cu=3D0,mis=3D0,r=3D1,dep=3D=

1,og=3D4,tim=3D746


386121784


FETCH



#7(good):c=3D0,e=3D186,p=3D0,cr=3D16,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,=

tim=3D2634962922637


4



FETCH



#10(bad):c=3D0,e=3D12,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,ti=

m=3D746386122056


FETCH



#7(good):c=3D0,e=3D6,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=

=3D26349629226492



FETCH



#10(bad):c=3D0,e=3D26,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D4,ti=

m=3D746386122263


FETCH



#7(good):c=3D0,e=3D14,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D4,ti=

m=3D26349629226615



BINDS #10(bad):


 bind 0: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D96 offset=3D0


   bfp=3Dffffffff7c4b7c38 bln=3D32 avl=3D09 flg=3D05
   value=3D"107278-01"


 bind 1: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D0 offset=3D32


   bfp=3Dffffffff7c4b7c58 bln=3D32 avl=3D09 flg=3D01
   value=3D"107278-01"


 bind 2: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D0 offset=3D64


   bfp=3Dffffffff7c4b7c78 bln=3D32 avl=3D09 flg=3D01
   value=3D"107278-01"


BINDS #7(good):


 bind 0: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D96 offset=3D0


   bfp=3Dffffffff7c4aeac0 bln=3D32 avl=3D09 flg=3D05
   value=3D"107278-01"


 bind 1: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D0 offset=3D32


   bfp=3Dffffffff7c4aeae0 bln=3D32 avl=3D09 flg=3D01
   value=3D"107278-01"


 bind 2: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D0 offset=3D64


   bfp=3Dffffffff7c4aeb00 bln=3D32 avl=3D09 flg=3D01
   value=3D"107278-01"



EXEC



#10(bad):c=3D0,e=3D686,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D4,t=

im=3D746386123823


EXEC



#7(good):c=3D0,e=3D466,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D4,t=

im=3D26349629227628



FETCH



#10(bad):c=3D200000,e=3D204295,p=3D0,cr=3D15358,cu=3D0,mis=3D0,r=3D1,dep=3D=

1,og=3D4,tim=3D74


6386328243


FETCH



#7(good):c=3D0,e=3D143,p=3D0,cr=3D7,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,t=

im=3D26349629227843



FETCH



#10(bad):c=3D0,e=3D37,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D4,ti=

m=3D746386328513


FETCH



#7(good):c=3D0,e=3D13,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D4,ti=

m=3D26349629227941



BINDS #10(bad):


 bind 0: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D96 offset=3D0


   bfp=3Dffffffff7c4b7c38 bln=3D32 avl=3D09 flg=3D05
   value=3D"107366-01"


 bind 1: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D0 offset=3D32


   bfp=3Dffffffff7c4b7c58 bln=3D32 avl=3D09 flg=3D01
   value=3D"107366-01"


 bind 2: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D0 offset=3D64


   bfp=3Dffffffff7c4b7c78 bln=3D32 avl=3D09 flg=3D01
   value=3D"107366-01"


BINDS #7(good):


 bind 0: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D96 offset=3D0


   bfp=3Dffffffff7c4aeac0 bln=3D32 avl=3D09 flg=3D05
   value=3D"107366-01"


 bind 1: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D0 offset=3D32


   bfp=3Dffffffff7c4aeae0 bln=3D32 avl=3D09 flg=3D01
   value=3D"107366-01"


 bind 2: dty=3D1 mxl=3D32(09) mal=3D00 scl=3D00 pre=3D00 oacflg=3D13
oacfl2=3D1 size=3D0 offset=3D64


   bfp=3Dffffffff7c4aeb00 bln=3D32 avl=3D09 flg=3D01
   value=3D"107366-01"



EXEC



#10(bad):c=3D0,e=3D706,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D4,t=

im=3D746386330128


EXEC



#7(good):c=3D10000,e=3D493,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D=

4,tim=3D2634962922


8936



FETCH



#10(bad):c=3D210000,e=3D208902,p=3D0,cr=3D15673,cu=3D0,mis=3D0,r=3D1,dep=3D=

1,og=3D4,tim=3D74


6386539152


FETCH



#7(good):c=3D0,e=3D121,p=3D0,cr=3D7,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,t=

im=3D26349629229130



FETCH



#10(bad):c=3D0,e=3D35,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D4,ti=

m=3D746386539431


FETCH



#7(good):c=3D0,e=3D12,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D1,og=3D4,ti=

m=3D26349629229225




Please see the official ORACLE-L FAQ: http://www.orafaq.com




To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


Received on Mon May 03 2004 - 15:22:07 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US