Re: SQL Profile strange behavior
- From: Dennis Williams <oracledba.williams@xxxxxxxxx>
- To: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
- Date: Wed, 1 Dec 2010 06:49:33 -0600
Thanks Kerry, Riyaj, Petr,
I appreciate your help. That gives me some insights to tackle this issue.
Dennis
On Wed, Dec 1, 2010 at 12:10 AM, Kerry Osborne <kerry.osborne@xxxxxxxxxxx>wrote:
> 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:
