Migrate Existing SQL Execution Plans to Autonomous AI Database to Mitigate the Risk of SQL Performance Regressions

Describes how to reduce the risk of SQL performance regressions when you migrate to Autonomous AI Database.

About Migrating to Autonomous AI Database with Real-time SQL Plan Management (SPM)

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

When you migrate from a source Oracle AI Database to Autonomous AI 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 AI 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 AI 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 AI 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 AI Database that you are migrating to Autonomous AI 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 AI Database and to migrate the database to Autonomous AI Database:

See the following for more information:

Enable Automatic SQL Tuning Set on the Source Oracle AI Database

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

ASTS needs to run for enough time to cover your workload and capture all or most of the SQL statements and their execution plans. So, consider enabling ASTS with lead time in advance of the migration to Autonomous AI Database. For example, for a financial or sales application, capture month-end or year-end processing.

On the source Oracle AI Database, as DBA user enable Automatic SQL Tuning Set (ASTS):

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.

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

If you want to monitor the SQL that’s being captured, view DBA_SQLSET_STATEMENTS. For example:

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

As required, you can monitor the size and free space of SYSAUX. For example:

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

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

Migrate Your Data to Autonomous AI Database

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

See Migrate Oracle AI Databases to Autonomous AI Database for options for migrating to Autonomous AI Database.

Export Automatic SQL Tuning Set from Your Source Oracle AI Database

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

On the source Oracle AI 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 AI Database.

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= dumpfile=*filename*

See DBMS_SQLSET for more information.

Import Automatic SQL Tuning Set to Autonomous AI Database

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

Import the staging file to your Autonomous AI Database.

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

For example, as the ADMIN user run these commands:

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

b. Use Oracle Data Pump to import the dump file with the ASTS data to your Autonomous AI 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 AI Database for more information.
Note:

Make sure that the timezone file in matches the value in the source Oracle AI 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 AI Database for more information.
In the migrated Autonomous AI 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;
/
```

See DBMS_SQLSET for more information.

Verify Your Real-time SPM Setting on Autonomous AI Database

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

Real-time SPM is enabled by default on Autonomous AI Database. You can verify the Real-time SPM mode as follows:

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

The mode AUTO (automatic) real-time SPM indicates real-time SPM is enabled.

If real-time SPM is not enabled, use the following command to enable it:

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