SQL Profile strange behavior

  • From: Dennis Williams <oracledba.williams@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 24 Nov 2010 10:52:29 -0600

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: