Recently I upgraded a database from version 12.1 to 12.2.
I take upgrades serious - similar (but not as skilled) to Mike Dietrich. So I downloaded latest preupgrade.zip and unzipped it [I made a small error by unzipping it NOT to $ORACLE_HOME/rdbms/admin but to a temporary directory - later more about this].
Beside such errors, I try to follow Oracles recommended Upgrade Process.
At some point Oracle recommends to Gather Dictionary Stats "the night before starting the upgrade".
For me this is a recommendation, not an obligation. I had (from my perspective) very good reasons NOT to follow this recommendation.
Still, during the upgrade, the preupgrade.log shows
+ (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Dictionary statistics do not exist or are stale (not up-to-date).
Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.
For information on managing optimizer statistics, refer to the 12.1.0.2
Oracle Database SQL Tuning Guide.
If they say AUTOFIXUP - treat this as a promise (or treat).
After preupgrade.jar, it's to run preupgrade_fixups.sql.
This calls (among others)
fixup_result := dbms_preup.run_fixup('dictionary_stats');
If you go through the package and other functionality, you will find function dictionary_stats_check in preupgrade_package.sql. And there it runsBEGIN
EXECUTE IMMEDIATE
' select 1 from dual where exists(
select distinct operation
from DBA_OPTSTAT_OPERATIONS
where operation =''gather_dictionary_stats''
and start_time > systimestamp - INTERVAL ''24'' HOUR) '
INTO dictionary_stats_recent;
and if no dictionary stats were conpleted recently, this function is executed:
-- *****************************************************************
-- This fixup executes dictionary stats pre upgrade
-- *****************************************************************
FUNCTION dictionary_stats_fixup (
result_txt IN OUT VARCHAR2,
pSqlcode IN OUT NUMBER) RETURN NUMBER
IS
stats_result BOOLEAN;
sys_string varchar2(5):='SYS';
BEGIN
stats_result := run_int_proc('DBMS_STATS.GATHER_DICTIONARY_STATS', result_txt, pSqlcode);
IF (stats_result) THEN
RETURN c_success;
ELSE
RETURN c_failure;
END IF;
END dictionary_stats_fixup;
That's the moment where I recognised how serious Oracle takes gathering of dictionary stats.A little rant on twitter about this aggressive recommendation gave me a nice reply where the recommendation is stated:
You mean ... just like it's stated right there the manual? Well, well, well... who would have thought 😀 pic.twitter.com/eI9hUKnSEu— Connor 🙃 McDonald (@connor_mc_d) October 9, 2018
🤔
At least I know where to change the code - but it must be done before preupgrade.jar is run!
But there is a 2nd situation when DBMS_STATS.GATHER_DICTIONARY_STATS is run:
After the upgrade itself, postupgrade_fixups.sql must run.
Here the side note to my initial statement why preupgrade.zip MUST be unzipped to $ORACLE_HOME/rdbms/admin:
VARIABLE admin_preupgrade_dir VARCHAR2(512);
REM
REM point PREUPGRADE_DIR to OH/rdbms/admin
REM
DECLARE
oh VARCHAR2(4000);
BEGIN
dbms_system.get_env('ORACLE_HOME', oh);
:admin_preupgrade_dir := dbms_assert.enquote_literal(oh || '/rdbms/admin');
END;
/
DECLARE
command varchar2(4000);
BEGIN
command := 'CREATE OR REPLACE DIRECTORY PREUPGRADE_DIR AS ' || :admin_preupgrade_dir;
EXECUTE IMMEDIATE command;
END;
/
@?/rdbms/admin/dbms_registry_basic.sql
@?/rdbms/admin/dbms_registry_extended.sql
REM
REM Execute the preupgrade_package from the PREUPGRADE_DIR
REM This is needed because the preupgrade_messages.properties file
REM lives there too, and is read by preupgrade_package.sql using
REM the PREUPGRADE_DIR.
REM
COLUMN directory_path NEW_VALUE admin_preupgrade_dir NOPRINT;
select directory_path from dba_directories where directory_name='PREUPGRADE_DIR';
set concat '.';
@&admin_preupgrade_dir./preupgrade_package.sql
That's the reason why unzipping it ton another directory, after the upgrade an older version of preupgrade scripts is used - maybe not a desired thing!Later there another fixup is called:
fixup_result := dbms_preup.run_fixup('post_dictionary');
This in that case that function is run:-- *****************************************************************
-- POST_DICTIONARY_CHECK Section
-- This check recommends re-gathering dictionary stats post upgrade
-- The logic in the query is: Check if statistics has been taken
-- after upgrade, if not report it and generate the fixup in the
-- postupgrade fixup script, after the fixup run, it will not fail
-- and therefore it will report this check as successfull.
-- *****************************************************************
FUNCTION post_dictionary_check (result_txt OUT VARCHAR2) RETURN NUMBER
IS
dictionary_stats_recent NUMBER := 0;
correct_version boolean := TRUE;
BEGIN
IF dbms_registry_extended.compare_versions(db_version_4_dots, C_ORACLE_HIGH_VERSION_4_DOTS, 4) < 0 THEN
correct_version := FALSE;
END IF;
BEGIN
EXECUTE IMMEDIATE
' select 1 from dual where exists(
select distinct operation
from DBA_OPTSTAT_OPERATIONS
where operation =''gather_dictionary_stats''
and start_time > (select max(OPTIME) from registry$log where cid =''UPGRD_END'')) '
INTO dictionary_stats_recent;
EXCEPTION
WHEN NO_DATA_FOUND then
null;
END;
After all this, I STILL recommend to use LATEST preupgrade.jar,
unzip it to proper $ORACLE_HOME/rdbms/admin,
execute it as documented
and don't mess around with it!
Don't mess around with it unless Oracle Support says so (or you take all the unforseen side effects on your credits).
Restore is done, but recovery takes it's time! I'm sure archivelogs are spread cross many tapes. This gave me time to do some (very simple!) performance tests with 
Unfortunately, the documentation is slightly unspecific regarding the requirements: glibc 2.14 is met, but in my system only 64 bit by default.