Re: "10053" for Exadata smart table scan?

In both cases the access path from tkprof is the same (although some of the #s are slightly different) so I don't believe anything is changing dynamically:

         4 4 4 PARTITION REFERENCE ALL PARTITION: 1 1024 (cr=17903859 pr=18172040 pw=280905 time=57544458 us cost=2938599 size=386201496 card=963096)

         4 4 4 HASH JOIN (cr=17903859 pr=18172040 pw=280905 time=77612888 us cost=2938599 size=386201496 card=963096)   15940440 15940440 15940440 TABLE ACCESS STORAGE FULL <****tab1****> PARTITION: 1 1024 (cr=9472182 pr=9462531 pw=0 time=27166219 us cost=1548575 size=157947908 card=963097)  129293199 129293199 129293199 TABLE ACCESS STORAGE FULL <****tab2****> PARTITION: 1 1024 (cr=8431677 pr=8428604 pw=0 time=31065204 us cost=1389647 size=30980208660 card=130718180)

What I see as the difference is *possibly* that Oracle is making a leap of logic on X-4 / 11gR2 that since all partitions are being read it uses the SUM of all partitions for the threshold check. For each partition in the nsmtio tracefile on X-4 shows:

NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp) NSMTIO: kcbdpc:DirectRead: tsn: 6, objd: 253905, objn: 106787 ckpt: 1, nblks: 8617124, ntcache: 1, ntdist:0 NSMTIO: Additional Info: VLOT=17697335
Object# = 253905, Object_Size = 8617124 blocks SqlId = 71bj5r8w32wr9, plan_hash_value = 798435894, Partition# = 1023

with "Object_Size" consistently at 8617124 blocks. On X-6 it's:

NSMTIO: kcbism: islarge 0 next 0 nblks 8188 type 2, bpid 3, kcbisdbfc 0 kcbnhl 262144 kcbstt 58676 keep_nb 0 kcbnbh 2941380 kcbnwp 24 NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: 8188 (blocks), Threshold: MTT(293383 blocks), _object_statistics: enabled, Sage: enabled, Direct Read for serial qry: enabled(::::kctfsage::), Ascending SCN table scan: FALSE
flashback_table_scan: FALSE, Row Versions Query: FALSE SqlId: 71bj5r8w32wr9, plan_hash_value: 798435894, Object#: 253905, Parition#: 1023 DW_scan: disabled

Dave
...

On Mon, Sep 18, 2017 at 7:31 PM, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> Dave, did you re-run system statistics? Also, if it's 12.1, do you have
> adaptive optimization turned on? If your adaptive optimization is on, you
> can have optimizer switching from hash join to nested loops.
>
>
>
> On 09/18/2017 07:21 PM, Dave Herring wrote:
>
>> 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
>>
>
> --
> Mladen Gogala
> Oracle DBA
> Tel: (347) 321-1217
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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