X

Insights into Statistics, Query Optimization and the Oracle Optimizer

Locked Optimizer Statistics – A Useful Tool in Your Toolbox

Nigel Bayliss
Product Manager

As you might imagine, I meet a lot of customers that need to load large volumes of data continuously into database tables. When high volumes are involved, use of table partitioning is common and, in response to that, there are operational procedures designed to manage partition creation, exchange, movement and so on. In addition, the timing of statistics maintenance operations is likely to be tightly coupled to the batch workflow so that execution plans remain consistent and repeatable during the batch and beyond. For this reason, a DBA may consider automatic stats gathering unsuitable for managing some tables. Unfortunately, this can lead to some DBAs to disable auto stats gathering altogether and, instead, relying on scripts of some kind.

However, there is an elegant happy-medium option worth consideration. Why not let auto stats take care of most objects in the database and reserve some tables for closer management? It is not necessarily easy to see how you can prevent auto stats from gathering statistics on a specific table because there’s no obvious ‘exclude this table from auto stats’ procedure in the DBMS_STATS API. Let’s instead take a look at how you can achieve this, with an easy mechanism for gathering statistics manually on the tables you want to exclude.

Consider a partitioned table, ‘P1’. In the following example I’ve chosen to use incremental statistics management to reduce the amount of work required to refresh stats, but use of this setting isn’t essential:

create table p1 (id number(10), b number(10))
partition by range (id) (
partition p1 values less than (10001),
partition p2 values less than (20001));
EXEC dbms_stats.set_table_prefs(user,'P1','INCREMENTAL','TRUE')
EXEC dbms_stats.set_table_prefs(user,'P1','INCREMENTAL_STALENESS','USE_STALE_PERCENT')
EXEC dbms_stats.set_table_prefs(user,'P1','INCREMENTAL_LEVEL', 'PARTITION')
EXEC dbms_stats.gather_table_stats(user,'p1')

Now for the first trick: locking table stats will prevent the auto stats job from gathering statistics on P1 (including its indexes):

exec dbms_stats.lock_table_stats(user,'p1')

Over time, statistics will become stale:

insert into p1 select rownum,rownum from dual connect by rownum <= 5000;
commit;
exec dbms_stats.flush_database_monitoring_info
select table_name,partition_name,stale_stats from user_tab_statistics;
TABLE_NAME           PARTITION_NAME       STALE_S
-------------------- -------------------- -------
P1                                        YES    
P1                   P1                   YES    
P1                   P3
P1                   P2                   NO          


If auto stats runs, it will not gather statistics on P1. Instead, we can let the batch process do it at a point-in-time of our choosing. This relies on a second trick: FORCE=>TRUE:

exec dbms_stats.gather_table_stats(user,'p1',options=>'gather auto',force=>true)
TABLE_NAME           PARTITION_NAME       STALE_S
-------------------- -------------------- -------
P1                                        NO     
P1                   P3                   NO     
P1                   P2                   NO     
P1                   P1                   NO  

If we had used the default FORCE=>FALSE, then an error message would have been raised and gather table stats would have been prevented. Instead, use of FORCE=>TRUE overrides the table’s locked status and allows statistics to be refreshed.

Join the discussion

Comments ( 1 )
  • Eric Vanier Tuesday, August 11, 2020
    Really very happy to say,your post is very interesting to read.Thanks for sharing.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.