24 Analyze Migrated Database Performance

Migrating a database can change the execution plans of SQL statements, resulting in a significant impact on SQL performance, resulting in performance degradation. SQL Performance Analyzer can review and help correct these issues. Migrating to Oracle Cloud changes the platform on which database workloads are running. Oracle Cloud Infrastructure provides a high performing platform to run a database workload.

SQL Performance Analyzer (SPA): Automates the process of assessing the overall effect of a change on the  SQL workload by identifying performance divergence for each SQL statement. A report that shows the net impact on workload performance due to the change is provided. For regressed SQL statements, SQL Performance Analyzer also provides appropriate executions plan details along with tuning recommendations. As a result, you can remedy any negative outcome before the end-users are affected. Furthermore, you can validate—with time and cost savings—that migration will result in net improvement.

An SQL Tuning Set (STS) containing the SQL and relevant execution metadata from the source database can be passed to the migration procedure.  A SPA task will be created which can then be used to compare the change in SQL Performance due to migration.

Should you need to use out-of-the-box performance management provided by the Autonomous Databases, Database Migration Workbench provides an option to verify performance by disabling indexes. You can then choose to disable indexes and let the Autonomous Database handle performance tuning. For more information see: Managing Auto Indexes in Oracle Database Administrator’s Guide .

View Migration Analysis

Follow these steps to view an analysis of a specific migration with Database Migration Workbench:

1. Navigate to the Migration Activities Dashboard.
2. Locate a recently completed Migration Activity you wish to review within the Migration Activity table.
3. Click on the Action Menu located at the far left of the specific Migration Activity you want to analyze and click on View Analysis.
4. In the View Analysis dashboard screen you can analyze the results from the three main steps:
   • Validation: Shows the results of the pre-migration checks.
   • Export: Shows the amount of successfully and unsuccessfully exported database objects.
   • Import: Shows the amount of successfully and unsuccessfully imported database objects.
   Underneath the analysis dashboards you can further drill down and analyze in detail each database object's performance during the migration by selecting clicking on Validation, Export and Import.

View Migration Analysis in REST API and EM CLI

To view the SPA Report after a migration use the following:

Note:

POST can be used if user wants the OMS to pull the files from the Host. The Host needs to be discovered in Enterprise Manager.

Request Method

POST

URI

/em/websvcs/restful/db/migration

Header

Content-Type: application/json
Authorization: Basic

Payload

{
    "action": "SPA_REPORT",
    "executionId": "<Execution ID>",
    "reportType": "html/text/active/xml",
    "reportLevel": "typical/all",
    "reportSection": "ALL/BASIC"
    "indexes": "YES/NO"
}

The Response will be in this format:

{    
    "report": "<Content of the SPA Report>"
}

To analyze a specific activity use the following:

Request Method

POST

URI

/em/websvcs/restful/db/migration

Header

Content-Type: application/json
Authorization: Basic

{
    "action": "ANALYZE_ACTIVITY", 
    "executionId": "<Execution_ID>"
}

To view Migration Analysis and SPA reports in EM CLI use the following command:

emcli migrate_db 
-file="/home/migrations/Analyze_Migration.JSON"

Where Analyze_Migration contains the JSON payload from the REST operations:

{
    "action": "SPA_REPORT",
    "executionId": "<Execution ID>",
    "reportType": "html/text/active/xml",
    "reportLevel": "typical/all",
    "reportSection": "ALL/BASIC"
    "indexes": "YES/NO"
}

{
    "action": "ANALYZE_ACTIVITY", 
    "executionId": "<Execution_ID>"
}