Index Range Scans are single block i/o (fast full scans use multiblock i/o). This is why you are seeing the high number of single block reads. If you want to use multiblock reads, use full table scans or fast full index scans. Be warned, response time could drop dramatically (along with your employee rating and salary...).
The real question is "Does this have any negative impact on response time for the businesses most important processes?"
Daniel Fink
"Biddell, Ian" wrote:
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 onlyEven 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:61755I 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:705852Explain plan of staementSELECT 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 680Thanks for any direction to the answer I can be given, just so I can understand more about what's happeningThanks :-)
