It's doing that because it ISN'T reading a sequential set of blocks. In my example (taken from your original list) block 14,709 is presumably already in the buffer, so Oracle has to read up to it, (which takes three blocks), skip it, and then start again at the block after.
Arguably it might be a better strategy for Oracle to allocate an 8-block multiblock object and always read 8 blocks into, then 'free' any that are already in the buffer. But it doesn't - and there may or may not be a very sensible argument why not, it might simply be an accident of history. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) ____UK_______April 8th ____UK_______April 22nd ____Denmark May 21-23rd ____USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____UK_(Manchester)_May ____USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 14 March 2003 21:04 > Thanks Jonathen , > But what I am pointing to is , why it is reading 3 blocks first time and > then 8 blocks next time , why not 8 blocks always . > Look at p3 which is no of block read . > > > > > > > Here is a part of trace file . I am finding that oracle is trying to > > read 8 or 3 or 7 blocks at a time . But block numbers are all > > sequential i.e. it will read 3 blocks starting from 14706 and then 8 > > blocks starting from 14710 ( 14706+3 ). Why it doesn't read 8 blcoks > > always it multi_block_read is set to 8 ? Any Idea . > > Also what is ela=1 ,does it mean elapsed time is 1 sentisec ? > > > > > > > > WAIT #32: nam='db file scattered read' ela= 0 p1=4 p2=14706 p3=3 > > WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=14710 p3=8 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
