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 ?
Code:
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
Plan
SELECT STATEMENT ALL_ROWS Cost: 1 Bytes: 555 Cardinality: 3
8 FILTER
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