[Oracle] How to execute a SQL Performance Analysis (SPA)

Hi all,

in this tutorial we are going to see how to generate a SQL performance analysis report before and after a change on the database (for example an upgrade) in order to analyze the differences.

1 – Baseline pre-change

First of all, a baseline of the database as-is needs to be generated. The first step is to create the sqlset with the following command:

EXEC DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'MY_SQLSET');

And decide which SQL statements to include in the sqlset. There are many ways to do that, two of them are mainly used:

• All the instructions executed between two database snapshots
• All the instructions executed by some database users with an elapsed time above some threshold, in order to exclude very fast queries:

Following there are the commands

##All queries between two snapshots
DECLARE 
mycur dbms_sqltune.sqlset_cursor; 
BEGIN 
open mycur for 
select value(p) from table(dbms_sqltune.select_workload_repository(BEGIN_SNAP,END_SNAP)) p; 
dbms_sqltune.load_sqlset('MY_SQLSET', mycur);
close mycur;
END; 
/

##All queries of MYUSER and elapsed_time>100
DECLARE
mycur dbms_sqltune.sqlset_cursor; 
BEGIN
open mycur for 
select value (p) from table(dbms_sqltune.select_cursor_cache('parsing_schema_name = ''MYUSER'' and elapsed_time > 100', null, null, null, null,1, null, 'ALL')) p;
dbms_sqltune.load_sqlset('MY_SQLSET', mycur);
close mycur; 
END; 
/ 

To do a reliable analysis, just delete and recalculate all the statistics of MYUSER if the second way of building the sqlset is chosen.

exec dbms_stats.DELETE_SCHEMA_STATS('MYUSER');
		 
exec DBMS_STATS.GATHER_SCHEMA_STATS (ownname => 'MYUSER', estimate_percent => dbms_stats.auto_sample_size, block_sample=> TRUE, method_opt => 'FOR ALL COLUMNS SIZE AUTO',degree => 8, granularity => 'ALL', cascade =>TRUE,options => 'GATHER AUTO');

Lastly, create and launch the analysis task:

var MY_TASK varchar2(30); 
var MY_SQLSET varchar2(30); 
exec :sname := 'MY_SQLSET'; 
exec :tname := 'MY_TASK';

##include all statements of MY_SQLSET
exec :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => :sname, task_name => :tname); 

##exclude all statements with less that 5 executions
exec :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => :sname, task_name => :tname, basic_filter => 'executions > 5'); 

##include only a specific SQL_ID
exec :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => :sname, task_name => :tname, basic_filter => 'MY_SQLID'); 

##include only a specific SQL_TEXT
exec :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => :sname, task_name => :tname, basic_filter => 'sql_text LIKE ''MY_SQLTEXT'''); 

##launch the analysis task
BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name       => :tname,
    execution_type  => 'test execute',
    execution_name  => 'baseline_pre_change');
END;
/

2 – Database change

In this step the database change is going to be performed. It can be a parameter change or a database upgrade. For example, follow this tutorial for a database upgrade from 12.1 to 12.2 version:

Upgrade ad Oracle database from 12.1 to 12.2

3 – Analysis post-change

After the database change, execute again the same task of step 1 with the following command:

var MY_TASK varchar2(30); 
var MY_SQLSET varchar2(30); 
exec :sname := 'MY_SQLSET'; 
exec :tname := 'MY_TASK';

BEGIN
  DBMS_SQLPA.execute_analysis_task(
    task_name       => :tname,
    execution_type  => 'test execute',
    execution_name  => 'baseline_after_change');
END;
/

4 – Regression report

Finally, to compare the two executions, use the following command to generate a HTML report:

var MY_TASK varchar2(30); 
var MY_SQLSET varchar2(30); 
exec :sname := 'MY_SQLSET'; 
exec :tname := 'MY_TASK';

##launch the compare task
BEGIN 
  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( 
	task_name => :tname, 
	execution_type => 'compare performance', 
	execution_name => 'compare elapsed time', 
	execution_params => dbms_advisor.arglist('execution_name1', 'baseline_pre_change', 'execution_name2', 'baseline_after_change', 'comparison_metric', 'elapsed_time') ); 
END; 
/

##generate the regression report
spool report_elapsed_time.html 
SELECT dbms_sqlpa.report_analysis_task(:tname, 'HTML', 'ALL','ALL', execution_name=>'compare elapsed time') FROM dual; 
spool off

The report can be easily analyzed since it is in HTML format.

That’s all, see you in the next tutorial!