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
