Home » RDBMS Server » Performance Tuning » How to push Hash Anti join without HINT or code modification ?
How to push Hash Anti join without HINT or code modification ? [message #217929] Tue, 06 February 2007 02:30 Go to next message
deadpoet69
Messages: 3
Registered: August 2006
Junior Member
Dears,

I'm facing a problem.

(Oracle 9.2.0.7)

An execution plan shows that a query want to do a Nested Anti Join. By placing an Hint "/*+ hash_aj */, it runs much faster.

But .. there's a good but ...

1- We are not the owner of the code, so we can't modify it or add an Hint.

2- We don't want to put the "always_anti_join"'s parameter to "HASH" because , we just want to tune 1 query and not all the database's queries.

3- The objects used for this query are synonyms of views. I don't know how to handle stats on that.

4- They are no bind variable in this SQL. All changing parameters pass as hard coded. So how can I use OUTLINES ?


So the question is , how can I tell Oracle to use an Hash Anti Join instead of the Nested Anti join ?
I'm puzzled ...

Many thanks to anyone who has spent little time , first for reading this.


Re: How to push Hash Anti join without HINT or code modification ? [message #217955 is a reply to message #217929] Tue, 06 February 2007 04:05 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi.

1. You don't have to deal with stats on synonyms/view. Try changing stats of the base table(s) to enforce HASH_AJ.

2. You can try using cursor_sharing = FORCE to tell Oracle about changing all hard coded values to parameters internally.
Afterward you can try outlines.

HTH.
Michael
Previous Topic: Help Required in Tunning
Next Topic: SQL Statement Tuning
Goto Forum:
  


Current Time: Tue Dec 06 10:22:51 CST 2016

Total time taken to generate the page: 0.10710 seconds