Describes using Real-time SQL Plan Management when you migrate from a source Oracle Database to Autonomous Database.

When you migrate from a source Oracle Database to Autonomous Database you can use Real-time SQL Plan Management (SPM). This enables you to capture the SQL execution plans from your source database and move them into Automatic SQL Tuning Set (ASTS) on Autonomous Database so that plans continue to perform with the same or better performance after the migration. Real-time SPM allows plans to change on Autonomous Database, but if Real-time SPM sees poor performance, it may use a plan that came from the pre-migrated database if that plan provides better performance (Real-time SPM uses the plans in Automatic SQL Tuning Set (ASTS) only if they provide better performance).

Keep the following in mind for using Real-time SPM with a migration to Autonomous Database:

• SPM relies on repeatable SQL statements. SPM is not suitable for databases that use literal values in SQL statements or for SQL that is highly dynamic, such as in ad-hoc query environments. However, if the SQL statements use literal values and the CURSOR_SHARING parameter is set to FORCE, SPM will work.
• In the source Oracle Database that you are migrating to Autonomous Database you capture application SQL statements in a SQL tuning set (SYS_AUTO_SYS). This can consume space in SYSAUX, but typically consumes no more than a few gigabytes (even for large systems). You can monitor the SYSAUX usage and increase the size of the tablespace, if necessary.
• Real-time SPM cannot prevent all performance regressions, but it can significantly reduce the risk of performance regressions happening due to SQL execution plan changes.

Perform the following steps to enable SQL Plan Management (SPM) on your source Oracle Database and to migrate the database to Autonomous Database:

1. Enable Automatic SQL Tuning Set on the Source Oracle Database

2. Migrate Your Data to Autonomous Database

3. Export Automatic SQL Tuning Set from Your Source Oracle Database

4. Import Automatic SQL Tuning Set to Autonomous Database

5. Verify Your Real-time SPM Setting on Autonomous Database

See the following for more information:

• CURSOR_SHARING
• What is Real-time SQL plan Management?
• Using the Automatic SQL Tuning Set in Oracle Database 19c

Before you migrate to Autonomous Database enable Automatic SQL Tuning Set (ASTS) on your source Oracle Database.

1. On the database you want to migrate, enable ASTS.

   BEGIN
     dbms_auto_task_admin.enable(
       client_name => 'Auto STS Capture Task',
       operation   => NULL,
       window_name => NULL);
   END;
   /

   See DBMS_AUTO_TASK_ADMIN for more information.

2. Verify that the ASTS background task is enabled.

   SELECT task_name, interval,status, last_schedule_time, enabled
       FROM   dba_autotask_schedule_control
       WHERE  dbid = sys_context('userenv','con_dbid') AND 
              task_name = 'Auto STS Capture Task';

SELECT substr(sql_text,1,100) txt, executions
    FROM dba_sqlset_statements 
    WHERE sqlset_name = 'SYS_AUTO_STS';

SELECT sum(bytes)/(1024*1024*1024) size_gb
    FROM   dba_data_files
    WHERE tablespace_name = 'SYSAUX' GROUP BY tablespace_name;

SELECT sum(bytes)/(1024*1024*1024) free_gb
    FROM dba_free_space
    WHERE tablespace_name = 'SYSAUX' GROUP BY tablespace_name;

After you capture a sufficient amount of SQL on the source Oracle Database with Automatic SQL Tuning Set (ASTS) enabled, perform the migration to Autonomous Database.

After you perform the migration to Autonomous Database, export the Automatic SQL Tuning Set (ASTS) from your source Oracle Database.

1. On the source Oracle Database, create and populate a staging table for ASTS data.

   As a DBA user, create the staging table:

   BEGIN
      dbms_sqlset.create_stgtab('ASTS_TABLE');
      dbms_sqlset.pack_stgtab('SYS_AUTO_STS','SYS','ASTS_TABLE');
   END;
   /

   After this operation completes the staging table contains the SQL statements that were captured on the source Oracle Database.

2. Export the staging table.

   For example, export the staging table using Oracle Data Pump:

   CREATE DIRECTORY dpdir AS '/export_directory';
   expdp user/password@database tables=asts_table directory=directory dumpfile=filename

After you perform the migration to Autonomous Database and you export the Automatic SQL Tuning Set (ASTS) from the source Oracle Database you are migrating, import the ASTS to your Autonomous Database.

1. Import the staging file to your Autonomous Database.

   Use Oracle Data Pump to import the staging table into Autonomous Database. First upload the dump file you exported from your source Oracle Database to a Cloud Object Storage bucket and then import the dump file.

   For example, as the ADMIN user run these commands:

   1. Create the credential to access the Cloud Object Store bucket.

      BEGIN
        DBMS_CLOUD.CREATE_CREDENTIAL(
          credential_name => 'BUCKET_CREDENTIAL',
          username => 'oracleidentitycloudservice/aaaaa@bbbbb.com',
          password => 'password'
        );
      END;
      /

      See Create Credentials to Access Cloud Services for more information.

   2. Use Oracle Data Pump to import the dump file with the ASTS data to your Autonomous Database instance.

      impdp admin/password@db_adb_high \
           directory=data_pump_dir \
           credential=BUCKET_CREDENTIAL \
           dumpfile= https://namespace-string.objectstorage.us-ashburn-1.oci.customer-oci.com/n/namespace-string/b/bucketname/o/asts_staging.dmp \
           tables=asts_table

      See Import Data Using Oracle Data Pump on Autonomous Database for more information.

   Note
   
   Make sure that the timezone file in Autonomous Database matches the value in the source Oracle Database where you created the dump file. If there is a timezone mismatch, the database raises the following error:

   ORA-39002: invalid operation' error raised by dbms_datapump.start_job'

   See Datetime Data Types and Time Zone Support and Manage Time Zone File Updates on Autonomous Database for more information.

2. In the migrated Autonomous Database, unpack the SQL statements from the staging table and load them into the destination ASTS.

   BEGIN
      dbms_sqlset.unpack_stgtab('SYS_AUTO_STS','SYS',TRUE,'ASTS_TABLE');
   END;
   /

Describes the steps to verify that real-time SPM is enabled on Autonomous Database.

SELECT parameter_value spm_mode  
    FROM   dba_sql_management_config
    WHERE  parameter_name = 'AUTO_SPM_EVOLVE_TASK'; 

EXEC dbms_spm.configure('AUTO_SPM_EVOLVE_TASK', 'AUTO')