RE: db_file_scattered_read high waits

If you got your one row using .... and rownum < 2, then it should stop scanning when it gets the one row, regardless of HWM.

So if you're doing that, you've got a large empty front. Unless new rows are going to be filling that space up (which presumes something about the order of your freelist(s), then you might have 376 empty blocks at the front.

Some software suites do a lot of scanning for one row to get a sample row for a join or whatever. Without getting into whether that is good or bad design, and especially if you can't change the code, "empty" front conditions are sometimes worth rebuilding to get rid of. Usually it matters more if it's a really small table with a lot of empty blocks where someone figured a scan would always be a good idea. Again, I'm not endorsing those design ideas, but I do encounter them.

Now if you can get at the row via index as Lex suggested, that is clearly much better (and it stays fixed.)

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Natural Join B.V. Sent: Tuesday, November 16, 2004 7:19 AM To: Paula_Stankus_at_doh.state.fl.us; oracle-l_at_freelists.org Subject: RE:db_file_scattered_read high waits

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