[Oracle] How to execute a SQL Performance Analysis (SPA)
ENVIRONMENT:
- Red Hat Linux 7
- Oracle Database 11/12
VARIABLES:
- MY_SQLSET: set of SQL statements to be executed during the test
- MY_TASK: database task that handles the execution of all instructions belonging to MY_SQLSET
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!