Re: "10053" for Exadata smart table scan?

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_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
>

-- 
Alex Fatkulin

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