Hi guys,

We upgraded our DWH DB from version 10.2.0.4 to 11.2.0.3 three months ago.
Since then, we have experienced a performance degradation of 30% in Reports' queries.
After a deep investigation, we've found out that since the upgrading there is a huge increase of SORT MERGE joins on expense of HASH joins:
Code:
  1  select count(1),trunc(timestamp,'MM')
  2  from DBA_HIST_SQL_PLAN
  3  where OPERATION like '%SORT%'
  4  and options like '%JOIN%'
  5  and timestamp >= to_date('01/01/2013','dd/mm/yyyy')
  6  group by trunc(timestamp,'MM')
  7* order by trunc(timestamp,'MM')
SQL> /
 
  COUNT(1) TRUNC(TIM
---------- ---------
       736 01-JAN-13
       672 01-FEB-13
       647 01-MAR-13
      2362 01-APR-13
      3960 01-MAY-13
      2460 01-JUN-13

Since the upgrading we've treated some reports with the hint USE_HASH in order to improve reports but there are too many reports which were screwed up by SMJ.

What can be the reason that the optimizer chooses by mistake SMJ instead of HJ?

Our DB is hosted by the following server:
Server type and version - SunOS 5.10 Generic_144488-08 (64-bit)
CPUs - 32 X 2 (3Gb)
Memory Size (MB) - 524288


Thanks in advance,
Nir