Re: "10053" for Exadata smart table scan?

Great idea but that didn't work either. I used commands like "EXEC dbms_stats.set_table_stats(ownname => '...', tabname => '...', partname => '...', numblks => 60000);" but afterwards I still saw lines like the following in the tracefile:

NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: 8188 (blocks), Threshold: MTT(297909 blocks), _object_statistics: enabled, Sage: enabled,

which shows the actual value for the object's size (not stored stats) and yet "_object_statistics: enabled" I understand to mean it's using the object's status.

Dave

On Tue, Sep 19, 2017 at 1:25 PM, Chris Stephens <cstephens16_at_gmail.com> wrote:

> 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