Have you used Oracle’s SQL Tuning Pack? What were your experiences like?
At my last consulting gig 2 out 3 statements I “tuned” with the SQL tuning pack actually ran significantly more slowly after applying the profiles recommended. I thought it was just my bad karma and luck of the draw until recently. Recently I’ve started asking other DBAs what their experiences were and generally finding less than enthusiastic responses. In the classes on Oracle 10g and OEM, I’ve always cautioned that profiles should not be blindly trusted and give an example using a Swingbench query where the SQL runs more slowly after the profile.If applying the profile, I recommend to jump immediately to the SQL performance page and to verify the effect of the profile on the load of the query.
Above is a page from OEM after the SQL Tuning Advisor has been run, a profile is suggested, and now I can implement the profile.
After the profile has been implemented, I’d recommend clicking on the SQL_ID to see go to the SQL details page and see what the new execution plan looks like on the load chart
In the above SQL details we can see the new execution plan hash 467377872 and that it’s load is now higher. The number of executions by the application, swingbench remained the same, but the load by the SQL statement with the new profile is higher. At this point I’d click on “Tuning Information” and back out the profile.
I thought the above example was a fluke along with the couple of bad experiences I had at a customer site but after asking a number of other DBAs who have used the SQL Tuning Advisor and who have responded less than enthusiastically, I’m now curious to poll a wider audience.
A couple of thoughts:
One, I think the reason the STA’s profile created more load is because the application, swingbench, actually only uses one row from the query result set and ignores the rest of the result set. I’m guessing that the STA tuned the query for the entire result set.
Two, it seems to be a serious flaw that the STA doesn’t actually run the query with the new profile to compare the before and after. At least this should be offered as an option.
It seems that Oracle 11 will address some of this issues in a round about way with SQL baselines and the evolve. See http://www.oracle.com/technology/oramag/oracle/09-mar/o29spm.html
From the above link, for example
Baseline Plan Test Plan Improv. Ratio
-------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 0 0
Elapsed Time(ms): 5036 1033 4.88
CPU Time(ms): 254 700 .36
Buffer Gets: 1728 43945 .04
Disk Reads: 254 22 11.55
Direct Writes: 0 0
Fetches: 49 22 2.23
Executions: 1 1
Though I haven’t investigated how the SQL Baselines and the evolve option are incorporated in OEM 11g.
Comments