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 :-)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Biddell, Ian
INET: ian.biddell_at_hp.com
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: ListGuru_at_fatcity.com (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).
Received on Mon Dec 01 2003 - 04:34:26 CST
