1

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.

Improve this question
2
  • 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
    Commented May 16, 2013 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?
    – user1822
    Commented May 16, 2013 at 16:41

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.