28 | 03 | 2024
Latest Articles
Popular Articles

Performance Tuning

SQL Tuning Sets & SQL Performance Analyzer

User Rating:  / 0
PoorBest 

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

STS1a

 

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

STS2a

 

STEP 3 : Create a new SQL Tuning Set

STS3a

 

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

STS4a

STEP 5 : optionally apply filters at will

STS5a

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

STS6a

STEP 7 : Throw an eye on the DBMS_SQLTUNE syntax

STS7a

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

STS8a

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

STS9a

STEP 10 : View the sql statement in detail

STS10a

 

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

STS11a

STEP 12 : Select the parameter you want to change

STS12a

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

STS13a