Oracle SQL Performance Analyzer (SPA) :

Predicting the Impact of Changes on SQL Workloads

Introduction

As a DBA, you often face a decision: the database needs to be upgraded, an index needs to be added, optimizer statistics need to be refreshed, or a parameter needs to be changed. These changes are usually meant to improve performance, but there is a risk you cannot ignore some SQL statements may slow down instead of speeding up because execution plans change. This is called SQL regression.

In the past, the only way was to change the test environment first and wait for problems to appear. SPA solves this problem. It allows you to run the complete SQL workload twice in a controlled environment once before the change and once after the change—and then automatically compare the performance of each SQL statement, telling you which statements got faster, which got slower, and by how much .

SPA is part of Oracle Real Application Testing (RAT). Its core value is: before end users are affected, you already know whether a change should be made .

Theoretical Foundation

How SPA Works

SPA does not require you to modify application code or wait for production problems to occur. Its workflow consists of four steps:

1. Capture workload: Collect a representative set of SQL statements from the source database (for example, from AWR or cursor cache) and store them in a SQL Tuning Set (STS).

2. Build a “pre-change” trial: Execute this STS on the test system before the change to record performance baselines for each statement (execution plan, elapsed time, CPU, buffer gets, etc.).

3. Apply the change: For example, upgrade the database, add an index, change parameters, or refresh statistics.

4. Build a “post-change” trial and compare: Execute the same STS again on the changed environment, then SPA generates a comparison report showing which SQL statements changed in performance, which execution plans changed, and which statements show regressions .

Two Execution Modes :

SPA provides two trial modes; the choice depends on how accurate you need the results to be and how much time you can invest :

If you choose “Generate Plans,” the comparison metric can only use Optimizer Cost because there are no actual runtime statistics. If you choose “Execute SQLs,” you can compare Elapsed Time, CPU Time, Buffer Gets, Physical I/O, and other metrics .

Relationship with Previous Topics :

In simple terms: ADDM tells you “where the problem is now,” SPA tells you “whether the change will cause problems,” and SPM tells you “after the change is made, don’t let plans drift randomly.”

Step 1 – Prepare a SQL Tuning Set (STS) :

SPA’s input is an STS containing a representative SQL workload. You can capture it from AWR or directly from the cursor cache.

Capture from Cursor Cache (Applicable to OLTP Workloads)

-- Create an STS
BEGIN
DBMS_SQLSET.CREATE_SQLSET(
sqlset_name => 'SPA_TEST_STS',
description => 'SQL workload for SPA test'
);
END;
/

-- Load high-load SQL from cursor cache
DECLARE
l_cur DBMS_SQLSET.SQLSET_CURSOR;
BEGIN
OPEN l_cur FOR
SELECT sql_id, sql_text, parsing_schema_name, elapsed_time, cpu_time, buffer_gets, disk_reads, executions FROM v$sql WHERE parsing_schema_name
 = 'YOUR_SCHEMA' AND executions > 100 AND sql_text NOT LIKE '%v$%';

DBMS_SQLSET.LOAD_SQLSET(
sqlset_name => 'SPA_TEST_STS',
populate_cursor => l_cur
);
CLOSE l_cur;
END;
/

Theoretical Note :

The quality of the STS determines the value of the SPA report. If you only include a few SQL statements that are not representative, the conclusions will not be meaningful. It is recommended to capture the Top SQL by resource consumption from AWR or cursor cache, or use a complete STS captured by Database Replay .

Step 2 – Create an SPA Analysis Task :

SQL Command :

VARIABLE task_name VARCHAR2(30);
EXEC :task_name := 'SPA_INDEX_CHANGE_01';

BEGIN
DBMS_SQLPA.CREATE_ANALYSIS_TASK(
task_name => :task_name,
sqlset_name => 'SPA_TEST_STS',
order_by => 'BUFFER_GETS',
description => 'Test impact of adding index on ORDERS table'
);
END;
/

Theoretical Note :

ORDER_BY determines the priority order for executing SQL statements. If you set it to BUFFER_GETS, SPA will prioritize statements with high logical reads. You can also use ELAPSED_TIME or CPU_TIME .

Step 3 – Build a “Pre-Change” Trial :

Option A: Execute Statements (Accurate Mode) :

BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_INDEX_CHANGE_01',
execution_type => 'TEST EXECUTE',
execution_name => 'before_change_trial'
);
END;
/

Option B: Generate Plans Only (Fast Mode) :

BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_INDEX_CHANGE_01',
execution_type => 'TEST EXECUTE',
execution_name => 'before_change_trial',
execution_params => DBMS_ADVISOR.ARGLIST(
'generate_plan_only', 'TRUE'
)
);
END;
/

Theoretical Note :

If you choose accurate mode, SPA will execute each statement multiple times by default (typically once to warm the cache, then multiple times to average) to reduce the impact of buffer cache on results . This means you need enough temporary tablespace and undo space on the test system, because DML statements will actually modify data (unless you configure them not to).

Step 4 – Apply the Change :

This step is not a SQL command but a manual operation. You apply the change you want to test on the test database:

• Add an index

• Change an initialization parameter

• Refresh optimizer statistics

• Upgrade the database version

• Change the schema

The key point is: The change must be applied to the same test system, then a second trial must be run . If you switch systems, ensure the environment is as consistent as possible, otherwise the comparison results will include noise from environmental differences.

Step 5 – Build a “Post-Change” Trial :

BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_INDEX_CHANGE_01',
execution_type => 'TEST EXECUTE',
execution_name => 'after_change_trial'
);
END;
/

Note: The parameters for this trial should be the same as the first trial (for example, both use Execute SQLs or both use Generate Plans), otherwise the comparison will be meaningless.

Step 6 – Execute Comparison Analysis :

SQL Command :

BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_INDEX_CHANGE_01',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'compare_result',
execution_params => DBMS_ADVISOR.ARGLIST(
'execution_name1', 'before_change_trial',
'execution_name2', 'after_change_trial',
'comparison_metric', 'BUFFER_GETS'
)
);
END;
/

Theoretical Note :

COMPARISON_METRIC determines which metric you use to judge “faster or slower.” Common choices include:

• ELAPSED_TIME: Most intuitive, but susceptible to system load fluctuations

• CPU_TIME: More stable, reflects actual computational cost

• BUFFER_GETS: Reflects logical read changes, suitable for I/O-sensitive workloads

• PHYSICAL_IO: Suitable for testing I/O-related changes

Suggestion: Do not look at just one metric. If after adding an index BUFFER_GETS decreases significantly but ELAPSED_TIME increases, it may be because index maintenance overhead offsets the read benefit. Multiple comparisons reveal the full picture.

Step 7 – Read the SPA Report :

Generate the Report :

SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_INDEX_CHANGE_01') FROM dual;

Key Sections of the Report :

1. Overall Performance Statistics

This section tells you: the overall workload performance improved or degraded by what percentage after the change. For example, the report might show: overall improved by 47.94%, but regressed SQL statements brought a negative impact of -10.08%. This means if you fix the regressed statements, the overall improvement could reach 58.02% .

2. Regressed SQL Statements List

This is the most important section. SPA lists each SQL statement that became slower after the change, along with:

• SQL ID and SQL text

• Execution plan before and after the change

• Performance metric difference (for example, BUFFER_GETS increased by X%)

• Optimization suggestions for the regressed statements

3. Improved SQL Statements List

Statements that got faster after the change, to verify the change achieved its intended effect.

4. Errors and Missing Statements

Some statements may error during execution (for example, due to missing objects or permission issues). In migration scenarios (such as migrating to Autonomous Database), the error count can be quite high and requires separate analysis .

Step 8 – Handle Regressed Statements :

After identifying regressed SQL, SPA itself provides tuning suggestions, but does not automatically fix them. You have several options:

Option A: Use SQL Plan Management (SPM) to Fix the Plan :

If the regression is caused by a plan change, you can load the old plan from AWR as a baseline, or use SPM to fix the pre-change plan .

Option B: Run SQL Tuning Advisor :

SPA reports typically include recommendations to run SQL Tuning Advisor on regressed statements. After generating a profile or index suggestion, you can apply it to the test system, then rerun SPA to verify the effect.

Option C: Adjust the Change Strategy :

For example, if adding an index causes some statements to regress, you might need to adjust the index design (change the column order, use a composite index, or create a function-based index). After modification, rerun the comparison.

Theoretical Note :

SPA’s value lies not only in “discovering problems” but also in “verifying fixes.” After you apply a fix, simply run the post-change trial and comparison again—no need to start the entire process from scratch. This makes SPA an iterative tool .

Practical Considerations :

Licensing :

SPA is part of Oracle Real Application Testing. On on-premises databases, RAT requires an additional license . However, if you are using Oracle Autonomous Database (ADB), RAT is included and requires no extra purchase. This is also an advantage of migrating to ADB .

Test Environment Consistency :

The more consistent the test environment is with production, the more reliable the SPA results. It is recommended to run the pre-change and post-change trials on the same test system to avoid noise introduced by different system configurations .

Triggers and SYSDATE :

By default, SPA executes triggers during trial execution. If triggers contain DML operations, they may affect the test results or cause side effects. You can disable triggers with the following command:

EXEC DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
task_name => 'SPA_INDEX_CHANGE_01',
parameter => 'EXECUTE_TRIGGERS',
value => 'FALSE'
);

For statements containing SYSDATE, you can use the REPLACE_SYSDATE_WITH parameter to fix the date value, avoiding result differences caused by different execution times .

Time Control :

If a statement runs for a very long time, SPA may stall. You can set a time limit per statement (for example, 5 minutes), but this sacrifices accuracy .

Summary :

Closing :

The relationship between SPA and the previous two topics can be summarized in one sentence:

ADDM tells you “the database is slow now,” SPA tells you “the database might slow down after the change,” SPM ensures “after the change is made, the database won’t slow down unexpectedly.”

Together, these three form a complete closed loop of performance management: discover problems → predict risks → solidify results. If you are preparing for a database upgrade, migration, or large-scale change, SPA is one of the most worthwhile tools to invest time in learning.