Hi,

I will explain What is the SQL Tuning Set and How to Create the SQL Tuning Sets in this article.

 

Oracle SQL Tuning Set ( STS )

SQL Tuning Sets are any database objects and sets of SQL Statements that serve as SQL repository. SQL Tuning sets are used as input for SQL Tuning Tools such as SQL Tuning Advisor and SQL Access Advisor.

 

 

 

You can read the following article to learn how to use SQL Tuning Sets in the SQL Tuning Advisor and SQL Access Advisor.

What is the SQL Access Advisor and How to Run SQL Access Advisor | Oracle Database Performance Tuning Tutorial -11

 

 

 

There are lots of informations for Tuning in the SQL Tuning Sets as follows.

• Set of SQL Statements that are collected via Cache or AWR
• SQL Execution Statistics such as CPU Time, Elapsed time, Buffer gets, Disk reads, rows processed, cursor fetches, the number of executions, the number of complete executions, optimizer cost
• Execution plans and row source statistics for each SQL statement

 

 

Create the SQL Tuning Sets in Oracle

You can create the SQL Tuning Sets by using Enterprise Manager and DBMS_SQLTUNE PL/SQL Package.

You can create the SQL Tuning Sets and Load SQL statements into SQL Tuning Sets by using Enterprise manager Cloud control as follows.

 

Step 1: Click Performance Tab and Click SQL Tuning Sets as follows.

 

 

Step 2: You can see all SQL Tuning Sets as follows.

You can create, drop , export the existing SQL Tuning sets in this page.

Click Create Button to Create a new SQL Tuning Set.

 

 

 

Step 3: Type SQL Tuning Set name, owner and Descripting in this step. Click Next button to continue.

 

Step 4: Pick Load SQL Methods into SQL Tuning Set in this Step.

You can set collection and capture process repeatedly by selecting first option.

Or you can load SQL Statements one time only by selecting second option as follows.

You can select data source from the following list.

• Cursor Cache
• AWR Snapshots
• AWR Baseline
• User-Defined Workload

 

 

 

 

Step 5: You can filter conditions for the SQL Statements as follows.

You can filter the SQL Statements according to Elapsed time, SQL ID, SQL Text and Schema Name as follows.

 

 

 

Step 6: SQL Tuning Set job will be created.

 

 

Step 7:  SQL Tuning Sets are created as follows. You can click the SQL Tuning Sets name to display all SQL Statements.

 

 

 

Step 8:  You can see all SQL Statements in the SQL Tuning Sets and its Execution Statistics (  Plan Hash Value, Parsing Schema, Executions, Elapsed Time, CPU Time, Buffer Gets, Disk Reads, Module ) as follows.

 

 

You can create SQL Tuning Sets by using DBMS_SQLTUNE package as follows.

BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'STS_TOP_SQL');
END;
/

 

Check SQL Tuning Sets if it exists or not.

SELECT name, owner FROM dba_sqlset WHERE name='STS_TOP_SQL';

 

 

 

Load SQL Statements into SQL Tuning Set from AWR Repository as follows.

DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.select_workload_repository (
27468, -- begin_snap
27650, -- end_snap
NULL, -- basic_filter
NULL, -- object_filter
NULL, -- ranking_measure1
NULL, -- ranking_measure2
NULL, -- ranking_measure3
NULL, -- result_percentage
10) -- result_limit
) p;

DBMS_SQLTUNE.load_sqlset (
sqlset_name => 'STS_TOP_SQL',
populate_cursor => l_cursor);
END;
/

 

Load SQL Statements into SQL Tuning Set from Cursor Cache as follows.

DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.select_cursor_cache (
NULL, -- basic_filter
NULL, -- object_filter
NULL, -- ranking_measure1
NULL, -- ranking_measure2
NULL, -- ranking_measure3
NULL, -- result_percentage
1) -- result_limit
) p;

DBMS_SQLTUNE.load_sqlset (
sqlset_name => 'STS_TOP_SQL',
populate_cursor => l_cursor);
END;
/

 

 

I will continue to explain Performance tuning tutorial in the next articles.

 

Next post link about Performance Tuning Tutorial is as follows.

What is the SQL Tuning Advisor and How to Run SQL Tuning Advisor Manually in Oracle | Oracle Database Performance Tuning Tutorial -10

 

 

 

Do you want to learn Top 30 SQL Tuning Tips and Tricks, then read the following articles.

 

SQL Tuning Tips and Tricks Tutorial in Oracle -3