A good starting point would be to do "alter session set events
'trace[nsmtio]'" then look at the resulting trace to see why buffered
reads are chosen.
On Mon, Sep 18, 2017 at 4:26 PM, Dave Herring <gdherri@xxxxxxxxx> wrote:
We have an X-6 environment where we're not getting smart table scans on a
particular cursor and nothing is standing out as to why, so I'm wondering
if anyone knows of a way to trace/debug why the choice is being made.
A few details: X-6, Oracle 12.1.0.2, Linux 6.8. Database was restored
from a backup of an X-4, Oracle 11.2.0.4 database on Linux 5.11.
Between X-6 and X-4 for this cursor the plan_hash_value is the same and
the xplans details (including predicts section) are exactly the same,
including references to "storage" in both sections.
Session tracing shows time taken up on "cell multiblock physical read" on
the X-6 whereas on X-4 I see "cell smart table scan" and as you can
imagine, the X-4 cursor runs much faster than on X-6.
Is there any way to trace where and why decisions are made when initially
Oracle seems to think it'll use smart table scans and then give up?
Dave
