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.
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.
Do you want to learn Top 30 SQL Tuning Tips and Tricks, then read the following articles.
One comment
Pingback: Performance Tuning and SQL Tuning Tutorial in the Oracle Database – SysDBASoft