Re: "10053" for Exadata smart table scan?

can you manually set statistics so that size is > threshold?

On Tue, Sep 19, 2017 at 10:09 AM Dave Herring <gdherri_at_gmail.com> wrote:

> 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