SQL tuning

[lee]

The foll. sql has a performance problem. How do I tune this?

SELECT a.dt_tm, b.data_blob from audit_event a, audit_data_blob b WHERE a.evnt_id = b.evnt_id and serv_typ_id =5399 and dt_tm > sysdate - 1 ORDER BY a.dt_tm

The explain plan is as follows:

Full table scan on audit_data_blob and
index(range scan) on audit_event_ndx_4(dt_tm)


Even though there is an index on evnt_id column in audit_data_blob , it does not seem to use it.

Why? Please let em know.

thanks.

[chrisrlong]

You neglected to add the table alias to all the columns.

Give us this info and maybe we can help

- Chris

[lee]

Hi,
Sorry for the delay. If there is no alias, does it make a difference? Please let me know.

Anyway, the sql is as follows:

SELECT a.dt_tm, b.data_blob from audit_event a, audit_data_blob b WHERE a.evnt_id = b.evnt_id and a.serv_typ_id =5399 and a.dt_tm > sysdate - 1 ORDER BY a.dt_tm

Thanks.

[chrisrlong]

If you eventually had added a dt_tm column to table AUDIT_DATA_BLOB, then this statement would suddenly have broken.

As a basic rule, always add a table name or alias prefix to every column in every multi-table statement.

Now, as to why I asked for them - without knowing where the columns came from, how would I possibly have guessed what the statement was truly doing? My mind-reading powers seem to have been on the blink recently

Okay, so now that I have at least some clue of what the statement is doing....

It does indeed seem odd that the statement will not use an index on that column. Reasons for this might include:

- The table is rather small and a full scan will be more efficient
- There are actually few distinct values for that column in the table and every value has many records, meaning that a significant percentage of the table will be read (anything over 5%?)
- The record size for B is very short (remember that a LOB is just a pointer). Therefore, *lots* of records fit in a given block. This will make the optimizer more likely to choose a table scan for the table because it will be very efficient.

So those are the thoughts off the top of my head.

Any of those sound like they might be the cause?

- Chris

[lee]

The total no of records in audit_data_blob are: 2172614

select count(distinct evnt_id) from audit_data_blob results in
2171268 records.

So, the table is huge.

What seems to be the problem?

[chrisrlong]

How about the last one? Is the record size really small?

Another thought - do you have current statistics on that table?

Can you post the exact output of the explain plan for the statement? Can you then add a hint to force the index and give us that plan as well?

- Chris

[lee]

sorry for the delay.
The exact explain plan is as follows:

select statement cost = 92552
2.1 sort (order by)
3.1 hash join
4.1 table access(by index rowid) - audit_event
5.1 index(range scan) - audit_event_ndx_4(non-unique)
4.2 table access(full) - audit_data_blob

total no of records in audit_data_blob = 2210359
total no of records in audit_event = 2209077

The stats on the tables are taken every week. This is a production sql.

Thanks.

[chrisrlong]

Again, have you tried hinting the index? Is it faster using the index?

Is the record size really small? - that is still my best bet

How many records does this query return?

- Chris

[lee]

The quety returns 105 records. IfI use the index hint on evnt_id, it's not faster. But, it should use the index, isn't it?

Thanks.

[lee]

I am so sorry. I used the wrong index. The query returns 105 records. If I use the index, it is faster, but buffer_gets in v$sqlarea is not below 2000.

Thanks.