------_=_NextPart_001_01C3B825.DE0C531A Content-Type: text/plain you may want to raise a call with Oracle support about this. We have an call open with them at present and they have raised a bug for us. We were noticing the same thing as you experienced on AIX 5L 9i RAC 9.2.0.3. Apparently Oracle is interpreting exectly the same statements that ran in minutes on 8i, differently to the way it reads them in 9i (where execution times went up to something like 22 hours !!!) HTH Lee
-----Original Message----- Biddell, Ian Sent: 01 December 2003 10:34 To: Multiple recipients of list ORACLE-L Hi All, I am running a big batch reporting program that does way too much SQL (but that's another story) and when I profile the trace file apart from one pice of SQL all the others are only doing 1 block reads. As you can see from the example below it did 63,209 physical IO calls and they were all for one block only Even though it's via Primary Key why doesn't Oracle get even more than one block per read if the index range scan is long? This is just an example from about 45 SQL statements that have the one block reads only. My multi block read count is 64 on an 8K blocksize, oracle 9.2 --------- Duration Per Call ----------- --- Detail of Max -- Blocks per Read Duration # Calls Avg Min Max Data Block Address ----------------- -------------- ---------- ----------- -------------- ------------ -------------------- 1 469s 100.0% 63,209 0.007423s 0.000544s 0.140338s 5:61755 I would have thought I would get more like one of the rare statements that gets 75% at 64 blocks per read. ie. 62 0s 0.1% 4 0.037230s 0.034101s 0.044109s 5:681746 63 0s 0.4% 18 0.039499s 0.034724s 0.062682s 9:706507 64 129s 75.8% 3,451 0.037441s 0.015427s 0.189772s 9:705852 Explain plan of staement SELECT STATEMENT Cost = 510 0-0-510 ...SORT AGGREGATE 1-0-1 .......TABLE ACCESS BY INDEX ROWID ALLOCATION_TRANSACTION_B 2-1-1 34 ...........INDEX RANGE SCAN ALLOCATION_TRANSACTION_PK 3-2-1 680 Thanks for any direction to the answer I can be given, just so I can understand more about what's happening Thanks :-) ********************************************************************** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. ------_=_NextPart_001_01C3B825.DE0C531A Content-Type: text/html Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=3DUS-ASCII"> <META content=3D"MSHTML 6.00.2800.1106" name=3DGENERATOR></HEAD> <BODY> <DIV><SPAN class=3D968031416-01122003><FONT face=3DArial color=3D#0000ff si= ze=3D2>you=20 may want to raise a call with Oracle support about this. We have an call op= en=20 with them at present and they have raised a bug for us. We were noticing th= e=20 same thing as you experienced on AIX 5L 9i RAC 9.2.0.3. Apparently Or= acle=20 is interpreting exectly the same statements that ran in minutes on 8i,=20 differently to the way it reads them in 9i (where execution times went up= to something like 22 hours !!!)</FONT></SPAN></DIV> <DIV><SPAN class=3D968031416-01122003><FONT face=3DArial color=3D#0000ff=20 size=3D2></FONT></SPAN> </DIV> <DIV><SPAN class=3D968031416-01122003><FONT face=3DArial color=3D#0000ff=20 size=3D2>HTH</FONT></SPAN></DIV> <DIV><SPAN class=3D968031416-01122003><FONT face=3DArial color=3D#0000ff=20 size=3D2></FONT></SPAN> </DIV> <DIV><SPAN class=3D968031416-01122003><FONT face=3DArial color=3D#0000ff=20 size=3D2>Lee</FONT></SPAN></DIV> <BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px"> <DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft><FONT face=3DTah= oma=20 size=3D2>-----Original Message-----<BR><B>From:</B> [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]<B>On Behalf Of </B>Biddell, Ian<BR><B>Sen= t:</B>=20 01 December 2003 10:34<BR><B>To:</B> Multiple recipients of list=20 ORACLE-L<BR><B>Subject:</B> Oracle 9i physical IO - why only one block=20 reads<BR><BR></FONT></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT face= =3DArial=20 color=3D#0000ff size=3D2>Hi All,</FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT face= =3DArial=20 color=3D#0000ff size=3D2></FONT></SPAN> </DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT face= =3DArial=20 color=3D#0000ff size=3D2>I am running a big batch reporting program that = does way=20 too much SQL (but that's another story)</FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT face= =3DArial=20 color=3D#0000ff size=3D2>and when I profile the trace file apart from one= pice of=20 SQL all the others are only doing 1 block reads.</FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT face= =3DArial=20 color=3D#0000ff size=3D2>As you can see from the example below it did 63,= 209=20 physical IO calls and they were all for one block only</FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT face= =3DArial=20 color=3D#0000ff size=3D2>Even though it's via Primary Key why doesn't Ora= cle get=20 even more than one block per read if the index range scan is=20 long?</FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT face= =3DArial=20 color=3D#0000ff size=3D2>This is just an example from about 45 SQL statem= ents that=20 have the one block reads only.</FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT face= =3DArial=20 color=3D#0000ff size=3D2>My multi block read count is 64 on an 8K blocksi= ze,=20 oracle 9.2</FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT face= =3DArial=20 color=3D#0000ff size=3D2></FONT></SPAN> </DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT face= =3DArial=20 color=3D#0000ff size=3D2></FONT></SPAN> </DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT face= =3DArial=20 color=3D#0000ff size=3D2></FONT></SPAN> </DIV> <DIV dir=3Dltr align=3Dleft><FONT face=3D"Courier New" size=3D1><FONT=20 size=3D2> &nbs= p; &= nbsp; &nbs= p; =20 --------- Duration Per Call ----------- --- Detail of Max --<BR>Blocks pe= r=20 Read =20 Duration #=20 Calls =20 Avg =20 Min Max = Data=20 Block Address<BR>----------------- -------------- ---------- -----------= -------------- ------------=20 --------------------<BR>1 = =20 469s 100.0% 63,209 =20 0.007423s 0.000544s =20 0.140338s &nbs= p; =20 5:61755</FONT><BR></FONT></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT face= =3DArial=20 color=3D#0000ff size=3D2>I would have thought I would get more like one o= f the=20 rare statements that gets 75% at 64 blocks per read.</FONT></SPAN></= DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT face= =3DArial=20 color=3D#0000ff size=3D2>ie.</FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT=20 face=3D"Courier New"=20 size=3D2>62 &n= bsp; =20 0s =20 0.1% 4 = 0.037230s 0.034101s =20 0.044109s =20 =20 5:681746<BR>63  = ; =20 0s =20 0.4% 18 =20 0.039499s 0.034724s =20 0.062682s &nbs= p; =20 9:706507<BR>64  = ; =20 129s 75.8% 3,451 &nb= sp;=20 0.037441s 0.015427s =20 0.189772s &nbs= p; =20 9:705852</FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT face= =3DArial=20 color=3D#0000ff size=3D2></FONT></SPAN> </DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003></SPAN> = ;</DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT face= =3DArial=20 color=3D#0000ff size=3D2>Explain plan of staement</FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT face= =3DArial=20 color=3D#0000ff size=3D2></FONT></SPAN> </DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT face= =3DArial=20 color=3D#0000ff size=3D2><FONT face=3D"Courier New">SELECT=20 STATEMENT &nbs= p; =20 Cost =3D=20 510 &nbs= p; =20 0-0-510 =20 <BR>....SORT &= nbsp; =20 AGGREGATE &nbs= p; &= nbsp; &nbs= p; =20 1-0-1 <BR>........TABLE ACCESS BY INDEX ROWID =20 ALLOCATION_TRANSACTION_B 2-1-1 34=20 <BR>............INDEX RANGE=20 SCAN =20 ALLOCATION_TRANSACTION_PK 3-2-1 680</FONT>= </FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT=20 face=3D"Courier New" color=3D#0000ff size=3D2></FONT></SPAN> </DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT=20 face=3D"Courier New" color=3D#0000ff size=3D2></FONT></SPAN> </DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT=20 face=3D"Courier New" color=3D#0000ff size=3D2>Thanks for any direction to= the answer=20 I can be given, just so I can understand more about what's=20 happening</FONT></SPAN></DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT=20 face=3D"Courier New" color=3D#0000ff size=3D2></FONT></SPAN> </DIV> <DIV dir=3Dltr align=3Dleft><SPAN class=3D435140910-01122003><FONT=20 face=3D"Courier New" color=3D#0000ff size=3D2>Thanks=20 :-)</FONT></SPAN></DIV></BLOCKQUOTE><FONT SIZE=3D3><BR> <BR> **********************************************************************<BR> The information contained in this communication is<BR> confidential, is intended only for the use of the recipient<BR> named above, and may be legally privileged.<BR> If the reader of this message is not the intended<BR> recipient, you are hereby notified that any dissemination, <BR> distribution, or copying of this communication is strictly<BR> prohibited.<BR> If you have received this communication in error,<BR> please re-send this communication to the sender and<BR> delete the original message or any copy of it from your<BR> computer system. Thank You.<BR> </FONT> </BODY></HTML> ------_=_NextPart_001_01C3B825.DE0C531A-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).