Search

Top 60 Oracle Blogs

Recent comments

How to gather Oracle optimizer statistics with minimal risks of regression

The ATLAS experiment control room

Here is, on the Databases at CERN blog, an example to lower to risks when you need to gather statistics in production:

  • use pending stats to be able to test them before publishing
  • be ready to restore old ones if a critical regression comes after publishing

The whole demo (I encourage you to follow the Databases at CERN blog):

https://db-blog.web.cern.ch/blog/franck-pachot/2018-09-optimizer-statistics-gathering-pending-and-history

Here is a summary of commands used, for an easy copy/paste:

exec dbms_stats.set_table_prefs('&&OWNER','&&TABLE','publish','false');
exec dbms_stats.gather_table_stats('&&OWNER','&&TABLE');
alter session set optimizer_use_pending_statistics=true;
select /*+ gather_plan_statistics */ count(*) from &&OWNER..&&TABLE;
select * from table(dbms_xplan.display_cursor(format=>'basic +rows +rowstats last'));
alter session set optimizer_use_pending_statistics=false;
exec dbms_stats.delete_pending_stats('&&OWNER','&&TABLE');
exec dbms_stats.publish_pending_stats('&&OWNER','&&TABLE',no_invalidate=>false);
exec dbms_stats.publish_pending_stats('&&OWNER','&&TABLE',no_invalidate=>false);
select report from table(dbms_stats.diff_table_stats_in_history('&&OWNER','&&TABLE',sysdate-1,sysdate,0));
exec dbms_stats.restore_table_stats('&&OWNER','&&TABLE',sysdate-1,no_invalidate=>false);