X

Insights into Statistics, Query Optimization and the Oracle Optimizer

  • March 21, 2018

Gathering Statistics Only When Stale

Nigel Bayliss
Product Manager

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.

Join the discussion

Comments ( 4 )
  • Warren Thursday, December 5, 2019
    is there a way to know when it is stale or not?
  • Nigel Bayliss Monday, December 9, 2019
    Hi Warren,
    Check out the STALE_STATS column in USER_TAB_STATISTICS. You can also see, for example, what Auto Stats would consider stale using DBMS_STATS.REPORT_GATHER_AUTO_STATS.
    Regards,
    Nigel
  • Joaquin Tuesday, May 12, 2020
    Does it work in 11.2? If it doesn't, how would you do it?
  • Nigel Bayliss Tuesday, May 12, 2020
    "Does it work in 11.2? If it doesn't, how would you do it?" - yes it works in 11.2. The reason I mention the 12c case is that you can use 'gather auto' in GATHER_TABLE_STATS in this way for online stats gathering (which is not available in 11g). The obj_filter_list 'trick' works fine in 11g.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.