Things to Know when you do 19c upgrade

Oracle 19c Upgrade Checklist and Best Practices

This document outlines key considerations and recommended steps when performing an upgrade to Oracle Database 19c, focusing on best practices for a smooth transition and optimal post-upgrade performance.

1. Recommended Upgrade Method

• autoupgrade.jar: The autoupgrade.jar utility is the recommended and most robust way to perform Oracle 19c upgrades. It automates many pre-checks, pre-upgrade fixes, and post-upgrade tasks, simplifying the process and reducing manual errors.

2. Pre-Upgrade Checks

Before initiating the upgrade, ensure the following:

a. Dictionary Statistics

Verify that dictionary and fixed object statistics have been gathered recently. This is crucial for the optimizer's performance during and after the upgrade.

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;

b. Stats on Clustered Indexes (If not using autoupgrade.jar)

If you are not using autoupgrade.jar (which typically handles this), it's recommended to gather statistics on critical SYS schema clustered indexes. This helps the optimizer in the new version.

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

3. Post-Upgrade Actions

After the upgrade is complete, consider these immediate actions:

• Adjust Stats History Retention:

  exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(14);
  
  

  This sets the statistics history retention to 14 days.

• Set Key Parameters in SPFILE:

  • _cursor_obsolete_threshold=1024

  • deferred_segment_creation=false

  • _sql_plan_directive_mgmt_control=0

  • Set optimizer_adaptive_statistics=FALSE explicitly in your SPFILE (It's recommended to explicitly set this to FALSE as adaptive statistics can sometimes lead to unexpected plan changes.)

4. Optimizer Parameters

• COMPATIBLE and OPTIMIZER_FEATURES_ENABLE:

  • Ensure the COMPATIBLE parameter is set to the latest version (e.g., 19.0.0).

  • The OPTIMIZER_FEATURES_ENABLE parameter should also be set to the latest version ('19.1.0') to leverage the latest optimizer enhancements.

5. Performance Analysis and Tuning

a. Collect Execution Plans Before Upgrade

Capture existing execution plans to compare them after the upgrade and identify any regressions.

• From Cursor Cache: Query V$SQL_PLAN or GV$SQL_PLAN for active and frequently executed SQL statements.

• Using AWR: Analyze AWR reports for top SQL statements.

• SQL Tuning Sets (STS): The most robust method. Create an STS from the AWR or cursor cache to capture SQL statements, their execution statistics, and execution plans.

  • This allows you to replay the workload later using SQL Performance Analyzer (SPA).

b. Compare AWR Snapshots

• AWRDDRPT.sql: Use the AWRDDRPT.sql script (located in $ORACLE_HOME/rdbms/admin) to generate AWR Diff reports. This allows you to compare performance metrics between AWR snapshots taken before and after the upgrade.

• Export AWR Data: You can export AWR data using the awrexp script (also in $ORACLE_HOME/rdbms/admin) to analyze it on a different database or for long-term storage.

c. SQL Tuning Sets (STS) and SQL Performance Analyzer (SPA)

• Capture STS: Capture a representative workload into a SQL Tuning Set.

• Load STS: Load this STS into the upgraded database.

• SQL Performance Analyzer (SPA): Use SPA (part of Real Application Testing) to compare the performance of the SQL statements in the STS before and after the upgrade. SPA identifies SQL statements with plan changes or performance regressions.

d. SQL Plan Management (SPM)

SPM is a powerful feature to control and stabilize execution plans.

• Configuration:

  • DBMS_SPM.CONFIGURE('PLAN_RETENTION_WEEKS', 5); (Default is 53 weeks)

  • DBMS_SPM.CONFIGURE('SPACE_BUDGET_PERCENT', 5); (Default is 10%)

• Baseline Capture:

  • OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE (Set this to start recording new plans as baselines. Remember to turn it off after capturing.)

• Baseline Selection/Usage:

  • OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE (Ensures the optimizer uses existing baselines.)

  • OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE (Turn off capture during normal operation.)

• Evolution:

  • DBMS_SPM.REPORT_AUTO_EVOLVE_TASK: Reports on the automatic evolution task.

  • DBMS_SPM.CREATE_EVOLVE_TASK: Manually creates a task to evolve (verify and accept) new plans into baselines.

e. SQL Tuning Advisor (STA)

• Utilize the SQL Tuning Advisor to analyze problematic SQL statements identified during post-upgrade testing. It can recommend various tuning actions, including new indexes, SQL profile creation, or SQL structure changes.

f. Export/Import STS to New DB

• After capturing an STS from the source database, you can export it and import it into the target (upgraded) database for performance analysis.

• DBMS_SPM.LOAD_PLANS_FROM_SQLSET: This procedure can be used to load plans from an STS into the SQL Plan Baseline (SPM) repository of the new database.

g. Workload Capture and Replay

• Real Application Testing (RAT): This feature allows you to capture a real production workload from the source database and replay it on the upgraded database. This provides a highly accurate way to test the impact of the upgrade on performance.

  • SPA is a free feature, while Real Application Testing (which includes workload capture/replay) requires a separate license.

h. Automatic SPM (Exadata 19c)

• On Exadata with Oracle 19c, Automatic SPM can further simplify SQL plan management by automatically managing baselines for frequently executed SQL.

By following these guidelines, you can significantly improve the success rate and performance stability of your Oracle 19c database upgrade.

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