Re: "10053" for Exadata smart table scan?

Ryan, unless I'm misunderstanding your response my main question was answered at first by Alex, with the suggestion of using "ALTER SESSION SET EVENTS 'trace[nsmtio]';". Once I did this I (and using Frits' blog) I could see possibly all the details I needed.

The tough part is what to do now. My employer cancelled their ULA w/ Oracle so now all support is through a 3rd party, which means I'll have to explain all this for 1 party and have them explain it to Oracle, then back through the phone game.

Dave

On Tue, Sep 19, 2017 at 9:43 AM, Ryan January <rjanuary_at_gmail.com> wrote:

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