RE:db_file_scattered_read high waits
- From: Steve Rospo <srospo@xxxxxxxxxxxxx>
- To: "Natural Join B.V." <lex.de.haan@xxxxxxxxxxxxxx>
- Date: Tue, 16 Nov 2004 08:23:52 -0800 (PST)
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.
> > --
> > //www.freelists.org/webpage/oracle-l
> >
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
