This content has been marked as final. Show 4 replies
I see two apparently conflicting statements:
"our DW developers are very good at knowing exactly how to manage stats"
"this approach has neglected the global stats of partitioned tables"
I'd be hesitant to call any stats gathering approach that excludes global stats as being "very good" :-)
As far as the automatic stats job goes, you don't need to use it to use incremental stats; you simply need to gather global stats somehow. Maria Colgan has an example at https://blogs.oracle.com/optimizer/entry/incremental_statistics_maintenance_what_statistics that uses a simple dbms_stats.gather_table_stats.
Yes, you're right. Quite the contradiction, I suppose. Maybe I'll say it differently:
"In order to get what they want to work on a daily basis, they're very good at compiling their own stats. However, they tend to hit problems whenever they query across a large range of partitions (and need global stats)."
OK, I think I have (somewhat) of a handle on it. Right now, I manually gather stats on designated tables using the APPROX_GLOBAL AND PARTITION granularity. Obviously, this is a pain in the posterior.
Instead of this, I would imagine that I will be doing this:
-- Set up the table for Incremental Global Stats Gathering
exec dbms_stats.set_table_prefs('MARKUSER', 'MARKTAB', 'INCREMENTAL', 'TRUE');
-- Gather table statistics for the first time using incremental gathering. Going to take a LONG time. Best bet is to use parallelism. Parallelism FTW!
-- Every time the project team create a new partition, they should do this to gather stats for the partition and then use those stats to update the global stats incrementally
One more question (I know I've moved slightly off-topic). If the MARKUSER.MARKTAB is up-to-date with its global statistics, will the second command (to gather table stats) have to do a FTS to gather its stats or will it gather its stats by taking the partition's stats?
Your approach looks good. The only thing I'd add is that you don't necessarily need to compute statistics if you're concerned about collection time. You can use a sample (ESTIMATE_PERCENT), even with incremental stats.
The idea with incremental stats is that you still gather stats for your newly-added partition the same way as before. But DBMS_STATS maintains a synopsis in the SYSAUX tablespace that lets you avoid scanning all the other partitions, while still maintaining a surprisingly accurate estimate of global statistics.
So in your example, the last GATHER_TABLE_STATS command should do a FTS only on the newly-added partition.
Gotcha. Thanks for the input, Marc!
I guess my only concern is with the growth of the SYSAUX tablespace. Through testing, that seems to be based on cardinality, but might end up growing to HUGE Gb.
We might not enable incremental stats for all the tables, just the ones that take the longest to gather global on, if that is the case.
I'm looking forward to being free of worrying about global stats one way or another soon :)
Have a great weekend,