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.

I am experiencing the same symptoms as noted in this question which was closed and does not have a satisfactory answer.

Original Question 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
    
The question was closed because it was difficult to know what was going on and the OP either wasn't able or willing to provide potential answerers with the information that they needed. I hope you are able to get resolution on this but in order to do so, you may be asked for more information. –  swasheck May 16 '13 at 16:19
5  
Seems to be, 11gR2 is twice as fast: it only needs half of the consistent gets. The number of physical reads simply means the data wasn't (yet) cached. What happened when you immediately ran the same statement again? –  a_horse_with_no_name May 16 '13 at 16:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.