Gathering Statistics Only When Stale

Oracle recommends that you use the Automatic Statistics Gathering Job to maintain optimizer statistics, but 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 make sure 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, DBA’s don't always have the luxury of knowing exactly which tables will have stale statistics and which ones won't, so it can be very useful to have a method for gathering statistics on some tables, skipping the ones that don't have stale stats.

Is there an easy way to do that? Of course, 'easy' is a subjective term, but I think you will like the technique I've outlined below.

If you are using Oracle Database 12c or later, 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.

As always, if you have any corrections or suggestions for improvements, leave a comment below.



TEL/電話+86 13764045638
QQ 47079569