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.
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.
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
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 184.108.40.206.0
• The target test system is 220.127.116.11.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
DBMS_SQLTUNE.DROP_SQLSET (sqlset_name => 'MYSIMPLESTSUSINGAPI');
DBMS_SQLTUNE.CREATE_SQLSET (sqlset_name => 'MYSIMPLESTSUSINGAPI', description => 'My Simple STS Using the API' );
1a. Run the following PLSQL code to execute SQL statement for SCOTT. (PLSQL to Simulate Workload of SQL Statement with Binds)
var b1 number;
for i in 1..10000 loop
:b1 := i;
select c1 into v_num from t1 where c1 = :b1;
1b. Populate the STS from the cursor cache for parsing schema = SCOTT
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);
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;
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' ) );
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');
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');
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.
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.
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)