Re: db file sequential reads

Hi Rodney,

> I am getting a lot of waits on my db file sequential reads and I kinda understand that is
> indicative of reading the indexes of the tables, but my question is, is it an indication that
> there are too many or ineffective indexing going on in the tables? Or can I increase my
> buffer cache to offset the wait state from the db file sequential reads?

Strange as it may seem, 'db file sequential reads' indicates random access and 'db file scattered read' indicates FTS, so your understanding is right. The question of whether there is too much or ineffective indexing depends on whether the response time is acceptable and within limits, and whether it is even throughout. Increasing the buffer cache may help only in cases where the tables/index blocks being accessed can be cached and other FTS/access requirements.

OTOH, what you *can* do is to look at the following:

(a) Set TIMED_STATISTICS=TRUE (and bounce the DB) if it is not set. This is essential to understanding the amount of waits. (b) Track stats of most accessed Datafiles from V$FILESTAT and determine which disks they come from. If the WAIT periods are within the 'normal' range for those type of disks, you are probably Ok. (c) If not, use your SA's help to look at the contending disks (or you can use 'iostat' to do the same) and look at spreading out your I/O.

To drill down into details of inter-process clashing or waits for hot-spot blocks you could probably use V$SESSION_WAIT. Look at P1, P2 and P3 values - these should indicate the file#, block# and blocks.

Hope this helps, Received on Thu Oct 12 2000 - 14:02:23 CDT