topsql problem

[yxez]

Hi,

I recently bothered by our apps running slow while accessing a certain table
CPCLASS. I tried to get the topsql resource users and i got the following
information:

SQL_TEXT DISK_READS = 2879986
----------
SELECT COP_CARE_OF_CLASS_CODE, COP_CARE_SUB_CLASS,
COP_CARE_OF_PARTICULARS, COP_PARTICULARS_DESC ,rowid
FROM CPCLASS WHERE
COP_CARE_OF_CLASS_CODE = :1
AND COP_CARE_SUB_CLASS = :2 AND
COP_CARE_OF_PARTICULARS = :3
ORDER BY COP_CARE_OF_CLASS_CODE ASC ,
COP_CARE_SUB_CLASS ASC,
COP_CARE_OF_PARTICULARS ASC

This table has only 6,000+ rows in it and all the columns that were used
in the "WHERE" conditions are indexed. What possibly cause this very high
reosource usage DISK_READS? I tried to simulate/run this program on
sqlplus and it works fine.

Any ideas pls....thanks

[davey23uk]

whos to say whether that number is bad, maybe that is since startup 3 months ago or something?

Take a statspack report with 15 minute intervals during the slowest time - see what you are waiting on and hat sql comes out top then

[yxez]

Thanks,
We just bounced our 7.1.3 database last night....is stastpak available in V7?

[yxez]

The users said sometimes the program is ok sometimes its not

[davey23uk]

no statspack wasnt in version7, i was still in shorts when version 7 was out.

You need to use utlbstat / utlestat