Re: SQL Profile strange behavior
- From: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
- To: oracledba.williams@xxxxxxxxx
- Date: Wed, 1 Dec 2010 00:10:13 -0600
I think Riyaj has a good idea on why some of the hints may be throwing that
error. In the trace file there will be several lines that say:
atom_hint=...
It will show all hints and some diagnostic info. The err flag should be set to
0, any that are not set to 0 are the ones that may be suspect. I say may,
because I have seen cases where the flag is incorrect, but it's a place to
start.
With regard to SQL Profiles, I have written quite a bit about them on my blog.
Specifically you're asking about Profiles that have been generated by the SQL
Tuning Advisor (STA). It is actually common for those to work for a while and
then go south. The reason is that the Profiles generated by STA are all about
fixing statistics and optimizer calculations based on statistics. So as the
data changes over time, the Profiles become less and less effective. I just
posted a short blurb with a script (lock_STA_profile.sql) I've had hanging
around for a while. It replaces a STA generated SQL Profile (using the
OPT_ESTIMATE hints) with one using directive hints, which I believe have a much
lower probability of going bad. The post is here:
http://kerryosborne.oracle-guy.com/2010/11/how-to-lock-sql-profiles-generated-by-sql-tuning-advisor/
And yes I am sitting in a hotel room tonight with nothing better to do than
read oracle-l postings. ;)
Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com
On Nov 29, 2010, at 5:07 PM, Riyaj Shamsudeen wrote:
> Dennis
> I haven't seen this specific issue, few of my clients use profiles with no
> such issue.
>
> did you have problem with older init.ora file or after reverting back to
> new spfile? Remember that profiles is a set of hints(hinted completely
> though) and if you have set some initialization parameters disallowing the
> execution path, then I can see profiles not working as intended.
>
> For example, if the profile has index_ffs hint, but the init.ora parameter
> disallows index fast full scan with _fast_full_scan_enabled=false, then the
> profile will not work as intended.
>
> Do you mind sending last part of that 10053 trace file?
>
> Cheers
>
> Riyaj Shamsudeen
> Principal DBA,
> Ora!nternals - http://www.orainternals.com - Specialists in Performance,
> Recovery and EBS11i
> Blog: http://orainternals.wordpress.com
> OakTable member http://www.oaktable.com
> Co-author: "Expert Oracle practices: Oracle Database Administration from the
> Oak Table" http://www.apress.com/book/view/9781430226680
>
>
>
> On Wed, Nov 24, 2010 at 10:52 AM, Dennis Williams
> <oracledba.williams@xxxxxxxxx> wrote:
> List,
>
> We have a errant query and created a SQL Profile for it. This worked great
> but after a few days it seemed to not be effective. I confirmed that the
> query was still using the profile. Eventually I ran a 10053 trace and at the
> bottom was the line:
>
> ******* WARNING: SOME HINTS HAVE ERRORS ********
>
> We dropped the profile and behavior seemed to improve. Now the developers
> have less faith in profiles and are wanting to rely on OPTIMIZER_COST_ADJ and
> OPTIMIZER_INDEX_CACHING instead. This was pretty much the first time we'd
> given profiles a try, so our experience is profiles is limited.
>
> Has anyone had experience with profiles going bad?
>
> The only unusual thing that happened between profile creation and this
> incident was that we accidentally started the database with an old init.ora
> file. Once we figured out what happened, we restarted with the SPFILE.
>
> Any thoughts appreciated,
> Have a Happy Thanksgiving,
>
> Dennis Williams
>
Other related posts:
