Performance degradation after upgrading from 10gR2 to 11gR2
We upgraded our DWH DB from version 10.2.0.4 to 184.108.40.206 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:
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')
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,