04 | 03 | 2021
Latest Articles
Popular Articles

Performance Tuning

SQL Tuning Sets & SQL Performance Analyzer

User Rating:  / 0

SQL Tuning Sets & SQL Performance Analyzer

A SQL Tuning Set --- STS --- is a set of sql statements together with their processing context.


The input sources of a STS are


1. Individual SQL statements grouped together by the DBA

2. The cursor cache



3. AWR statistical data ( snapshots , baselines )

4. A combination of cursor cache and some individual statements added

5. A user defined workload (Statements inserted in a workload table)


The idea is that we package some SQL toghether with execution statistics. We have something changed in the processing context ( change parameters ) have the STS executed again and have a comparison done. Is the new processing context better, faster ?

The package dbms_sqltune can be used to create, update, delete SQL tuning Sets. Alternatively the Database Control offers us a grafical interface.


STEP 1 : Consider the performance TAB of the Database Control



STEP 2 : On the performance tab click on SQL Tuning Sets



STEP 3 : Create a new SQL Tuning Set



STEP 4 : Select the data source : Cursor Cache, AWR snapshots, AWR Baseline or a User Defined Workload


STEP 5 : optionally apply filters at will


STEP 6 : Create a SQL Tuning Set as a scheduled task and have it executed


STEP 7 : Throw an eye on the DBMS_SQLTUNE syntax


STEP 8 : Note that once the SQL Tuning Set has been created it can be used as an input source for the SQL Access Advisor or the SQL Tuning Advisor. We select here the option SQL Performance Analyzer


STEP 9 : drill down and add optionally other SQL statements, remove optionally some SQL statements


STEP 10 : View the sql statement in detail



STEP 11 : Once the SQL Performance Analyzer chosen there is the option to choose between Parameter Change or Guided Workflow


STEP 12 : Select the parameter you want to change


STEP 13 :  Take a look to the comparison reports after execution. you can choose several comparisons ( buffer gets, elapsed time, disk reads, ... )