RE: Question on Stats for Indexes Created in Parallel

> 1. Is there any way to avoid having to re-gather stats when creating an index in parallel?

I tried to figure out (with "_px_trace") where (and how) the num distinct keys are gathered, but could not find anything on the quick. It seems like it correlates with the amount of PX slaves, so i assume (no proof!) that the NDV calculation is done on PX slave level and just aggregated afterwards. You got a similar issue with partition and global NDV, but DBMS_STATS got some solution for it.

However if you always know the number of distinct keys you can set it manually with DBMS_STATS without the whole re-gather process.  

> 2. Why is the stale stats value NULL in dba_ind_statistics – especially since it says the stats have been collected at the time the index was built
> (last_analyzed is populated in dba_indexes and dba_ind_statistics)

Due to its definition (i guess especially due to the t.analyzetime part in your case):

-----------------8<----------------------
    case when
          (i.analyzetime is null or
            t.analyzetime is null) then null
         when (i.analyzetime < t.analyzetime or
               (((m.inserts + m.deletes + m.updates) >
                 t.rowcnt *
                 to_number(DBMS_STATS.GET_PREFS('STALE_PERCENT',
                                                DBMS_STATS_INTERNAL.DQ(ut.name),
                                                DBMS_STATS_INTERNAL.DQ(ot.name)))/100 or
                bitand(m.flags,1) = 1))) then 'YES'
         else  'NO'

-----------------8<----------------------

I re-created a test case with 12c as table statistics are automatically gathered with CTAS and in this case it shows STALE=YES instead of NULL with 11g for the same test case. So please check, if your table is analyzed.

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK

> "Deas, Scott" <Scott.Deas_at_lfg.com> hat am 30. November 2015 um 17:43 geschrieben:
>
>
> I realized I left out the part where I mention this is EE 11.2.0.4 RAC on AIX 6.1.
> Thanks,
>
> Scott

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