RE: Question on Stats for Indexes Created in Parallel
- From: Stefan Koehler <contact@xxxxxxxx>
- To: Scott.Deas@xxxxxxx, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 1 Dec 2015 10:35:51 +0100 (CET)
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'
end
-----------------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: @OracleSK
"Deas, Scott" <Scott.Deas@xxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
