How do you use Oracle’s SQL Performance Analyzer (SPA) to evaluate the impact of system changes on SQL performance? — Oracle Database Interview Question #76

Oracle SQL Performance Analyzer (SPA) is a tool that helps you to evaluate the performance of SQL statements before and after implementing system changes, such as database upgrades, parameter changes or schema modifications. SPA can also be used to compare the performance of the same SQL statement on different systems or with different execution plans, thus helping to identify the optimal execution path.

The main steps to use SPA are:

1. Create a SQL Tuning Set (STS): an object that collects SQL statements to be evaluated via the SQL Performance Analyzer. STS can be created from AWR (Automatic Workload Repository) snapshots, SQL trace files or directly from a SQL statement.

-- Creating SQL Tuning Set using SQL Statement
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'my_sqlset', description => 'My Tuning Set');
  
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_sqlset', populate_cursor => TRUE,
  sql_text => 'SELECT /*+ FULL(t) */ COUNT(*) FROM mytable t');
END;
/

2. Create a SQL Performance Analyzer task: a task that contains the system changes to be evaluated and the SQL Tuning Set to be tested. The task can also include execution options such as concurrency, degree of parallelism and optimization level.

-- Creating a SQL Performance Analyzer task
BEGIN
  DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'my_sqlset', task_name => 'my_task',
  description => 'My SPA Task', execution_type => DBMS_SQLPA.EXECUTION_TYPE_STANDALONE,
  execution_name => 'SYS_AUTO_SQL_TUNING_TASK', execution_params => NULL);
END;
/

3. Execute the SQL Performance Analyzer task: this will compare the performance of the SQL statements in the Tuning Set before and after the system changes. The results of the analysis are stored in the SQL Performance Analyzer repository and can be accessed through the DBA_SQLSET_STATEMENTS view.

-- Executing a SQL Performance Analyzer task
BEGIN
  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_task');
END;
/

4. Analyze the SQL Performance Analyzer results: the SPA results will show you the execution statistics for each SQL statement in the Tuning Set before and after the system changes, including the execution time, CPU time, buffer gets and disk reads.

-- Analyzing SQL Performance Analyzer results
SELECT sql_id, sql_text, execution_time_diff, buffer_gets_diff, disk_reads_diff
FROM dba_sqlset_statements
WHERE task_name='my_task'
ORDER BY execution_time_diff DESC;

By using SQL Performance Analyzer, you can quickly evaluate the impact of system changes on SQL performance, and identify any regression issues. SPA can also help you to identify the best execution plan for a given SQL statement, and validate the performance benefits of such plan changes.