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
Though I haven't investigated how the SQL Baselines and the evolve option are incorporated in OEM 11g.
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
Kyle,
ReplyDeleteA couple of thoughts about the problems STA has with this specific example.
It's using the first_rows hint, not the first_rows(n) hint - and first_rows exists only for backwards compatibility and shouldn't really be used in any version of Oracle from 9i onwards. This MAY have contributed to the unsuitable choice of profile.
I think your suggestion about optimising for all rows may be correct - which is why it might be interesting to see (a) if the "unprofiled" path would have been better if the original hint had been first_rows(1) and (b) if the STA does a different (?better) job if the hint were first_rows(1).
If the STA is only working towards all rows optimisation then running the before and after test could still leave the STA suggesting the profile because it would probably select ALL the data in both cases, and its strategy for all the data may be better than using the first_rows to get all the data.
Is the query one that is logically designed to return one "sensible" row or is it effectively picking one row at random from a result set that matches some given conditions. If the latter then it is a bit of a special case as far as optimisation is concerned -- "any row will do" isn't a common optimisation requirement.
One problem I have with it, does it take into account the affect of caching between run 1 and run n? You may see a significant improvement between two runs simply because one is cached and one is not.
ReplyDelete