Migrating to Oracle Autonomous Database with Real-time SPM

Introduction

Suppose you want to migrate from a 19c on-premises database or a 19c/23ai BaseDB database to an Oracle Autonomous Database (19c or 23ai). You can use real-time SQL plan management to significantly reduce the risk of SQL performance regressions caused by execution plan changes. You can use the same technique to migrate between autonomous databases, too. This post tells you how.

How's it Done?

Here are the steps:

1. Enable the automatic SQL tuning set (ASTS) in the database you want to migrate. It needs to be version 19c or above.
2. Migrate application data to the Autonomous Database.
3. Export the contents of the ASTS from your pre-migrated database and import it into the Oracle Autonomous Database.
4. Enable real-time SQL plan management in Oracle Autonomous Database.

1 - Enable the Automatic SQL Tuning Set

The automatic SQL tuning set will capture the SQL executing in your pre-migrated database. You need to enable this some time before the migration to ADB begins because SPM needs 'good' plans from which to choose. Real-time SPM repairs plan performance regression by spotting plan changes, and it will reinstate a previously-known plan if it performs better. The pre-migration capture time period depends on how long it takes your application to churn through most of its functionality so that most SQL it uses has been executed. Ideally, you want to capture month-end or year-end processing, but I understand this will not be practical in all cases. So, remember that the SQL you haven't captured will not be protected by real-time SPM, at least not immediately. Over time, this SQL will be captured in the post-migrated database and protected from subsequent plan performance regressions.

If you don't know about the automatic SQL tuning set, here's a blog post covering that.

You are unlikely to need patches, but check this page, which covers what you need to know before enabling the automatic SQL tuning set.

Here's how it is enabled (use a DBA user account):

BEGIN
   DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_Name => 'Auto STS Capture Task',
    operation => NULL,
    window_name => NULL);
END;
/

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

Look in DBA_SQLSET_STATEMENTS if you want to monitor the SQL that's being captured:

select substr(sql_text,1,100) txt,
       executions
from dba_sqlset_statements
where sqlset_name = 'SYS_AUTO_STS';

You will likely see thousands or hundreds of thousands of SQL statements, but the database infrastructure can handle this.

Monitor SYSAUX size and/or free space, for example:

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;

2 - Migrate Your Data to ADB

Migrate your data to ADB however you like - using Data Pump, for example.

3a - Export ASTS SQL Statements

In your pre-migrated database, create and populate a staging table for the automatic SQL tuning set (use a DBA user account):

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

If you have hundreds of thousands of SQL statements or more, the staging table might take some time to load and it can be large. You can specify a tablespace using the PACK_STGTAB if you need to. Once the pack operation is complete, the table will contain the SQL statements captured in the pre-migrated ASTS. You need to get this table to your migrated database and unpack the contents so that the ASTS in that database will contain these SQL statements.

Typically, you will export the staging table using Data Pump. For example:

SQL> create directory mydpdir as '/<export_directory>';
$ expdp <user>/<pass>@mypdb tables=asts_table directory=mydpdir dumpfile=asts_staging.dmp

3b - Import ASTS SQL Statements Into ADB

Use Data Pump to import the staging DMP file into ADB (the migrated database). There are various ways to do this, but you can (for example) upload to a storage bucket and import it from there using the ADMIN account (creating a credential is covered here):

-- Create a credential first 
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'BUCKET_CREDENTIAL',
    username => 'oracleidentitycloudservice/aaaaa@bbbbb.com',
    password => '<generated-auth-token-for-your-user>'
  );
END;
/
set serveroutput on
declare
  h1      number;
  jstatus varchar2(128);
  message varchar2(1024);
begin
   h1 := dbms_datapump.open('IMPORT','FULL');
   dbms_datapump.add_file(handle => h1,
       filename => 'https://...objectstorage....oci.customer-oci.com/n/xxxx/b/yyyy/o/asts_staging.dmp',
       directory => 'BUCKET_CREDENTIAL', filetype => dbms_datapump.ku$_file_type_uridump_file);
   dbms_datapump.metadata_remap(h1, 'REMAP_SCHEMA', '<user-who-owned-staging-table>', 'ADMIN');
   dbms_datapump.start_job(h1);
   dbms_datapump.wait_for_job(handle => h1, job_state => jstatus);
   message := 'Execution status: ''' || jstatus || '''';
   dbms_output.put_line(message);
end;
/

If you are experimenting with copying ASTS SQL without a full migration, make sure that the timezone file in ADB matches your on-premises database before doing this. Otherwise, you will get an 'ORA-39002: invalid operation' error raised by dbms_datapump.start_job.

In the migrated 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;
/

4 - Enable Real-time SPM

Real-time SPM is enabled by default in ADB. You can check the SPM mode setting like this:

select parameter_value spm_mode
from   dba_sql_management_config
where  parameter_name = 'AUTO_SPM_EVOLVE_TASK';

AUTO is (automatic) real-time SPM, and ON is also automatic SPM, with plans verified in the background instead of the foreground (real-time).

If real-time SPM is not already enabled, you use the following command to do that:

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

You are now all set!

More on Migrating to Autonomous Database

There's more on migrating Oracle database to Autonomous Database here. There are a number of options covered. Whatever method you use, you need to get the SPM staging table into the migrated ADB database and unpack it there. After that, enable real-time SPM.

What's the Catch?

• SQL plan management relies on repeatable SQL statements. It's unsuitable for databases that use literal values in SQL statements or if SQL is highly dynamic (such as in ad-hoc query environments). If your SQL statements use literal values but force cursor sharing is enabled, SPM will work with that.
• In the pre-migrated database, you will capture application SQL statements in a SQL tuning set called SYS_AUTO_SYS. This will consume space in SYSAUX, so you should monitor this and increase the size of the tablespace if necessary. You will typically consume no more than a few gigabytes, even on large systems.
• Real-time SPM cannot prevent all performance regressions (I explain that here), but it significantly reduces the risk of it happening due to SQL execution plan changes.

What About Non-Autonomous Upgrades/Migrations?

You can use the same technique if you're migrating from Oracle Database 19c to Oracle BaseDB 23ai since real-time SPM is available there.

In addition, you can use background verified automatic SPM from Oracle Database 19c RU19.22 onwards. In other words, you can perform upgrades with auto SPM set to ON instead of AUTO. This will repair SQL performance regression using a background task. If you are upgrading in place, you won't need to export/import the ASTS; you just need to enable automatic SPM. If you enable automatic SPM (real-time or background modes), ASTS will be enabled for you. You won't need to do that in a separate step.