9i solution to override application generated hints? Options

[HamDBA]

With Oracle 9i, is there an easy way to disable application generated hints? We have a very old vendor app that is generating bad hints and basically we want to prevent all of those hints from being used – the query execution time is very good without them. Strangely enough, there doesn’t seem to be a way of stopping the hints from the application end. With 9i, would stored outlines be my best bet for tackling this problem?

[dave]

might work, try it

[HAL9000]

"We have a very old vendor app that is generating bad hints and basically we want to prevent all of those hints from being used"

In 9i, yes, optimizer plan stability is the proper answer, works great.

You can change any SQL execution plan, hit or not.

http://dba-oracle.com/googlesearchsite_pro..._catalog.html#0

See here for working examples, great way to get it right fast:

http://www.rampant-books.com/ebook_vendor_tune.htm

[HamDBA]

I think I answered my own question. I created a stored outline for the application SQL that had the vendor imbedded hints (bad plan). I then created a stored outline for the same SQL, but removed all of the vendor imbedded hints (good plan). In the two stored outlines (bad vs good) there was a difference in one of the hints. Changing 'USE_NL' to 'USE_HASH' made the bad plan execute very well. Anyway, hope this helps someone else down the road....

[burleson]

Hi Ham,

>> Changing 'USE_NL' to 'USE_HASH' made the bad plan execute very well.

Beware, the propensity of the optimizer to do a hash join is controlled by your settings for the PGA RAM areas (hash_area_size, pga_aggregate_target):

http://www.dba-oracle.com/tips_oracle_hash_joins.htm