How to gather Oracle optimizer statistics with minimal risks of regression

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

How to gather Oracle optimizer statistics with minimal risks of regression

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);