Oracle tuning stories - 1

Like any DBA I have a warchest of old tuning stories. I tend to use them to illustrate a point to a developer who can't see the reason for changing the way the program runs.

The easy low hanging fruit in tuning is finding the dodgy SQL which is not using a index. The performance increase with proper indexing can be truly amazing.

Given the large variety of index types and options available in Oracle, there will be an index for any tuning occasion. You have got to keep in mind though what the application is trying to achieve overall. Grabbing every row individually via an index, when you are going to read the whole table anyway should be dealt with differently then a complex or simple SQL join which randoms picks the rows it wants (adhoc user and search type queries).

I was talking to a developer about the log aggregation process which had failed and was taking a long time to catch up, about 8-10 hours for each days worth of weblogs. The application was in twlight zone, one of the applications and databases where the reason for it continued existence is questionable i.e. as long as it is stable and up, not much time is allocated to stressing over it.

A quick check of the logs produced nailed down the offending SQL.

select ... from t1, t2
where to_char(t1.username) = to_char(t2.username)

This was taking the 8-10 hours to run.

I checked the statistics, and added a function based index to t1.username
create functional index fx_t1_username
on t1(to_char(username))
tablespace ...

We rerun and it now takes.... 1 minute.

Now instead of the database being the slow portion of the whole process it is the perl scripts which aggregrate the weblogs. 1 hour per day. The catchup, instead of taking the rest of the month was finished in 12 hours.

Have Fun