Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: db file scattered read

Re: db file scattered read

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 14 Mar 2003 13:39:08 -0800
Message-ID: <F001.0056AD3B.20030314133908@fatcity.com>

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

> 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: jonathan_at_jlcomp.demon.co.uk

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 Fri Mar 14 2003 - 15:39:08 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US