Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We've recently upgraded from Oracle 10gR2 ( ) to 11gR2 ( 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
          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

          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.

share|improve this question

closed as not a real question by Jack Douglas Dec 18 '12 at 15:36

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

Do you have a diagnostic pack license? An AWR report will be the most useful thing you can look at – Phil Sep 19 '12 at 10:02
Oh, and regarding the physical reads thing. It's likely that the majority of the working set of data was in cache due to the 10gR2 DB being up for a while - obviously this wouldn't be the case when the 11g database was first put to use. 11g also introduced a feature whereby the DB does direct path reads for some scans in some situations (rather than going via the cache) - this may be affecting you – Phil Sep 19 '12 at 10:09
If you don't have the diagnostic pack, run a Statspack report. $ORACLE_HOME/rdbms/admin/spreport.sql - Edit out any site-specific stuff and put it on pastebin – Phil Sep 19 '12 at 10:18
@Phil: thanks for your comments. I've updated the description with the stats – dimitrisli Sep 19 '12 at 10:24
Can you post the explain plan for that query? – Phil Sep 19 '12 at 10:26

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.

share|improve this answer
thanks for your answer. We don't have AMM enabled by memory_target. Only sga_target/pga_agg_target similar to the 10gR2 setup. – dimitrisli Sep 19 '12 at 11:07
how are the shared_pool and database cache sizes? – ik_zelf Sep 19 '12 at 11:56

Not the answer you're looking for? Browse other questions tagged or ask your own question.