AW: SQL Profile strange behavior
- From: Petr Novak <Petr.Novak@xxxxxxxxxxxx>
- To: "riyaj.shamsudeen@xxxxxxxxx" <riyaj.shamsudeen@xxxxxxxxx>, "oracledba.williams@xxxxxxxxx" <oracledba.williams@xxxxxxxxx>
- Date: Tue, 30 Nov 2010 08:00:37 +0000
Hallo Dennis,
what DB version it is ? Can you send the content of the SQLProfile ? How did
you create it ?
Best Regards,
Petr
________________________________________
Von: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx]" im
Auftrag von "Riyaj Shamsudeen [riyaj.shamsudeen@xxxxxxxxx]
Gesendet: Dienstag, 30. November 2010 00:07
Bis: oracledba.williams@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx; rshamsud@xxxxxxxxx
Betreff: Re: SQL Profile strange behavior
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<mailto: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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » AW: SQL Profile strange behavior - Petr Novak
