--_=_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 AvgMin
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.
620s0.1% 4 0.037230s 0.034101s
0.044109s 5:681746
630s0.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
...INDEXRANGE 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
HTMLHEAD
META HTTP-EQUIV=3DContent-Type CONTENT=3Dtext/html; charset=3DUS-ASCII
META content=3DMSHTML 6.00.2800.1106 name=3DGENERATOR/HEAD
BODY
DIVSPAN class=3D968031416-01122003FONT face=3DArial color=3D#ff si=
ze=3D2you=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 thingnbsp; 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=
tonbsp; something like 22 hours !!!)/FONT/SPAN/DIV
DIVSPAN class=3D968031416-01122003FONT face=3DArial color=3D#ff=20
size=3D2/FONT/SPANnbsp;/DIV
DIVSPAN class=3D968031416-01122003FONT face=3DArial color=3D#ff=20
size=3D2HTH/FONT/SPAN/DIV
DIVSPAN class=3D968031416-01122003FONT face=3DArial color=3D#ff=20
size=3D2/FONT/SPANnbsp;/DIV
DIVSPAN class=3D968031416-01122003FONT face=3DArial color=3D#ff=20
size=3D2Lee/FONT/SPAN/DIV
BLOCKQUOTE dir=3Dltr style=3DMARGIN-RIGHT: 0px
DIV class=3DOutlookMessageHeader dir=3Dltr align=3DleftFONT face=3DTah=
oma=20
size=3D2-Original Message-BRBFrom:/B [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]BOn Behalf Of /BBiddell, IanBRBSen=
t:/B=20
01 December 2003 10:34BRBTo:/B Multiple recipients of list=20
ORACLE-LBRBSubject:/B Oracle 9i physical IO - why only one block=20
readsBRBR/FONT/DIV
DIV dir=3Dltr align=3DleftSPAN class=3D435140910-01122003FONT face=
=3DArial=20
color=3D#ff size=3D2Hi All,/FONT/SPAN/DIV
DIV dir=3Dltr align=3DleftSPAN class=3D435140910-01122003FONT face=
=3DArial=20
color=3D#ff
