A database change can make some statements faster and others slower. A useful change assessment preserves comparable inputs, identifies each statement across the trials, measures performance and correct results, and records an application acceptance decision.
Oracle DBA Lesson 103 Part 2 — Assess SQL before and after a change
These commands target Oracle Database 19c. SQL Performance Analyzer and DBMS_SQLPA require the applicable Real Application Testing rights. Package availability alone does not establish entitlement. The package's analysis-task interfaces require ADVISOR. Access to objects, SQL tuning sets, and relevant views has separate requirements. Managing an owned tuning set generally needs ADMINISTER SQL TUNING SET, with broader privileges for another owner's set.
Oracle 19c documents certain Enterprise Edition SQL tuning set interfaces without requiring Tuning Pack for every such use. AWR-based sources require applicable Diagnostics Pack rights, and advisor or profile remediation may require Tuning Pack. Confirm the actual interface, offering, and contract. If SPA is unavailable or unentitled, use the SQL*Plus comparison below without accessing DBMS_SQLPA. See Licensing Information and managing SQL tuning sets.
What makes a comparison useful
Start with the question: how will a particular optimizer setting, statistics change, or upgrade affect the SQL that matters? Record the original configuration and a tested backout before changing the clone. Limit a small experiment to one defined change. An upgrade changes many internals together. Treat that upgrade as the defined change and record other differences rather than attributing every result to one optimizer behavior.
Keep SQL text, important bind values, parsing schema, representative data, and unrelated settings stable. Include typical, high-volume, and business-critical statements. Record workload frequency and bind-value coverage. A single convenient input value can hide skew or a poor plan for a different part of the data.
Use a consistent warm-up method, repeated comparable runs, and recorded resource conditions. Background work, cache state, concurrency, hardware differences, and network fetch behavior can change elapsed time. If those factors vary materially, document the limitation or repeat under controlled conditions before attributing a difference to the test change.
Match each statement and its relevant context between the before and after trials. Compare the same metric with the same units. Investigate elapsed time together with CPU, logical reads, execution errors, and the actual plan when available. An unchanged plan can perform differently under changed conditions. A changed plan deserves evaluation through measured work and response time.
SQL Performance Analyzer and application testing
SQL Performance Analyzer (SPA) organizes SQL trials and comparisons. A task holds the input workload and its analysis executions. In a test-execute trial, it runs the supplied statements separately, collects plans and runtime statistics, and compares performance across conditions. It does not preserve the statements' original order or concurrency. Documented warm-up and repeated executions improve SQL measurement, but they do not recreate concurrent application behavior.
By default, SPA executes only the query portion of DML. Its EXECUTE_FULLDML setting can enable full DML execution, including row locking and modification, followed by rollback. Review that behavior and all possible side effects before selecting it. Rollback is not a general reset for external actions. The API example below keeps the default. Do not infer transaction or write-path acceptance from those trials.
An explain-plan trial produces plans and optimizer cost estimates. Use test-execute evidence when the question requires runtime measurements. A cost estimate and an observed elapsed duration have different units and meanings.
Database Replay addresses captured database workload behavior with timing and concurrency controls. Its setup, capture restrictions, and external dependencies still need review. Complete application testing must cover end-to-end correctness, concurrent transactions, contention, transaction ordering, and external service calls. A successful SPA comparison supports the SQL decision. Application acceptance requires these additional tests. See SQL Performance Analyzer and Database Replay.
Create a named SPA comparison
The SQL tuning set ORDERS_CHANGE_01 must already contain representative statements, bind values, parsing context, and frequency, and remain stable during the comparison. Create the uniquely named task and collect the baseline:
VARIABLE change_task VARCHAR2(128)
BEGIN
:change_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
sqlset_name => 'ORDERS_CHANGE_01',
task_name => 'SPA_CHANGE_01');
END;
/
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_CHANGE_01',
execution_type => 'TEST EXECUTE',
execution_name => 'BEFORE');
END;
/VARIABLE declares a SQL*Plus bind variable for the returned task name. CREATE_ANALYSIS_TASK associates the analysis with the existing tuning set. TEST EXECUTE collects execution plans and runtime statistics under the trial environment. Review execution errors, coverage, and completion before proceeding. See creating a pre-change SQL trial.
Apply only the defined test change using its recorded procedure. Record exactly what changed and how it will be restored. The appropriate optimizer setting and scope depend on the experiment. If testing an environment setting, review APPLY_CAPTURED_COMPILENV deliberately: restoring a captured compilation environment could override the setting being tested. Its documented default is false. Review execution limits and DML behavior as part of the trial configuration.
Collect the after trial:
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_CHANGE_01',
execution_type => 'TEST EXECUTE',
execution_name => 'AFTER');
END;
/See creating a post-change SQL trial.
Name the two inputs and metric explicitly when comparing them:
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_CHANGE_01',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'COMPARE_ELAPSED',
execution_params => DBMS_ADVISOR.ARGLIST(
'execution_name1', 'BEFORE',
'execution_name2', 'AFTER',
'comparison_metric', 'elapsed_time'));
END;
/execution_name1 identifies the baseline and execution_name2 identifies the post-change trial. comparison_metric sets the basis of classification. Naming both trials avoids accidentally comparing the last two executions after retries or additional tests. Use unique execution names for later repeats and reference their exact identities. Review the configured SQL and workload impact thresholds when interpreting an unchanged classification. See comparing SQL trials and DBMS_SQLPA.
Export a report before cleanup. In SQL*Plus, choose a new local output filename and suitable CLOB display limits:
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET PAGESIZE 0
SET LINESIZE 200
SET TRIMSPOOL ON
SPOOL spa_change_01_elapsed.txt
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(
task_name => 'SPA_CHANGE_01',
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL',
execution_name => 'COMPARE_ELAPSED')
FROM dual;
SPOOL OFFThe report function returns a CLOB. SQL*Plus settings affect how much is displayed and spooled. Verify that the exported file contains the intended complete report. Review summary and individual statements, errors and omissions, plans, metric direction, workload impact, and regression details. An execution that failed or timed out requires investigation. Classify it separately from a successful comparable run. See SQL*Plus SET commands.
Interpreting a regression
Elapsed time is in milliseconds per execution:
| Statement | Before | After | Change |
|---|---|---|---|
| Customer lookup | 20 | 10 | Faster by 10 ms |
| Report | 200 | 500 | Slower by 300 ms |
If there were 100 lookups and one report, the total would fall from 2,200 ms to 1,500 ms. That sum describes the selected SQL work at those frequencies. It is not the wall-clock duration of concurrent application traffic. The report is still slower and might miss a business deadline. Frequency-weighted benefit and critical-statement acceptance both matter.
For an actual regression, check the same SQL, context, and binds, the input data, execution errors, actual plan, and resource measurements. Investigate changed access paths or cardinality estimates when relevant, and also consider unchanged plans affected by resource conditions. Repeat comparable runs. After a remedy, retain the evidence and rerun the relevant SQL and application tests.
Compare with a SQL*Plus script
Save the same versioned script and run it before and after the defined change:
SET TIMING ON
SELECT customer_id, COUNT(*), SUM(amount)
FROM orders
GROUP BY customer_id ORDER BY customer_id;
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ORDER BY department_id;
SET TIMING OFFSET TIMING ON reports elapsed duration for the executed statements. Fully fetch the results using the same client conditions and compare the actual result sets as well as timing. The ordering makes the grouped rows easier to compare, but result validation must account for actual types, nulls, formatting, rounding, and expected semantics.
The script adds no bind parameters. If the selected workload uses binds, preserve their types and representative values, or version each planned bind case. Record output and measured results in separate baseline and after logs without overwriting either. Use comparable warm-up and repeated runs. Account for background load and the client and network effect on elapsed duration.
Where permitted, add GATHER_PLAN_STATISTICS to a selected query, fully fetch it, and inspect the correct executed cursor using DBMS_XPLAN.DISPLAY_CURSOR with ALLSTATS LAST. Capture the exact SQL ID and child cursor if intervening client SQL may change the last cursor. Collected runtime statistics and access privileges are prerequisites. Record actual logical reads, CPU, and elapsed measurements from approved sources. SQL*Plus timing alone does not supply all of them. See DBMS_XPLAN.
Drop the analysis task
Retain the required report and evidence, then remove only the uniquely created task:
EXEC DBMS_SQLPA.DROP_ANALYSIS_TASK('SPA_CHANGE_01');Dropping the task deletes its result data. It does not reverse the optimizer setting, upgrade, or other test change. Restore that change through its own recorded backout. Remove a captured tuning set only when it was created for this exercise, has no remaining references or consumers, and its retention rules permit removal. Preserve shared or pre-existing sets.
Recap
A useful before/after comparison keeps SQL text, important bind values, parsing schema, representative data, and unrelated settings stable, and records one defined change. Match each statement and its context between trials. Compare the same metric in the same units. Read elapsed time together with CPU, logical reads, execution errors, and the actual plan. Warm-up, repeated runs, cache state, concurrency, and hardware can change elapsed time. Document those limits or repeat under controlled conditions.
SQL Performance Analyzer organizes SQL trials. A test-execute trial runs statements separately and collects plans and runtime statistics. It does not preserve original order or concurrency. By default it executes only the query portion of DML. An explain-plan trial supplies plans and cost estimates, which are different units from observed elapsed time. Database Replay addresses captured workload timing and concurrency. Application acceptance still needs end-to-end correctness, contention, transaction ordering, and external calls.
Create task SPA_CHANGE_01 from tuning set ORDERS_CHANGE_01. Collect named BEFORE and AFTER test-execute trials, then compare them with comparison_metric elapsed_time. Naming both executions avoids comparing the last two after retries. Export the COMPARE_ELAPSED report before cleanup. A frequency-weighted total can improve while a critical statement becomes slower. Both the workload sum and the business deadline matter. If SPA is unavailable, run the same versioned SQL*Plus script before and after the change, fully fetch the results, and compare result sets as well as SET TIMING ON durations. Add GATHER_PLAN_STATISTICS and DBMS_XPLAN.DISPLAY_CURSOR with ALLSTATS LAST when you need actual cursor statistics.
Retain the report, then drop only the created task. Dropping the task deletes its result data and does not reverse the test change. Restore that change through its own recorded backout. Remove a captured tuning set only when it was created for this exercise and retention rules permit it.
No comments:
Post a Comment