We have a new application in a fairly empty database and there have been complaints of slow performance. Looking at the statspack report for those time windows, this is what I see as the most expensive query. But the explain plan for that SQL looks good. When I run this SQL directly on the database, it comes back under 2 msecs. So where else should I look ?
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
38.39 12 3.20 59.0 320.31 14,569,520 3959463376
Module: JDBC Thin Client
select level, seq_order, standard_id, title, internal_grade_leve
ls as grade from standard where standard_type
_id = :1 and state_code = :2 connect by prior
standard_id = parent_id start with parent_id = :3
SELECT STATEMENT ALL_ROWS Cost: 1 Bytes: 555 Cardinality: 3
7 CONNECT BY WITH FILTERING
2 TABLE ACCESS BY INDEX ROWID TABLE SBO.STANDARD Cost: 1 Bytes: 555 Cardinality: 3
1 INDEX RANGE SCAN INDEX SBO.STANDARD_PARENTID_IX Cost: 1 Cardinality: 3
6 NESTED LOOPS
3 CONNECT BY PUMP
5 TABLE ACCESS BY INDEX ROWID TABLE SBO.STANDARD Cost: 1 Bytes: 555 Cardinality: 3
4 INDEX RANGE SCAN INDEX SBO.STANDARD_PARENTID_IX Cost: 1 Cardinality: 3
I understand some user is complaining about bad performance -that's a surprise, users never complain about performance LOL
Okay... you found a query in statspack.
Question, is that the particular query user is complaining about?
Can you define good performance for the affected query? do you have a baseline? what's the SLA? Did you compare SLA vs. actual response time?
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Click Here to Expand Forum to Full Width