Things to Know when you do 19c upgrade

-> autoupgrade.jar recommended way to upgrade
-> dictionary stats have been gathered the last time

column OPERATION format a40
set linesize 200
select to_char(max(END_TIME),'DD-MON-YY hh24:mi') LATEST, OPERATION from DBA_OPTSTAT_OPERATIONS where OPERATION in ('gather_dictionary_stats','gather_fixed_objects_stats') group by operation;


-> stats on clustered indexes ( In case you are not using autoupgrade.jar)

exec dbms_stats.gather_schema_stats('SYS');
exec dbms_stats.gather_index_stats('SYS','I_OBJ#');
exec dbms_stats.gather_index_stats('SYS','I_FILE#_BLOCK#');
exec dbms_stats.gather_index_stats('SYS','I_TS#');
exec dbms_stats.gather_index_stats('SYS','I_USER#');
exec dbms_stats.gather_index_stats('SYS','I_TOID_VERSION#');
exec dbms_stats.gather_index_stats('SYS','I_MLOG#');
exec dbms_stats.gather_index_stats('SYS','I_RG#');

-> Things to do right after upgrade -> 
exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(14);
_cursor_obsolete_threshold=1024
deferred_segment_creation=false
_sql_plan_directive_mgmt_control=0
Set optimizer_adaptive_statistics=FALSE explicitly in your SPFILE

-> Comptable (features) and optimiser_features_enable (use latest)  - Keep the latest

-> Collect execution plan before upgrade (cursor cache and AWR) [how to ?] [sql tunning sets]

-> compare AWR snapshots (AWRDDRPT.sql), You can export AWR data using the awrexp script in rdbms/admin

-> capture STS -> load STS (SQL performance analyser)

-> SPM  ( 53 week default - dbms_spm.configure('plan retention week',5) , (space_budget_percent',5)
   Baseline Capture -> optimiser_capture_sql_plan_baselines= TRUE (start recording ) 
   selection -> optimise_use_sql_plan_baselines= TRUE,OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE
   evolution -> dbms_spm.report_auto_evolve_task,  DBMS_SPM.CREATE_EVOLVE_TASK

-> SQL Tuning Advisor

-> export/import STS to new DB  

-> DBMS_SPM.LOAD_PLANS_FROM_SQLSET

-> capture workload, reply workload (compare)

-> SPA is Real Application Testing.SPM is a free feature

-> AUTOMATIC SPM - Exadata 19C