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 high waits

RE:db_file_scattered_read high waits

From: Steve Rospo <srospo_at_watchmark.com>
Date: Tue, 16 Nov 2004 08:23:52 -0800 (PST)
Message-ID: <Pine.GSO.4.44.0411160820400.2884-100000@sapphire.wa.watchmark.com>

In addition to what Lex has below (especially the index suggestion), I'd also make sure that that the extent size is a multiple of the multiblock read count. Yes, it's ok to have a couple hundred extents but not if they're not an exact multiple of the multiblock read stride.

S-

On Tue, 16 Nov 2004, Natural Join B.V. wrote:

> Paula,
>
> the performance of full table scans is mainly influenced by the high-water mark
> in the table. in 10g, you can use "alter table ... shrink" to move the rows and
> push the HWM back; in 9i you can use "alter table ... move" to rebuild the
> table, but it has some drawbacks. but if you are retrieving only one row, an
> index might help if the full table scan is too expensive?
>
> Cheers,
> Lex.
>
> > Okay,
> >
> > There have been a lot of deletes/inserts in an environment resulting in
> > a high db_file_scattered_read wait I believe on a small table when doing
> > a full table scan and retrieving a specific row. Basically to retrieve
> > one row from this table I had to scan 377 blocks according to the trace
> > each of 16K. =20
> >
> > So I have some questions:
> >
> > -I am using LMT with uniform extents and have a few 100 extents - my
> > understanding is that the extent sizing shouldn't play a part in this
> > issue.
> >
> > -I believe the large amount of deletes/inserts have. How to minimize
> > the deletes/inserts performance impact? At this point it appears that I
> > would have to rebuild the table. However, in the future there will be
> > additional deletes/inserts and I shouldn't have to keep rebuilding the
> > table to ensure good performance. =20
> >
> > -I am using automatic segment management.
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 16 2004 - 10:58:33 CST

Original text of this message

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