Sql tuning sets 10g &11g

Sql Tuning Sets

Sql tuning sets is a basic building block for many oracle tuning features, built into the oracle 10g and 11g databases. A sql tuning set captures one or more sql statements issued by database users and their corresponding execution statistics (think, some of the statistics available in v$sqlarea),execution plans and row source statistics (think, some of the information from v$sql_plan etc), and execution context (think, bind varaibles etc…).

Sql tuning sets are used as inputs for the “Sql tuning advisor”. Sql tuning sets can be moved between databases. This comes in handy when you want to tune sql statements captured from a production environment in a development environment (So that you do not do development activities on production). sql tuning sets are used by the ‘sql performance analyzer’ component of ‘Real Application Testing’ and also the ‘sql plan stability’ feature.

Sql tuning sets can be created from the following four sources
– The database cursor cache (sqlarea)
– AWR reports
– An existing sql tuning set
– A sql trace file (generated using the 10046 database event)

Typically You would perform the following operations on a sql tuning set.
– Create a sql tuning set
– Add sql statements to the sql tuning set
– List the contents of a sql tuning set
– Modify a sql tuning set
– Transport a sql tuning set
– Drop a sql tuning set

 

Creating a sql tuning set

You can use the dbms_sqltune.create_sqlset procedure to create a new sql tuning set.
It takes 3 arguments.

DBMS_SQLTUNE.CREATE_SQLSET (
sqlset_name IN VARCHAR2,
description IN VARCHAR2 := NULL
sqlset_owner IN VARCHAR2 := NULL);

typically you can just specify the sqlset_name and a description, that lets you know why you created the sql tuning set in the first place.
Now i went ahead and used swingbench to run some transactions on the database for 10 minutes. This generates numerous sql statements in the shared pool.

 

Adding sql statements to a sql tuning set

Once we have run a load on the database, there will be sql statements in the shared pool that we want to analyze.
You can use the dbms_sqltune.load_sqlset procedure to popluate the sql tuning set with sql statements.

DBMS_SQLTUNE.LOAD_SQLSET (
sqlset_name IN VARCHAR2,
populate_cursor IN sqlset_cursor,
load_option IN VARCHAR2 := ‘INSERT’,
update_option IN VARCHAR2 := ‘REPLACE’,
update_condition IN VARCHAR2 := NULL,
update_attributes IN VARCHAR2 := NULL,
ignore_null IN BOOLEAN := TRUE,
commit_rows IN POSITIVE := NULL,
sqlset_owner IN VARCHAR2 := NULL);

For example you might want to create a sql tuning set with sql tuning statements that executed more than 5 times and had buffer_gets greater than 100000
So as you can see we used dbms_sqltune.select_cursor_cache to find the high resource using sql statements from the shared pool. So you have the following four different methods to get sql statements and populate the sql tuning set.
dbms_sqltune.select_cursor_cache
dbms_sqltune.select_sql_trace
dbms_sqltune.select_sql_set
dbms_sqltune.select_sql_workload_repository
All the above procedures return a table of object type sqlset_row

 

List the contents of a sql tuning set

You can list the statements that have been included in the sql tuning set by invoking the dbms_sqltune.select_sqlset procedure

 

Modify sql tuning sets

You can delete statements that have been included in the sql tuning set by invoking dbms_sqltune.delete_sqlset

You can update attributes of the sql tuning set by invoking dbms_sqltune.update_sqlset

 

Transporting a sql tuning set

If you need to move the sql tuning set from one database to another you can follow the procedure below
First create the sql tuning set staging table (This will be table where we temporarily store the information regarding the sql tuning set and then use exp/imp to move it to a different database).

dbms_sqltune.create_stgtab_sqlset
BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET( table_name => ‘my_first_stage_tab’ );
END;
/

Copy the sql tuning set into the staging table

dbms_sqltune.pack_stgtab_sqlset
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => ‘my_first_sqlset’,
staging_table_name => ‘my_first_stage_tab’);
END;
/

Now you can exp/imp this staging table from production to dev or test.
Once the staging table is in the destination database, you have to move the sql tuning set from the staging table into the data dictionary.

dbms_sqltune.unpack_stgtab_sqlset
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => ‘%’,
replace => TRUE,
staging_table_name => ‘my_first_stage_tab’);
END;
/

You can also use enterprise manager to perform all the above operations.