Gathering Statistics Only When Stale

Oracle recommends you use the Automatic Statistics Gathering Job to maintain optimizer statistics. Still, sometimes you might want to gather statistics on a select number of tables rather than (for example) all tables in a particular schema. Perhaps you want to ensure that stats are fresh on a group of tables when a batch run has completed, or maybe at key points during the run. In situations like this, DBAs don’t always have the luxury of knowing exactly which tables will have stale statistics and which ones won’t, so it can be useful to have a method for gathering statistics on some tables, skipping those that don’t have stale stats.

You might assume that something like this will work:

exec dbms_stats.gather_table_stats(user, 't1',options=>'gather auto')
exec dbms_stats.gather_table_stats(user, 't2',options=>'gather auto')
exec dbms_stats.gather_table_stats(user, 't3',options=>'gather auto')

It doesn’t work because ‘GATHER AUTO’ in the context of GATHER_TABLE_STATS is for creating additional statistics (such as histograms) after a bulk load operation has taken advantage of online statistics gathering.

When I was talking to the folks in the Real World Performance Group, a neat solution was presented. They told me that they use GATHER_SCHEMA_STATS with ‘GATHER AUTO’ to implement something like this:

DECLARE
filter_lst dbms_stats.objecttab := dbms_stats.objecttab();
BEGIN
filter_lst.extend(2);
filter_lst(1).ownname := 'hr';
filter_lst(1).objname := 'employees';
filter_lst(2).ownname := 'hr';
filter_lst(2).objname := 'departments';

dbms_stats.gather_schema_stats(
ownname => 'hr',
obj_filter_list=> filter_lst,
options => 'gather auto');
END;
/

The ‘GATHER AUTO’ option in the context of GATHER_SCHEMA_STATS tells the database to skip tables and partitions that do not have stale statistics. If you want to try it out for yourself, I’ve uploaded a couple of examples to GitHub. There is a test case for both partitioned and non-partitioned tables.

In 2012, Maria Colgan wrote about using obj_filter_list in the context of concurrent stats gathering. You can check out that blog post here. It includes some additional information on the specifics of obj_filter_list too.