Warning! While the details of the other points in this post remain valid, the main conclusion of the post (i.e. that the Tuning Advisor can create Profiles with hints other than the “statistics modification” type, opt_estimate for example) is not valid. At this point I have no proof that the Tuning Advisor creates Profiles with the more directive type hints. The conclusion was based on a faulty script (sql_profile_hints) which in certain situations combined hints from multiple Profiles. (The script has been modified now)

Be sure and see the comments from Jonathan and my response for further information. I will leave the remainder of the post unchanged for reference.

============================================================

This is the 2nd post in response to a dialog with Jonathan Lewis regarding SQL Profiles. Here’s a link to the 1st post and a link to the original dialog.

Just a quick recap: Jonathan had pointed out that I was using SQL Profiles in a manner that was not intended by the developers (with which I had to agree). SQL Profiles are created by the SQL Tuning Advisor (STA) primarily to address inaccurate calculations by the optimizer. The first post dealt with the question of whether SQL Profiles were capable of dealing with any/all hints or just the ones most commonly used by STA generated Profiles. This post focuses on a second question, namely whether STA generated Profiles ever had any thing other than the “statistics modification” type hints (opt_estimate, column_stats, table_stats, index_stats, etc…).

So in an effort to answer that question, I did a little looking around on some systems that had implemented a fair number of STA generated Profiles. In order to speed my “looking around”, I wrote a script to do a simple grouping of hints (sql_profile_distinct_hints.sql). sql_profile_hints.sql script if you want to follow along in your hymnal.

Also note that STA creates Profiles that are named with this format: SYS_SQLPROF_xxxxxxx, so we’ll only look at Profiles that are named in that fashion.

OPT_ESTIMATE was far and away the most popular hint. This is followed by the occasional XXX_STATS hints. The OPTIMIZER_FEATURES_ENABLE hint shows up in almost all the STA Profiles. Also, the IGNORE_OPTIM_EMBEDDED_HINTS shows up fairly regularly, but not always (not sure why it’s there sometimes and not others – I thought it might show up only when there are actually embedded hints in the SQL text, but that is not the case). Occasionally, an ALL_ROWS or FIRST_ROWS hint will show up. And finally, in rare cases, a full blown Profile with many directives will show up as in the one case I was able to reproduce. I really have no idea when/why this kicks in. But it doesn’t seem to happen very often. However, it does give a strong clue that most, if not all, valid hints can be applied by SQL Profiles.

And a couple of editorial points:

1. I don’t really like STA generated Profiles as a long term solution. Since they primarily apply fudge factors (via the opt_estimate hint) to the optimizer calculations (which are based in large part on statistics), they tend to go bad fairly quickly as the statistics change.

2. I think they are very useful though for finding where the optimizer has gone wrong. Anywhere there is an opt_estimate hint with a really large (or small) scaling factor, the optimizer had some kind of problem. This could be due to bad stats, or just a short coming in the optimizer itself. But it can be a very good pointer to why/where the optimizer is not doing a good job.

3. There is no reason you can’t use an STA Profile to get a decent plan and then do something else to make it more permanent – i.e. create an Outline or Baseline or even another Profile with all the hints necessary to reproduce the plan. This is of course if your intent is to “lock” the plan.

4. If you have a short coming in the optimizer (such as 10g’s lack of knowledge about correlated columns) then the opt_estimate hint in the STA Profiles may be a viable long term solution. Of course, this implies that you know enough about how the calculations are done to be sure that a certain calculation will always be off by a calculatable (did I just make that word up?) factor.

6 Comments

I agree with your points. I was recently horrified when a recent STA job had recommended a plan with about 20 cardinality hints. No way I would consider implementing that suggestion as it would quickly be wrong.

It did give me a direction to start looking and further analysis with GATHER_PLAN_STATISTICS showed me exactly where Estimated Rows and Actual Rows were getting hosed.

But sometimes a simple switch between ALL_ROWS and FIRST_ROWS will be something that is generic enough to feel safe implementing.

It’s the same transformed query block referencing the same alias twice with different scale factors. Again, that’s suspect; and it happens at many points in the hint set.

I suspect that you’ve managed to report two profiles as one – which doesn’t preclude the possibility that there is a faked set that holds the ‘outline’ type hints and your generated set is just ‘stats’ hints.

Excellent observation. I always just figured they didn’t do a very good job of programming, but now that I think about it a little, it seems unlikely that the programmers would not catch multiple all_rows hints in the same profile, much less multiple opt_estimates on the same operation (which I hadn’t noticed). I’ll have a look at that specific example and let you know what I find.

Well rats! I hate it when I am wrong! My script was combining hints for multiple Profiles (if there happened to be more than one Profile on the same statement, which is possible if you use multiple categories – a bit unusual but very possible). This was exactly the case in my test. Brilliant piece of deduction on your part by the way, without actually seeing the system – especially since I hacked up the hints to protect the table and column names. At any rate, I’m back to where I was before with a Profile of unknown origin that has a mixture of opt_estimate and other hints, but no proof that it was generated by the Tuning Advisor. It’s possible someone manually added hints or changed the name of the profile, although I can’t get anyone to fess up to that. So still a bit of a mystery to unravel there – another post I’m sure.

At any rate, I’ve updated my sql_profile_hints script to add the additional join (on category). Thanks for taking the time to look carefully at this post and comment on it.