In the last weeks I noticed an increased number of cases where Oracle starts to pick notoriously bad plans for some statements on widely different systems (Exadata, ODA and AIX).

I haven’t seen that much before with our applications, so I wonder if this is related to a recent patch (the systems seems to have different releases/Versions). Or maybe the default analyser settings have changed?

In one particular case a primary (unique) index is not used for a simple primary key lookup but instead it seems to use some kind of index fast scan. I thought adaptive coursor sharing is actually not used for that kind of simple query.

  • 1
    Have you checked to see if stats have been gathered recently? Could somebody have executed DBMS_RESOURCE_MANAGER.CALIBRATE_IO() by accident? – Philᵀᴹ Jul 10 '18 at 8:31
  • Yes the plan changes when/while the tables are analyzed, the stats are gathered always automatically this is not something new. – eckes Jul 10 '18 at 8:32
  • Is the calibrate_io somewhere visible in awr data history? – eckes Jul 10 '18 at 8:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.