The following is a brief overview about the SQL Performance Analyzer (SPA) tool provided as part of the Oracle Real Application Testing option/feature.


Database Testing Guide

A big part of the job of a DBA is to make sure current production workload and SQL plans continue to run smoothly when a proposed or planned change is scheduled. Changes may include a database upgrade, adding a new index or changing a specific database parameter. The SPA tool is provided as a part of the Oracle Real Application Testing option and allows SQL from a production workload to be executed in a target database test environment so the results can be compared and any regressions can be identified and fix prior to a migration, upgrade or specific system change.  If you plan to use the Database Replay feature, then using SPA prior to running a replay is part of the Oracle recommended Best Practices.  The goal is to identify and fix all SQL regressions prior to database replay so we can focus on concurrency and throughput with the replay feature. SQL Performance Analyzer takes advantage of and uses SQL Tuning Sets (STS) as input, STS’s have been around for some time and this allows the DBA to take an existing production workload of SQL statements and easily compare a set of pre-change and post-change execution results saving time and resources. A SQL Tuning Set (STS) is a database object that contains a set of SQL statements from the workload, execution context information such as user and bind values, execution statistics and execution plans. For more information on STS’s, refer to:  Managing SQL Tuning Sets

NOTE: SQL Performance Analyzer requires the Oracle Real Application Testing license. For additional information, refer to:  Oracle Database Licensing Information.

The following list provides some of the most common scenarios where a DBA would think about using the SPA tool.

 

Usage Scenarios


1.    Database Upgrade – a new version of the database means a new version of the optimizer. The DBA can proactively determine any SQL regressions before upgrading the production systems.
2.    Deployment of a patch – You may be deploying a patch with specific fixes related to performance or the optimizer. Using SPA to test your production SQL workload can help validate the patch will not introduce any SQL regressions.
3.    Database initialization parameter changes - there are various database parameters that may impact performance so this is a good scenario where SPA can help.
4.    Schema changes such as adding an index – Making schema changes and modifying to adding indexes directly impacts optimizer decisions and plans. PA can be used to test these changes and make sure no negative impacts are introduced.
5.    Changing or refreshing Optimizer Statistics – Optimizer statistics have a direct bearing on decisions made by the optimizer and plans generated, you can use SPA to test new statistics and settings to make sure they do not introduce regressions.

Using SPA consists of executing the workflow document below. The SPA tool is fully integrated into Oracle 12c Cloud Control and Oracle also provides a PLSQL Package called DBMS_SQLPA that allows DBA’s to implement the steps using PL/SQL. The workflow uses an iterative process to execute, compare and analyze, and fix the regressions. DBA’s can use tools and features such as SQL Plan baselines or SQL Tuning advisor to fix bad or regressed SQL statements identified by SPA.

 

SPA Workflow
1.    Capture the SQL workload from the production system that you intend to analyze and store it in a SQL tuning set
2.    Setup the target test system (This should match the production system as close as possible)
3.    Create a SPA task on the test system

SPA_Workflow.png

4.    Build the pre-change SPA task
5.    Make the system change
6.    Build the post-change task
7.    Compare and Analyze the pre-change and post-change performance data
8.    Tune or fix any regressed SQL statements
9.    Repeat steps 6-8 until the SQL performance is acceptable in the test system

 

The following is a simple walk through where the system change is: “Schema Changes that will include the addition of an index to a table”.

•    The source database version is 12.1.0.2.0
•    The target test system is 12.1.0.2.0
•    The system change is the addition of an index to a test table called t1
•    The Performance report will be generated in HTML format with detailed information.

 

SPA – A Simple Walkthrough Using the PL/SQL API

NOTE: For additional information on the DBMS_SQLPA package and its usage, refer to:  Using DBMS_SQLPA

1.    Capture the SQL Workload in a SQL Tuning Set

Create and Populate the STS

BEGIN
  DBMS_SQLTUNE.DROP_SQLSET (sqlset_name  => 'MYSIMPLESTSUSINGAPI');
END;
/

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET (sqlset_name  => 'MYSIMPLESTSUSINGAPI', description  => 'My Simple STS Using the API' );
END;
/

1a. Run the following PLSQL code to execute SQL statement for SCOTT. (PLSQL to Simulate Workload of SQL Statement with Binds)
var b1 number;

declare
v_num number;
  begin
  for i in 1..10000 loop
  :b1 := i;
  select c1 into v_num from t1 where c1 = :b1;
  end loop;
end;
/

1b. Populate the STS from the cursor cache for parsing schema = SCOTT

DECLARE
  c_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN c_sqlarea_cursor FOR SELECT VALUE(p) FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name = ''SCOTT''', NULL, NULL, NULL, NULL, 1, NULL,'ALL')) p;
  DBMS_SQLTUNE.LOAD_SQLSET (sqlset_name => 'MYSIMPLESTSUSINGAPI', populate_cursor => c_sqlarea_cursor);
END;
/

1c. Check the STS for number of SQL statements captured:

COLUMN NAME FORMAT a20
COLUMN COUNT FORMAT 99999
COLUMN DESCRIPTION FORMAT a30

SELECT NAME, STATEMENT_COUNT AS "SQLCNT", DESCRIPTION FROM  USER_SQLSET;

Results:

NAME                    SQLCNT DESCRIPTION
-------------------- ---------- ------------------------------
MYSIMPLESTSUSINGAPI          12 My Simple STS Using the API

1d. A Query to display the contents of the STS

COLUMN SQL_TEXT FORMAT a30 
COLUMN SCH FORMAT a3
COLUMN ELAPSED FORMAT 999999999

SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCOTT", SQL_TEXT,  ELAPSED_TIME AS "ELAPSED", BUFFER_GETS FROM  TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'MYSIMPLESTSUSINGAPI' ) );

Results: (partial)

SQL_ID        SCOTT                          SQL_TEXT                                              ELAPSED                  BUFFER_GETS
------------- ------------------------------ ------------------------------                                  ----------                    -----------
0af4p26041xkv SCOTT                SELECT C1 FROM T1 WHERE C1 = :  169909252            18185689

 

2.  Setup the Target System

For demonstration purposes this simple walkthrough will use the same target test system as the source capture for the STS.

 

3.  Create the SPA Task

VARIABLE t_name VARCHAR2(100);
EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'MYSIMPLESTSUSINGAPI', task_name => 'MYSPATASKUSINGAPI');
print t_name

Results:

T_NAME
-----------------

MYSPATASKUSINGAPI

 

4.  Create and Execute the Pre-Change SPA Task

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'MYSPATASKUSINGAPI', execution_type => 'TEST EXECUTE', execution_name => 'MY_BEFORE_CHANGE');


5.  Make the System Change


CREATE INDEX t1_idx ON t1 (c1);

 

6.  Create and Execute the Post-Change SPA Task


EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'MYSPATASKUSINGAPI', execution_type => 'TEST EXECUTE', execution_name => 'MY_AFTER_CHANGE');


7.  Compare and Analyze the Performance from the Pre-Change and Post-Change Data

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'MYSPATASKUSINGAPI', execution_type => 'COMPARE PERFORMANCE', execution_name => 'MY_EXEC_COMPARE', execution_params => dbms_advisor.arglist('comparison_metric', 'elapsed_time'));

-- Generate the Report

set long 100000000 longchunksize 100000000 linesize 200 head off feedback off echo off TRIMSPOOL ON TRIM ON
VAR rep  CLOB;
EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK('MYSPATASKUSINGAPI', 'html', 'typical', 'all');
SPOOL C:\mydir\SPA_detailed.html
PRINT :rep
SPOOL off


Example Report in HTML Format:

The following is a screen shot of the summary portion of the SPA report. The report consists of three sections. The general information on the pre-change and post-change tasks which includes information such as the scope, status, execution start times, number of errors and comparison metric used. The summary section includes the project workload impact of the change. The third section contains details on the SQL ID(s) and metrics such as workload impact, execution frequency, the before metric and the after metric which in this simple test scenario was elapsed time. For this scenario, the workload impact for SQL ID 0af4p26041xkv was 97%. The change we are proposing has a positive impact and decrease the execution time from 12766 to 29. We can also see that a plan change did take place after our change. This information can be used by the DBA to drill down into and focus on any specific issues or regressions. In this case, it was an improvement.

 

SPA_Summary.png

 

The following is a screen shot of the details of the plans that changes  after making the system changed of adding our index. This section  allows the DBA to drill down into the specifics and details for the  execution plans for the pre-change and post-change execution tasks. For  any regressions this can help the DBA determine how the plan changed  and take action using tolls such as SQL Tuning Advisor or create a  baseline using SPM.

 

SPA_Details.png

 

List  of recommended resources for Real Application Testing:
   
•    Oracle  Real Application Testing Product Information
•    Master Note for Real  Application Testing Option (Doc  ID 1464274.1)
•    Database Testing: Best  Practices (Doc  ID 1535885.1)
•    Mandatory Patches for Database  Testing Functionality for Current and Earlier Releases (Doc  ID 560977.1)