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