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.

Is that index VALID?
How long does it take to query one specific USER_ID/MEMBER_ID row?

Raymond. Received on Tue Nov 13 2001 - 14:24:22 CST