I’m on call this week. Here are the steps that I took to speed up a query today.
First I got an AWR report and found the top query. Also, someone from support told me to look at November 11 before the latest release and I found a similar top query.
I got a plan for both the new and old top queries – they both use the same plan which makes me think that the two queries are similar.
I looked at the top segments on the AWR report and found a particular table at the top of the logical reads. An index of that table was like number 5 on that list.
I looked at the columns of the table’s index to see how many distinct values there were. None of the three columns had more than 300 distinct values so they were not very selective. I noticed that there was a unique index on the table and the first column of that index had millions of distinct values.
I extracted some sample bind variable values for the query and find that the second bind variable was null or something like that. But, the index we were using included this second variable.
In looked at the bind variables and found that the first column from the unique index was part of the join conditions in the query. (The query had like 20 joins).
Then I extracted the query text and replaced the bind variables with literals to see how it would run. It used the unique index. I used hints to force the original index and compared to running with the unique index. It ran about 30 times faster with the unique index. I ran a few times to make sure it was all cached.
Then I tried to use SQLT’s coe_xfr_sql_profile.sql to force the plan that used the unique index but got an error. Had to download the latest version of SQLT to get it to work.
Now, on average, the query seems to run about 1000 times faster.
It is a delivered vendor package so it was nice to find the better plan and go behind the scenes to fix it. But, if another release comes out and changes this sql to a new sql_id we will have to create a new profile. It’s not perfect but its a good quick fix for my on call.
Bobby
