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: Natural Join B.V. <lex.de.haan_at_naturaljoin.nl>
Date: Tue, 16 Nov 2004 12:18:39 MET
Message-Id: <20041116112046.A25243A5D1A@ha-smtp2.tiscali.nl>


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
Received on Tue Nov 16 2004 - 05:16:29 CST

Original text of this message

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