Today I want to discuss some additional ways to hint SQL.
Oracle10 introduced the OPT_PARAM hint. This hint allows you to set various INIT.ora settings at the SQL level! Isn’t that cool! No longer do you have to make changes to parameters with possible far-reaching implications just to fix a hand full of SQL.
This is the hint: OPT_PARAM( <option> );
These are the options currently supported:
There is a specific Dynamic Sampling hint that I’ve used before for specific SQL as well.
Another useful hint for those applications that have migrated to a newer version of Oracle but some of the SQL struggle but work fine in the older releases. The Optimizer_Features_Enable(<oracle version>) hint allows you to specify an optimizer version for this particular SQL. This is also an init.ora setting but one of those that might have far-reaching implications. This hint allows you to apply the older CBO rules to particular problem SQL.
A caveat with this feature though is that if the improvement that isn’t working happens to be a bug fix, Oracle doesn’t roll back the bug fixes using this technique. In fact, I do not know of a way to NOT apply bug fixes in newer releases when emulating an older release.
Something you might try when testing is to disable all the hints in any SQL altogether. I do strongly believe that the CBO is improving all the time and that some, if not most of the hints used in the past to direct features to the SQL are no longer necessary. In fact…I have often said that hints disable the future…if some new feature/newer stats/etc can be used by a particular feature but the SQL is full of hints…that SQL will not be able to take advantage of the changes in structures/stats/etc…because they are loaded down with these hinted directives.
Try this: alter session set “_optimizer_ignore_hints” = true; …then run your test. See how the performance is. See what changes. Perhaps you can strip out all the hints and just start over.
So test test test!
I wanted to share some additional cool hinting options.
Dan HotkaOracle ACE DirectorInstructor/Author/CEO
>Dynamic Sampling hint that I’ve used before for specific SQL as well
I would really like to hear more about this part. I read a lot and still have no clear standing.