Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

share|improve this question
1  
Do you have a diagnostic pack license? An AWR report will be the most useful thing you can look at –  FreshPhilOfSO Sep 19 '12 at 10:02
    
Did you update dictionary/system/table statistics? –  Mat Sep 19 '12 at 10:08
    
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 –  FreshPhilOfSO 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 –  FreshPhilOfSO 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
show 3 more comments

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.

1 Answer

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
add comment

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