1. Performance Tuning Guide
  2. Steps to Create the SQL Plan Baseline in Source Database

Steps to Create the SQL Plan Baseline in Source Database

Before generating the SQL Plan Baseline, it is highly recommended that you optimize all or the most critical SQL statements that are selected to be transported to target database.

Steps and options

All SQL statements that require to be transported from Source to Target database must be executed during SQL Baseline capture in option #1 or must be in CURSOR_CACHE in option #2 while DBMS_SQLTUNE.SELECT_CURSOR_CACHE is polling the cache (please see the remark below).

  1. Option 1: Automatic Plan Capture
  2. Option 2: Manual Plan Capture and the STS section SQL Tuning Set (STS)
  3. In case the second option is chosen, the SPM baseline can be created on the Source database through: DBMS_SPM.LOAD_PLANS_FROM_SQLSET
  4. In-Place Upgrade: The SPM Baseline is preserved and honored after the upgrade has completed
  5. Once the SPM baseline is created through option #1 or #2, it can be transported to Target if different than Source:
    1. DBMS_SPM.PACK_STGTAB_BASELINE will use a staging table to pack the baseline
    2. Export the table from Source database and import it into Target database
    3. DBMS_SPM.UNPACK_STGTAB_BASELINE will unpack the baseline on Target database
  6. Make sure that the initialization parameter OPTIMIZER_USE_SQL_PLAN_BASELINES is set to TRUE in Target database or at after the Oracle 19c Database and future releases Upgrade has completed (in-place).
Note: Options #1 and #2 should be able to capture the execution plans of all SQL statements that have been optimized through SQL Profile, Stored Outlines and SQL Patches as long as they're in CURSOR_CACHE. However, direct migration of these optimizations is briefly explained in the following sections in case SQL Profiles and Stored Outlines aren't captured because of filters applied or SQL Baseline was generated differently than the proposed method above.