How To Gather Fixed Object Statistics After Upgrade With AutoUpgrade

In a previous blog post I wrote about the importance of gathering fixed objects statistics at the right time. The (at time-of-writing) latest release of AutoUpgrade, 19.9.2, does unfortunately by default gather fixed objects statistics as part of the post-upgrade checks. This is not good and not in line with our own recommendations. We have a bug open to get that changed – stay tuned! And a thanks to a keen reader of the blog, Robert Ortel, who made a comment on this.

Update 29th October 2020: The bug is fixed in the latest version of AutoUpgrade, 19.10.0.

Prevent AutoUpgrade From Gathering Fixed Objects Statistics

Until we have a new version of AutoUpgrade ready for you, here is a workaround. AutoUpgrade is by design very flexible and there is a way to override the default fixups. It is very well described in our documentation and Mike Dietrich has a really good blog post about it.

The fixup that you want to override and disable is:

[checkname]          POST_FIXED_OBJECTS
[stage]              POSTCHECKS
[fixup_available]    YES
[runfix]             YES <== Change to NO 
[severity]           RECOMMEND

And you should change runfix to NO. Save the checklist file and reference it from the AutoUpgrade config file using the checklist parameter.

Schedule Fixed Objects Statistics Gathering

In the previous blog post I argued that you could create a scheduler job that re-gathers fixed objects statistics after a number of days (and the database is warmed-up). If you like that idea here is how you can implement that in AutoUpgrade.

CDB

First, create a file named /home/oracle/sched_gfos/sched_gfos.sql:

BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
      job_name => '"SYS"."GATHER_FIXED_OBJECTS_STATS_ONE_TIME"',
	  job_type => 'PLSQL_BLOCK',
	  job_action => 'BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; END;',
	  start_date => SYSDATE+7,
	  auto_drop => TRUE,
	  comments => 'Gather fixed objects stats after upgrade - one time'   
   );
   DBMS_SCHEDULER.ENABLE (
      name => '"SYS"."GATHER_FIXED_OBJECTS_STATS_ONE_TIME"'
   );
END;
/

Next, create a script named /home/oracle/sched_gfos/sched_gfos.sh. It will run the SQL script in all PDBs including CDB$ROOT, except PDB$SEED. I don’t want to run in PDB$SEED because it is normally in READ ONLY state:

export ORACLE_SID=CDB1
export ORACLE_HOME=/u01/app/oracle/product/19
export PATH=$ORACLE_HOME/bin:$PATH
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 4 -C 'PDB$SEED' -e -b sched_gfos -d /home/oracle/sched_gfos/ sched_gfos.sql

And then I add the following line to my config file:

upg1.after_action=/home/oracle/sched_gfos/sched_gfos.sh

Non-CDB

First, create a file named /home/oracle/sched_gfos/sched_gfos.sql:

BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
      job_name => '"SYS"."GATHER_FIXED_OBJECTS_STATS_ONE_TIME"',
	  job_type => 'PLSQL_BLOCK',
	  job_action => 'BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; END;',
	  start_date => SYSDATE+7,
	  auto_drop => TRUE,
	  comments => 'Gather fixed objects stats after upgrade - one time'   
   );
   DBMS_SCHEDULER.ENABLE (
      name => '"SYS"."GATHER_FIXED_OBJECTS_STATS_ONE_TIME"'
   );
END;
/
EXIT

Next, create a script named /home/oracle/sched_gfos/sched_gfos.sh:

export ORACLE_SID=DB1
export ORACLE_HOME=/u01/app/oracle/product/19
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus / as sysdba @/home/oracle/sched_gfos/sched_gfos.sql

And then I add the following line to my config file:

upg1.after_action=/home/oracle/sched_gfos/sched_gfos.sh