What are "db file sequential read"s?

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. Received on Tue Nov 13 2001 - 11:22:04 CST