Re: Oracle 9i physical IO - why only one block reads

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Mon, 01 Dec 2003 06:59:25 -0800
Message-ID: <F001.005D855D.20031201065925@fatcity.com>

Ian,

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 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: Daniel Fink
  INET: Daniel.Fink_at_Sun.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 - 08:59:25 CST