Re: "10053" for Exadata smart table scan?

> On Sep 18, 2017, at 4:26 PM, Dave Herring <gdherri_at_gmail.com> 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

Getting directly back to the question of if there's a way to trace why the decision is made. IIRC The decision to do a smart scan is at the storage layer, and below the optimizer's reach. The only thing you can do is ensure you have an environment which would _allow_ for smart scans to occur. The first/easiest step I go to is checking for valid storage indexes on the columns you're interested in.

If anyone is aware of a trace that we could do on the storage cells, that would be good to know.

--
http://www.freelists.org/webpage/oracle-l