Oracle 10gR2 to 11gR2 upgrade - performance hit We've recently upgraded from Oracle 10gR2 (10.2.0.4 ) to 11gR2 (11.2.0.3) and we are noticing a significant hit in performance although execution plans are the same for the offended queries before and after the upgrade. Allocation of more memory did improve the performance but just slightly. We also tried to set optimizer mode to 10204 at session level but queries produced no better results. The only difference we've noticed is when enabling autotrace the physical reads on 10gR2 were 0 whereas on 11gR2 it was of the magnitude of tens of thousands.

Including stats demonstrating the physical reads discrepancies:

10gR2 Stats
~~~~~~~~~~
Statistics
--------------------------------------------------------
          0  recursive calls
          0  db block gets
      21189  consistent gets
          0  physical reads
          0  redo size
       1014  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed



11gR2
~~~~~~
Statistics
------------------------------------------------------
          0  recursive calls
          0  db block gets
      11243  consistent gets
      11232  physical reads
          0  redo size
       1049  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


Any ideas/suggestions/right-directions would be greatly appreciated.
I think you have AMM - Automatic Memory Management - enabled. This quickly brings you in the situation where all memory goes to the shared_pool and leaves you a very small database cache. Looking at the number of IO's this could fit your case.

Start with a memory setup like you had while running 10g. Forget about AMM on a rdbms instance.