From what i understand from your statspack report.
global cache cr request and db file scattered read are contributing to your wait times.
I would suggest SQL tuning esp those that are on this table
Top sql's to tune would be
WHERE USERID = :B1
FROM USERPRIVILEGES, ENTITYPRIVILEGE
USERPRIVILEGES.USERENTITYID = :B1
USERPRIVILEGES.PRIVID = ENTITYPRIVILEGE.PRIVID
3)SELECT A.USERENTITYID, A.USERID, A.FIRSTNAME,
A.LASTNAME, A.EMAIL, A.ISDELETED,
B.PARENTID, B.ISUSERACTIVE, B.ISADMIN
FROM USERMAST A, USERENTITY B
WHERE A.USERENTITYID = B.USERENTITYID
AND LOGINID = :B2 AND PASSWORD = :B1
Hope you have set segment space management for the BIDRXDV1 tablespace to auto.
Another object where you would expect marginal performance improvement would be RESPONSE time.
06-11-2005, 07:16 AM
Thanks for your suggestions. Yes, I can see to from the Statspack report that USERENTITY table was a big trouble. I have created an index on USERID column without which it was causing lot of consistent gets(3915). After creating Index I have seen a drsatic improvement in the query. There are only 2 consistent gets.
BEFORE INDEX (COST=584, consistent gets=3915, Full Table Scan)
SQL> select count(userentityid)
from userentity where userid = 8;