Re: What are "db file sequential read"s?

David LeJeune <dlejeune_at_yahoo.com> wrote:
>
>After more analysis it seems that the bottleneck (from looking at
>v$session, v$sqlarea and v$session_wait) is the PRIMARY KEY INDEX for
>the USER_MEMBERS table.
>
>Any ideas what could be causing the problem?

In answer to your question, "db file sequential read" is the event a session waits on while the database is reading data from disk. This information is in the Oracle 8i Reference manual in Appendix A.

You should be aware that v$session_wait only tells you the *last* event a session waited on, not necessarily what it's currently waiting for.

You've gone about query tuning in a somewhat roundabout manner. You should first run an EXPLAIN PLAN on the query. The execution plan may be such that you're doing far more I/O than you really need. As the query contains a full reference to the PK in the WHERE clause conditions, it should be doing an index lookup only be getting a single row by ROWID.

If you do have a sensible execution plan, then possibly the instance has too few block buffers, and is doing excessive amounts of I/O, or the I/O subsystem is slow?

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/