Managing statistics during migration to Oracle Autonomous AI Database

Database statistics is often an area which is confusing when doing a database migration , especially to Oracle Autonomous AI Database.

Let’s explore what we need to consider in terms of statistics during migration to Oracle Autonomous AI database.

I have used below acronyms.

ATP-S – Autonomous AI Transaction Processing – Serverless

ATP-D – Autonomous AI Transaction Processing -Dedicated

ALK-S – Autonomous AI Lakehouse – Serverless

ALK-D – Autonomous AI Lakehouse – Dedicated

ADB – Autonomous AI Database (Generic name used when workload is not specified or relevant)

I have divided the blog as below sections.

Statistics management components

Autonomous AI Database Statistics – How and When ?

Managing statistics during logical offline migration

Managing statistics during logical online migration

Managing statistics preferences for critical databases

Performance Stability

Statistics management components

Accurate statistics in the target database are essential for a successful database migration.

This requires proper handling of the following three components:

Object Statistics

Statistics Preferences

Column Usage Information

Object statistics

These are the statistics about the objects (tables/indexes etc) .

Below are some of the examples of object statistics.

Number of rows.

Number of distinct values.

Statistics preferences

This is a mechanism for us to tell database how and when to collect statistics for certain schemas , tables or for the entire database.

Preferences are typically implemented based on our learning either from a previous incident or from our testing.

For e.g. We have learned that for a certain tables it is best to set the stale percent to 5 instead of the default of 10 since you had a performance issue in the past where certain queries didn’t behave well even when the percentage of changes in table were less than 10% and collecting stats resolved the issue.

Column usage information

This information is collected by database based on the workload and it has information about how the columns are used by queries . This information is critical for creating histograms by the database.

Autonomous AI Database Statistics – How and When ?

It is vital to understand how and when statistics are gathered in an Autonomous AI Database.

This again depends on whether our database is ATP or ALK.

Autonomous AI Transaction Processing (ATP):

Below is applicable for both serverless and dedicated.

Autonomous AI Transaction Processing database gathers optimizer statistics automatically so that we do not need to perform this task manually and this helps to ensure our statistics are current.

Automatic statistics gathering is enabled and runs in a standard maintenance window.

We also have high frequency stats job for ATP which runs every 15 minutes.

Real time statistics gathering is disabled by default and it is recommended not to change it .

Autonomous AI Lakehouse (ALK):

Below is applicable for both serverless and dedicated.

All the standard database maintenance windows are disabled and also all the auto task jobs including stats jobs .

This is done on purpose since it is assumed that there are not many DMLs happening on a regular basis on Autonomous AI Lake House.

However database gathers optimizer statistics automatically for tables loaded with direct path operations issued in SQL . For example, for loads using the DBMS_CLOUD package the database gathers optimizer statistics automatically.

If we have tables modified using conventional DML operations we can run commands to gather optimizer statistics for those tables.

For example, for the APP schema we can gather statistics for all tables in the schema using the following command.

BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS('APP', options=>'GATHER AUTO');
END;
/

This will gather statistics for all tables that have stale statistics in the APP schema.

ALK also have high frequency stats gathering job in place which runs every 15 minutes by default.

Real time statistics gathering is disabled by default and it is recommended not to change it .

Managing statistics during logical offline migration

Our recommendation is to exclude statistics during Data Pump export and gather them after the Data Pump Import.

Please follow below sequence for ATP and ALK (both serverless and dedicated).

a. Data Pump Export

It is not a good practice to bring the statistics from the source database during logical migration especially to Autonomous Database.

Ensure that we exclude statistics during DataPump Export using below parameter for manual migration.

exclude=statistics

For migrations using Oracle Zero Downtime Migration , we don’t have to do anything since statistics are excluded by default.

Please note that this will exclude object statistics , statistics preference and columns usage information.

b. Data Pump Import

It is again not a good practice to collect stats during Import since only basic statistics can be gathered this way with potentially significant overhead during the process.

Please ensure statistics are not collected during Data Pump Import using below parameter for manual migration.

DATA_OPTIONS=DISABLE_STATS_GATHERING

We have noticed that above parameter doesn’t with 19c client (19.31 as well) and we could make it work with 23.26.2 client.

Please set below for Oracle Zero Downtime Migration.

DATAPUMPSETTINGS_DATAPUMPPARAMETERS_DISABLESTATSGATHERING=TRUE

c. Gather stats

There is no statistics present in the target Autonomous AI database now since we have excluded statistics and also disabled stats collection during import.

Let’s proceed with stats collection for each of the schemas for all ADBs except ATP-D.

begin
dbms_stats.gather_schema_stats(
ownname => 'APP',
options => 'GATHER',
cascade => TRUE,
degree => DBMS_STATS.AUTO_DEGREE);
end;
/

This will also create histograms on all columns since by default global preference for METHOD_OPT is set to FOR ALL COLUMNS SIZE 254 for all ADBs except ATP-D.

For ATP-D , METHOD_OPT is set to FOR ALL COLUMNS SIZE AUTO by default which means database creates histograms where it is beneficial and this would require column usage information which is empty now.

I would recommend to modify the global preference (only for ATP-D) before stats collection so that histograms are created based on the data distribution.

Check the current preference .

SELECT DBMS_STATS.GET_PREFS('METHOD_OPT') AS method_opt FROM dual;

Below is the output from my environment.

METHOD_OPT
—————
FOR ALL COLUMNS SIZE AUTO

Modify the preference as below.

BEGIN
    DBMS_STATS.SET_GLOBAL_PREFS(
        pname => 'METHOD_OPT',
        pvalue => 'FOR ALL COLUMNS SIZE SKEWONLY'
    );
END;
/

Gather statistics for ATP-D now using the same package mentioned earlier in the step. Please note that we might end up creating more histograms than actually needed , however it is better to have more histograms than fewer histograms since the latter can lead to performance issues.

We can continue with this preference until few days after go-live (to ensure column usage information is populated) and revert METHOD_OPT to FOR ALL COLUMNS SIZE AUTO which is the original default value.

d. Statistics preferences

We have object statistics in place now , let’s see how to handle the stats preferences.

It is not recommended to blindly bring the non-global statistics preference from source to target unless there is a reason.

One way to evaluate whether we need them is to review the reason/condition for having those preferences and see whether same reasons are still applicable in Autonomous AI database environment. Once we decide that we need a particular preference , we can transport them to target using method mentioned here.

Alternative way to evaluate statistics preferences is to test our workload in target with SQL performance Analyzer and see whether they are still relevant. This is the recommended way for critical databases . Please refer next section for more details on the process.

What about global statistics preferences from source database ?

It is generally not recommended to enable same global statistics preferences in target ADB , however if you have implemented some specific global preferences to fix some complex issues in source database , you can enable them in target as well. Please note that you need to set them manually.

e. Column Usage information

This step is to explain how we had handled histograms with out column usage information .

There is no specific action taken in this step.

As you know column usage information is useful for histogram creation. We don’t have any column usage information in our target ADB for now , however this will not impact histogram creation in ALK (serverless and dedicated) and ATP serverless since histograms will be created for all columns during stats collection (due to preference set for METHOD_OPT).

For ATP-D:

Histograms are not created for all columns by default . We have ensured that the correct histograms are created by modifying the default method_opt ( covered in step c) temporarily.

What about dictionary stats ?

This is automatically collected by ADB and we don’t have to do anything manually.

Managing statistics during logical online migration

Recommendation is to exclude statistics during export and gather them at two different point in time during the migration.

Gather stats first after initial load and another one after completing Oracle GoldenGate replication.

Please follow below sequence for ATP and ALK (both serverless and dedicated).

a. Data Pump Export

It is not a good practice to bring the statistics from the source database during logical migration especially to Autonomous Database.

Ensure that we exclude statistics during DataPump Export using below for manual migration.

exclude=statistics

For migrations using Oracle Zero Downtime Migration , we don’t have to do anything since statistics are excluded by default.

Please note that this will exclude object statistics , statistics preference and columns usage information.

b. Disable auto task sub system (Optional , applicable for ATP-S and ATP-D)

This is an optional step recommended for very big databases where you expect the initial load to take more time.

Also this step is only required if your Data Pump Import is happening either partially or fully with in the standard maintenance window of database.

I would highly recommend to disable auto task jobs during DataPump Import since it doesn’t make sense to run auto tasks while the Import is going on.

Let’s check the current status

select status from DBA_AUTOTASK_STATUS;

Below is expected output.

STATUS 
-------     
ENABLED

Let’s disable auto task job using below SQL.

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE;
END;
/

Below is the current status.

STATUS 
-------     
DISABLED

c. Data Pump Import (Initial Load)

It is again not a good practice to collect stats during Import since only basic statistics can be gathered this way with potentially significant overhead during the process.

Please ensure statistics are not collected during Data Pump Import using below parameter for manual migration.

DATA_OPTIONS=DISABLE_STATS_GATHERING

We have noticed that above parameter doesn’t with 19c client (19.31 as well) and we could make it work with 23.26.2 client.

Please set below for Oracle Zero Downtime Migration.

DATAPUMPSETTINGS_DATAPUMPPARAMETERS_DISABLESTATSGATHERING=TRUE

d. Gather stats – First time

We have completed our initial load now .

Let’s proceed with stats collection for each of the schemas for all ADBs except ATP-D.

begin
dbms_stats.gather_schema_stats(
ownname => 'APP',
options => 'GATHER',
cascade => TRUE,
degree => DBMS_STATS.AUTO_DEGREE);
end;
/

This will also create histograms on all columns since by default global preference for METHOD_OPT is set to FOR ALL COLUMNS SIZE 254 for all ADBs except ATP-D.

I would recommend to modify the global preference (only for ATP-D) before stats collection so that histograms are created based on the data distribution.

Check the current preference .

SELECT DBMS_STATS.GET_PREFS('METHOD_OPT') AS method_opt FROM dual;

Below is the output from my environment.

METHOD_OPT
—————
FOR ALL COLUMNS SIZE AUTO

Modify the preference as below.

BEGIN
    DBMS_STATS.SET_GLOBAL_PREFS(
        pname => 'METHOD_OPT',
        pvalue => 'FOR ALL COLUMNS SIZE SKEWONLY'
    );
END;
/

e. Enable auto task sub system (Applicable for ATP-S and ATP-D)

We can enable auto task subsystem now if we had disabled it earlier (step b).

Use below command to enable.

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE;
END;
/

Check the current status using below query.

select status from DBA_AUTOTASK_STATUS;

Below is the expected output.

STATUS
——-
ENABLED

f. Gather stats -Second time

We should gather stats manually once more when we have completed Oracle GoldenGate replication.

This time we should collect stats using below SQL for all ADBs including ATP-D.

begin
dbms_stats.gather_schema_stats(
ownname => 'SH',
options => 'GATHER AUTO',
cascade => TRUE,
degree => DBMS_STATS.AUTO_DEGREE);
end;
/

This should not take more time since database gathers statistics only for objects Oracle determines need statistics.

Please note that we have not reverted the preference for METHOD_OPT in ATP-D.

Please remember to revert it after few days after go-live.

g. Statistics preferences

We have object statistics in place now , let’s see how to handle the stats preferences.

It is not recommended to blindly bring the non-global statistics preference from source to target unless there is a reason.

h. Column Usage Information

This step is to explain how we had handled histograms with out column usage information .

There is no specific action taken in this step.

As you know column usage information is useful for histogram creation.We don’t have any column usage information in our target ADB for now , however this will not impact histogram creation in ALK (serverless and dedicated) and ATP serverless since histograms will be created for all columns during stats collection (due to preference set for METHOD_OPT).

For ATP-D:

Histograms are not created for all columns by default . We have ensured that the correct histograms are created by modifying the default method_opt ( covered in step d) temporarily.

What about dictionary stats ?

This is automatically collected by ADB and we don’t have to do anything manually.

Managing statistics preference for critical databases

There are some databases where you always needs to be extra careful and any changes in statistics preference may have a big impact.

As mentioned earlier one of the best way to evaluate statistics preference is to test them against our workload.

Follow below sequence.

Perform Test Database Migration

Perform a test database migration using recent copy of Production Database.

You can either perform a logical offline migration or logical online migration for the test database migration.

Once we have completed test migration , we should use SQL Performance Analyzer to test the workload and make necessary changes to statistics preference if required.

Please refer Webinar for more details on how to use SQL Performance Analyzer.

Based on the testing , if the statistics preferences are still relevant we can make them available on the target production database by exporting the statistics preferences from Test Database and importing them to Production database using the below process.

Export statistics preference

All the tasks are done on the test Autonomous AI database.

i. Create a stage table.

Create a stage table for exporting statistics preference.

BEGIN
DBMS_STATS.CREATE_STAT_TABLE(
ownname => 'APP',
stattab => 'APP_PREF_STAT');
END;
/

We will refer the APP_PREF_STAT as stat table in later steps.

ii. Export statistics preference.

We can export schema or table preference based on our requirement.

Use below for exporting schema preference.

BEGIN
DBMS_STATS.EXPORT_SCHEMA_PREFS(
ownname => 'APP',
stattab => 'APP_PREF_STAT',
statid => 'APP_PREF',
statown => 'APP');
END;
/

This will pack our schema stats preference to the stat table (APP_PREF_STAT) specified.

We can use DBMS_STATS.EXPORT_TABLE_PREFS for specific table preferences.

iii. Perform Data Pump Export of the stat table.

We have the statistics preferences loaded to the stat table mentioned in earlier steps.

We can perform a Data Pump export of the stat table so that we can use it during our Production Migration.

Use something like below.

expdp ....tables=APP.APP_PREF_STAT

Import statistics preference

All the tasks are done on the Production Autonomous AI Database which we have migrated.

i. Perform Data Pump Import of the stat table.

We now have the statistics preferences from the test database as an export dumpfile.

Let’s perform the Data Pump Import to the actual production database.

impdp dumpfile=APP_PREF.STAT.dmp

ii. Import the statistics preference.

We can now unpack the schema statistics preference so that it is in effect on our Production database.

Use below sql.

BEGIN
  DBMS_STATS.IMPORT_SCHEMA_PREFS(
    ownname => 'APP',
    stattab => 'APP_PREF_STAT',
    statid  => 'APP_PREF'
  );
END;
/

Performance Stability

Database statistics plays a major role in ensuring plan stability , however statistics alone is not enough to ensure plan stability.

There are lot of other things required to have plan stability.

Please refer the webinar to understand how you can ensure plan stability for your databases.

Credits :

I would like to give credits to Mike Dietrich and Team since I have used some information from their webinar on statistics and migrations. Special thanks to Adrian Capitanu as well for his insights .

Lessons from my journey with databases