Oracle Database Discussions

Hi,
One of our customers reported huge performance problems and the DBAs I work with after some investigation asked them to set the obsolete parameter hashjoin_enabled to false and one more undocumented parameter optimizercost_model to 'io'. Apparantely it solved their system wide
problem & the customer was happy but I wasn't.

I was not satisfied with the fact that we disabled hash joins all together. There has to be a reason why Oracle made this parameter obsolete. If it is obsolete why use it? None of the DBAs had any satisfying answer. This lead me to pursue an answer myself. I have been trying all sorts of experiments to find an answer. Last 3 weeks have been spent in search of enlightenment but in vain :(.

I have failed to understand the rationale behind what improved the performance. Why were hash joins detrimental to the performance? And if they were why did not optimizer chose sort merge at the first place? Here are some facts.

1. Oracle version is 10.2.0.3.
2. The memory management is automatic and the aggregate PGA is set to 1.2G which seems to be Ok.
3. The Target SGA is 3.5G
3. When hash joins are disabled the optimizer does sort merges mostly.
4. With hash joins the LIO's are almost 20-30 times more. The LIO for the queries is pretty high.
1M consistent gets with hash joins disabled, 20M o/w.

5. The statistics are up to date.
6. The tables in concern range from 1M to 20M rows.
7. The cost of the queries in concern becomes 30% of current when hash joins are enabled, but they
are 5-50 times slow!!

Any help or any lead would really be appreciated.

Regards,
Saurabh