RE: Question on Stats for Indexes Created in Parallel

I guessed the same regarding the cause, but thought there may be something I'm missing.

My concern about setting the stats is that for this low-cardinality column, I know what the distinct values are, but I would assume that any of our bitmap indexes will have bad cardinality estimates as a result of this issue (and I won't know the NDV for each). Re-gathering stats is not a deal-breaker, but it seems like it shouldn't have to be done.

I ran a few more tests in our environments:

DB Version 10.2.0.4 - NDV is correct, regardless of whether the index is built in parallel or not.  Stale_stats is set to NO
DB Version 11.2.0.4 - NDV is wrong when index is built in parallel, correct when built serially.  Stale_stats is set to NO (this is not what I saw in my earlier tests, maybe a user error.....) 
DB Version 12.1.0.2.0 - NDV is wrong when index is built in parallel, correct when built serially.  Stale_stats is set to NO

Sounds like I need to open a ticket with Oracle.

Thanks,
Scott

-----Original Message-----
From: Stefan Koehler [mailto:contact_at_soocs.de] Sent: Tuesday, December 01, 2015 4:36 AM To: Deas, Scott <Scott.Deas_at_lfg.com>; oracle-l_at_freelists.org Subject: RE: Question on Stats for Indexes Created in Parallel

Hi Scott,

> 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

Notice of Confidentiality: **This E-mail and any of its attachments may contain Lincoln National Corporation proprietary information, which is privileged, confidential, or subject to copyright belonging to the Lincoln National Corporation family of companies. This E-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this E-mail, you are hereby notified that any dissemination, distribution, copying, or action taken in relation to the contents of and attachments to this E-mail is strictly prohibited and may be unlawful. If you have received this E-mail in error, please notify the sender immediately and permanently delete the original and any copy of this E-mail and any printout. Thank You.**
†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Tue Dec 01 2015 - 17:23:56 CET