Oracle Query Optimization Tips

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)

Statistics
———————————————————–
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

Table/Index Fragmentation

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).

De-frag Table:

alter table TABLE_NAME move

Rebuild Index:

alter index INDEX_NAME rebuild;

If you de-frag table, always remember, you must rebuild ALL indexes on that table.

Re-Compute Statistics

TODO: More Info on this…

Analyze Table <TABLE_NAME>  Compute Statistics;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: