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

"David LeJeune" <dlejeune_at_yahoo.com> wrote in message news:f329f8a7.0111130922.549d1bb_at_posting.google.com...
> Hi -
>
> I have a really frustrating query. It's very simple but it goes
> against a big table (more than 1million recs). (PK is USER_ID +
> MEMBER_ID)
>
> SELECT * FROM USER_MEMBERS
> WHERE USER_ID = :b1 AND
> MEMBER_ID = :b2 AND
> USER_TYPE_CD = 'C';
>
> When I check my user processes, it seems that they are being hung on a
> "db file sequential read". By hung I mean that our average
> transactions per second drops considerably. Testing w/ simple instert
> statements we are able to do more than 5000 inserts per second.
> Combining it with this query seems to only produce on the order of 20
> per second.
>
> I investigated what was the bottle neck... here are my results:
>
> select ses.username||' ('||sw.sid||') --> '|| event
> from v$session ses, v$session_wait sw
> where ses.sid = sw.sid and ses.username is not null;
>
> PRODUCES
>
> MYUSR (11) --> SQL*Net message from client
> SYS (17) --> SQL*Net message from client
> MYUSR (24) --> db file sequential read
>
> 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?
>
> Thanks in advance --- anyone that has an idea, I'm open,
> =-Dave L.

Dave:

db file sequential read -- wait event for foreground process that is doing a sequential read from one of the database files. Usually this is a single block table access as a result of getting a rowid from an index read.

So.... you are inserting 5000 rows per second into a table (which of course updates the pk/index) and selecting * from the same table at the same time. Are the datafiles on physically separate devices? (I assume the index is not in the same tablespace as the data). How long are the waits? Is there a single 'commit' at the end of the insert? Are the logfiles on separate devices? Have you run a statspack report and checked the wait events section?

HTH,

• LM

It is not surprising to see Received on Tue Nov 13 2001 - 19:21:11 CST