Re: "10053" for Exadata smart table scan?

Interesting and thanks! That led me to Frits Hoogland's blog on this and now I can see that the involved table is "too small" (at least that's how I interpret it):

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

The trace has 1024 of the above messages, 1 for each of the hash partitions. The xplan shows "PARTITION REFERENCE ALL" followed by "TABLE ACCESS STORAGE FULL" so even though it's a full scan referencing all partitions, based on the trace file it evaluates each segment separately. Makes sense so far.

On X-4 the partitions are the same size, "_small_table_threshold" is 70789 (vs. 59581 for X-6) but the tracefile shows 1024 lines like:

NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)

VLOT is 1024 on both X-4 and X-6. I'll need to dive deeper into the middle of Frits' blog that hits this situation but what's odd is the change between X-4 and X-6 (and/or 11gR2 and 12cR1), possibly a change in comparing size thresholds for the object.

Dave

On Mon, Sep 18, 2017 at 4:37 PM, Alex Fatkulin <afatkulin_at_gmail.com> wrote:

> 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