Why is my Query Slow?
You can easily examine where your query is spending time by enabling autotracing, and reviewing the results
Set Autotrace ON
Now Execute your Query (lets say it is a DELETE Query), and look at the results.
Your results will look something like this:
Plan hash value: 2130017389
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | DELETE STATEMENT | | 49 | 2291 | 41 (0)| 00:00:01 |
| 1 | DELETE | WHATEVER_TABLENAME | | | | |
|* 2 | INDEX RANGE SCAN| WHATEVER_INDEX_IDX | 49 | 2291 | 3 (0)| 00:00:01 |
Predicate Information (identified by operation id):
2 – access(“ORDER_ID”=34438309)
3 user calls
0 physical read total multi block requests
3914500 physical read total bytes
3827200 cell physical IO interconnect bytes
0 commit cleanout failures: block lost
0 IMU commits
1 IMU Flushes
0 IMU contention
0 IMU bind flushes
0 IMU mbu flush
So, from the above you can deduce the following:
- Are indexes being used
- Are there lot of Physical Reads
If there are frequent inserts/deletes on large tables, it is possible that Table and Index data has got fragmented, this can result in Slow Queries (often Delete Queries are slow because of this).
alter table TABLE_NAME move
alter index INDEX_NAME rebuild;
If you de-frag table, always remember, you must rebuild ALL indexes on that table.
TODO: More Info on this…
Analyze Table <TABLE_NAME> Compute Statistics;