Autonomous Dedicated and SQL Performance Analyzer: Through good SQL and bad

More and more customers are looking to move into the Oracle Cloud, and many of them want to understand what Autonomous Databases can do for them but worry that performance may be affected. With that in mind, I decided to write a blog post about using SQL Performance Analyzer (SPA) to verify performance is as good or better when moving from on-prem (or anywhere else) into an Autonomous Database running on dedicated infrastructure. SQL Performance Analyzer (SPA) allows us to create a known set of SQLs from our "working" environment, replay those SQLs on our new environment, and generate a comparison report. This is also a great tool to run on QA/Staging environments, where patches can be tested before they are applied in production.

After moving to an Autonomous Database, I can run my SQL set any time to confirm that performance of the Autonomous Database is as expected or if there is a known change to the environment I want to validate on my mission-critical databases. Let me show you how I can run this analysis with only a little bit of setup.

I am going to use a 12.1.0.2 database running on-prem with traditional SAN storage as my source. The workload we will use to generate our SQL Tuning set will be from Swingbench.

The target database will be a 19c Autonomous Database (ADB) running on an Autonomous Dedicated Infrastructure (ADB-D) in OCI. First, we need to work in the 12.1.0.2 database and generate the SQL Tuning set we will use for our comparison. Log into your source system as a privileged user and run the following after updating the filtering as needed:

exec DBMS_SQLTUNE.create_sqlset(sqlset_name => '12c_19c_spa_test');

PL/SQL procedure successfully completed.

DECLARE

    l_cursor  DBMS_SQLTUNE.sqlset_cursor;

 BEGIN

  OPEN l_cursor FOR

     SELECT VALUE(a) FROM   TABLE(

     DBMS_SQLTUNE.select_cursor_cache(

     basic_filter=> 'upper(SQL_TEXT) not like

     ''%SQL_ANALYZE%''

     and upper(SQL_TEXT) not like ''%BEGIN%''

     and upper(SQL_TEXT) not like ''%DBMS_SQLTUNE%''

     and upper(SQL_TEXT) like ''%SELECT%''

     and upper(parsing_schema_name) = ''SOE''',

     attribute_list => 'ALL')) a;

     DBMS_SQLTUNE.load_sqlset(sqlset_name=>

     '12c_19c_spa_test',populate_cursor => l_cursor);

 END;

 / 

PL/SQL procedure successfully completed.

Next, we need to create the staging table which we will use to move this SQL tuning set to our Autonomous Database:

BEGIN

DBMS_SQLTUNE.create_stgtab_sqlset (table_name => 'STG_TABLE',

schema_name => 'SOE',tablespace_name =>'SOE');

END;

/

PL/SQL procedure successfully completed.

After we have created the staging table, we can pack our tuning set into it using:

BEGIN

DBMS_SQLTUNE.pack_stgtab_sqlset (sqlset_name => '12c_19c_spa_test',

sqlset_owner => 'SYS', staging_table_name => 'STG_TABLE',

staging_schema_owner => 'SOE'

);

END;

/

PL/SQL procedure successfully completed.

Taking a quick peek at our staging table, I can see we have 18 unique SQLs to use for our test:

select distinct(sql_id) from SOE.STG_TABLE;

SQL_ID

-------------

5ckxyqfvu60pj

…..

7hk2m2702ua0g

18 rows selected.

Once we have the staging table populated, we need to move this table into our Autonomous Database. I will use Data Pump to facilitate the move:

expdp soe@pdb121 

tables=STG_TABLE

directory=STG_DIR 

dumpfile=STG_TABLE.dmp 

logfile=STG_TBL.log

After we have the dump file available, we need to upload it into OCI Object Storage, so we can ingest it into Autonomous Database.

Now that we have done our prep work, we can move over to the Autonomous Database and get everything ready to run SPA. The following assumptions and work must be done beforehand: (1) Have access to an Autonomous Dedicated Infrastructure and create an Autonomous Database; and (2) Load schema(s)/application data from the source system (the closer the data matches where the STS came from, the better our analysis will be).

First, we need to import the SQL tuning set staging table from our 12c database. I chose to use SQL Developer Data Pump import wizard (with saved OCI credentials and a link to my dmp file in object storage), but you could also use mv2adb (Doc ID 2463574.1) or straight impdp

After the table is imported, we need to unpack it into a SQL tuning set.

BEGIN

DBMS_SQLTUNE.unpack_stgtab_sqlset (sqlset_name => '12c_19c_spa_test',

sqlset_owner => 'SYS',

replace => TRUE,

staging_table_name => 'STG_TABLE',

staging_schema_owner => 'SOE');

END;

/

PL/SQL procedure successfully completed.

Phew! Prep work completed on both systems, and we are ready to run SPA as many times as needed to test our upgrade to Autonomous.

We can use the CONVERT_SQLSET execution type of DBMS_SQLPA.EXECUTE_ANALYSIS_TASK to convert our SQL tuning set into a “before run” for our 12c timings, generate the 19c timings, and run the comparison using:

VARIABLE spa_job VARCHAR2(64);

PL/SQL procedure successfully completed.

EXEC :spa_job :=  DBMS_SQLPA.create_analysis_task(sqlset_owner => 'SYS', sqlset_name => '12c_19c_spa_test');

PL/SQL procedure successfully completed.

PRINT :spa_job

SPA_JOB

--------------------------------------------------------------------------------

TASK_135

begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(

task_name => :spa_job,

execution_type => 'CONVERT SQLSET' ,

execution_name => '12c_preupgrade');

end;

/

PL/SQL procedure successfully completed.

BEGIN

DBMS_SQLPA.execute_analysis_task(

task_name       => :spa_job,

execution_type  => 'TEST EXECUTE',

execution_name  => '19c_adb');

END;

/

PL/SQL procedure successfully completed.

BEGIN

DBMS_SQLPA.execute_analysis_task(

task_name        => 'TASK_135',

execution_type   => 'compare performance', 

execution_params => dbms_advisor.arglist(

'execution_name1', 

'12c_preupgrade', 

'execution_name2', 

'19c_adb',

'workload_impact_threshold', 0, 

'sql_impact_threshold', 0));

END;

/

PL/SQL procedure successfully completed.

After the analysis is done, we can generate the report and look over the results using:

SET TRIM ON

SET TRIMSPOOL ON

SET PAGESIZE 0

SET LINESIZE 1000

SET LONG 5000000

SET LONGCHUNKSIZE 5000000

SPOOL /tmp/spa_active_report.html

SELECT DBMS_SQLPA.report_analysis_task(:spa_job, 'ACTIVE', 'ALL') FROM dual;

SPOOL OFF

Here is an example screenshot of the report from our workload. As you can see, we had a few plan changes when upgrading versions. Overall performance was better on most SQL!

Here is an example of a report where a SQL has a plan change and performance has regressed:

I can look down the report and find the regressed SQL and click into the SQLID and find out exactly what has changed and why. The drilldown will report metric changes between the query and list the findings of what has changed.

Now that I have my STS in the Autonomous Database, I can start using it anytime there are system changes or if I want to start testing out some of the Autonomous features!

One last thing...While SPA is an Oracle feature add-on, the license is included with the Autonomous Dedicated OCPUs. So get out there and start using it.!

One more last thing…If you are an Enterprise Manager 13.4+ user there is a guided workflow named “Migrate to Oracle Autonomous Database” which guides you through the process of testing an on-prem SQL tuning set on an Autonomous Database Link