Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> 9i solution to override application generated hints?
HamDBA
post Apr 21 2010, 11:33 AM
Post #1


Newbie
*

Group: Members
Posts: 6
Joined: 21-April 10
Member No.: 42,966



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?
Go to the top of the page
 
+Quote Post
dave
post Apr 21 2010, 12:19 PM
Post #2


Advanced Member
***

Group: Members
Posts: 5,207
Joined: 8-October 04
Member No.: 785



might work, try it
Go to the top of the page
 
+Quote Post
HAL9000
post Apr 22 2010, 01:55 PM
Post #3


Advanced Member
***

Group: Members
Posts: 884
Joined: 25-September 07
Member No.: 12,336



"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
Go to the top of the page
 
+Quote Post
HamDBA
post Apr 22 2010, 05:37 PM
Post #4


Newbie
*

Group: Members
Posts: 6
Joined: 21-April 10
Member No.: 42,966



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....
Go to the top of the page
 
+Quote Post
burleson
post Apr 25 2010, 09:09 AM
Post #5


Advanced Member
***

Group: Members
Posts: 11,730
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 27th November 2014 - 08:16 PM