Oracle 9i physical IO - why only one block reads

[Biddell, Ian]

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 :-)