Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Bad performing statement on Exadata suddenly very quick - without us doing anything

3038990May 9 2016 — edited Jun 10 2016

Hello,

A few days ago we migrated a DB from 11.2 to 12c (both binaries

on same server, a SuperCluster). We had a query performing fairly good

in the 11g version (around 2 seconds), but after migration to 12c the

response time was very slow (between 25 and 30 seconds) for several days.

In the latter case a select for this statement SQL_ID on IO_CELL_OFFLOAD_ELIGIBLE_BYTES

and  IO_CELL_OFFLOAD_RETURNED_BYTES in V$SQL kept showing 0 (zero) and

~ select (1 - (io_interconnect_bytes/NULLIF(physical_read_bytes,0)))*100 "SAVING%"

~ from v$sql [...]

null or negative values.

A few hours later i ran this SELECT again - and now it's blazing fast (result is immediately

returned). In the meantime we did... nothing. Modified nothing, we were simply busy with other

things.

Any idea what had our cells to change their behaviour so dramatically ? (now the "SAVING%" ratio

shown above -that, *if I'm not mistaken*, I took from a Tanel Poder's paper- keeps showing a 99....%

value)

Thanks.

Regards,

Seb

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 8 2016
Added on May 9 2016
4 comments
1,702 views