Sql Tuning Sets

Using SQL tuning sets we can group statements and related metadata into a single object, which can further be used as input to SQL tuning tools like SQL Tuning Advisor, SQL Access Advisor, 
and SQL Performance Analyzer.

The database stores SQL tuning sets in a database-provided schema. An STS includes:
A set of SQL statements
Associated execution context, such as user schema, application module name and action, list of bind values, and the environment for  SQL compilation of the cursor
Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, the number of executions, 
the number of complete executions, optimizer cost, and the command type
Associated execution plans and row source statistics for each SQL statement (optional)

Source for SQL TUNING SETS
To create an STS, you must load SQL statements into an STS from a source.
The source can be Automatic Workload Repository (AWR), the shared SQL area, customized SQL provided by the user, trace files, or another STS.




Tool for SQL TUNING SET.

On the command line, you can use the DBMS_SQLTUNE or DBMS_SQLSET packages to manage SQL tuning sets.

BASIC TASK FOR STS Operations

Below mentioned table is SQLSET_ROW which will be used for filter operations.

We will perform step by step for each task as below.

1=> Create SQL TUNING SET

To create STS we will use DBMS_SQLSET PACKAGES as below.

SQL> BEGIN
  DBMS_SQLSET.CREATE_SQLSET (sqlset_name  => 'SQLT_TEST',   description  => 'STS FOR TESTING' );
  END;
/
PL/SQL procedure successfully completed.

Verify if STS created

SELECT NAME, STATEMENT_COUNT AS "SQLCNT", DESCRIPTION FROM   USER_SQLSET;

NAME                                   SQLCNT DESCRIPTION
-------------------- ---------- --------------------------------------------------
SQLT_TEST                                0 STS FOR TESTING

2=> Load SQL TUNING SET (Using DBMS_SQLSET.LOAD_SQLSET)

This procedure populates the SQL tuning set with a set of selected SQL statements.We can call the procedure multiple times to add new SQL statements/replace attributes of existing statements.

We can create SQL tuning sets by filtering or ranking SQL statements from several sources:

1=> the shared SQL area using the SELECT_CURSOR_CACHE Function
2=> Top SQL statements from the Automatic Workload Repository using the SELECT_WORKLOAD_REPOSITORY Function
3=> Other SQL tuning sets using the SELECT_SQLSET Function
4=> SQL Performance Analyzer task comparison results using the SELECT_SQLPA_TASK Function
5=> SQL Trace files using the SELECT_SQL_TRACE Function
6=> A user-defined workload

A => Load SQL TUNING SET using SELECT_CURSOR_CACHE Function(From Cursor)

-> Load SQL_ID in tuning Set as below.

First let we get complete details for SQL_ID using below command.
Setp 1->  select /*+ sts_developer */ * from DEPT1;

Setp 2-> select sql_text,sql_id from v$sql where sql_text like '%sts_developer%';

SQL_TEXT                                                                         SQL_ID
------------------------------------------------------------ --------------------
select /*+ sts_developer */ * from DEPT1              1gt8sxyxgk87u

Setp 3-> SELECT * FROM TABLE(DBMS_SQLSET.SELECT_CURSOR_CACHE('sql_id = ''1gt8sxyxgk87u'''));

This Query plays major part in STS. Using this Query we will  be able to identify filters as below.



Now we will filter with Module and Buffer gets and SQLID AS BELOW as then load in STS.

SQL> BEGIN
 DBMS_SQLSET.CREATE_SQLSET (sqlset_name  =>'SQLT_FROM_CURSOR',   description  => 'STS FROM CURSOR' );
  END;
/

SQL> DECLARE
  c_sqlarea_cursor DBMS_SQLSET.SQLSET_CURSOR;
BEGIN
OPEN c_sqlarea_cursor FOR
   SELECT VALUE(p)
   FROM   TABLE(
            DBMS_SQLSET.SELECT_CURSOR_CACHE(
            ' module = ''SQL Developer'' AND parsing_schema_name = ''ABHI_TEST'' and sql_id = ''1gt8sxyxgk87u''')
          ) p;
-- load the tuning set
 DBMS_SQLSET.LOAD_SQLSET ( 
    sqlset_name     => 'SQLT_FROM_CURSOR'
,   populate_cursor =>  c_sqlarea_cursor
);
END;
/ 
PL/SQL procedure successfully completed.

SQL>

Setp 4-> Get details for STS which has been loaded as below

COLUMN SQL_TEXT FORMAT a30  
COLUMN SCH FORMAT a3
COLUMN ELAPSED FORMAT 999999999

SELECT * FROM   TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'SQLT_FROM_CURSOR' ) );



B => Load SQL TUNING SET using SELECT_WORKLOAD_REPOSITORY Function( From AWR)

We will create STS from AWR as below.

Let assume below SQLID is captured in AWR tool. So BEFORE proceeding let we get complete information for That SQLID.

SNAP ID=> 189
Sql id => 7f8uq94a7mbr2

col parsed format a6
col sql_text format a40
set lines 200
set pages 300

select     
 sql_text,
 parsing_schema_name as parsed,
 elapsed_time_delta/1000/1000 as elapsed_sec,
 stat.snap_id,
 to_char(snap.end_interval_time,'dd.mm hh24:mi:ss') as snaptime,
 txt.sql_id
from     
 dba_hist_sqlstat stat,
 dba_hist_sqltext txt,
 dba_hist_snapshot snap
where     
 stat.sql_id=txt.sql_id and
 stat.snap_id=snap.snap_id and
 --snap.begin_interval_time>=sysdate-10  and
 --lower(sql_text) like '%&t%' and
 parsing_schema_name in ('ABHI_TEST')
order by snaptime asc;

 
Create Blank STS and LOAD STS as below.

SQL>
SQL> BEGIN
  DBMS_SQLSET.CREATE_SQLSET (
    sqlset_name  => 'SQLT_FROM_AWR'
,   description  => 'STS From awr'
);
END;
/

DECLARE
  cur DBMS_SQLSET.SQLSET_CURSOR;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
      FROM table(
                DBMS_SQLSET.SELECT_WORKLOAD_REPOSITORY(189,194,
                                                'parsing_schema_name =''ABHI_TEST'' and sql_id = ''7f8uq94a7mbr2''')) P;

  DBMS_SQLSET.LOAD_SQLSET(sqlset_name     => 'SQLT_FROM_AWR',
                          populate_cursor => cur );
END;
/
PL/SQL procedure successfully completed.

SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13  
PL/SQL procedure successfully completed.
SQL >
Get details for loaded STS as below

SELECT * FROM   TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'SQLT_FROM_AWR' ) );



C=> Load SQL TUNING SET from another SQLSET using the SELECT_SQLSET Function

As WE already have STS as per above example. Let we load the same from below package.

SQL> BEGIN
  DBMS_SQLSET.CREATE_SQLSET (
    sqlset_name  => 'SQLT_FROM_SQLT'
,   description  => 'STS From awr'
);
END;
/
PL/SQL procedure successfully completed.

SQL> DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE (P)
    FROM table(DBMS_SQLSET.SELECT_SQLSET('SQLT_FROM_CURSOR',' module = ''SQL Developer'' AND parsing_schema_name = ''ABHI_TEST'' and sql_id = ''1gt8sxyxgk87u''')) P;
    
      DBMS_SQLSET.LOAD_SQLSET(sqlset_name     => 'SQLT_FROM_SQLT',
                          populate_cursor => cur );
END;  2    3    4    5    6    7    8    9   10  
 11  
PL/SQL procedure successfully completed.
SQL>

D=> Loading SQL TUNING SET using trace file.

This table function reads the content of one or more trace files and returns the SQL statements it finds in the format of sqlset_row.

This function requires a mapping table for translation of trace file.

Below are steps used to load STS from trace file.

Steps 1=> Set trace and generate trace as below.

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';

Session altered.

SQL> SELECT 1 FROM DUAL;
     1
-----------------------
     1

SQL> SELECT COUNT(*) FROM dba_tables
  COUNT(*)
----------
      2220

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

Session altered.

Steps 2=> Mapping table creation as below.

CREATE TABLE mapping AS
SELECT object_id id, owner, substr(object_name, 1, 30) name
   FROM dba_objects
   WHERE object_type NOT IN ('CONSUMER GROUP', 'EVALUATION CONTEXT',
                             'FUNCTION', 'INDEXTYPE', 'JAVA CLASS',
                             'JAVA DATA', 'JAVA RESOURCE', 'LIBRARY',
                             'LOB', 'OPERATOR', 'PACKAGE',
                             'PACKAGE BODY', 'PROCEDURE', 'QUEUE',
                             'RESOURCE PLAN', 'TRIGGER', 'TYPE',
                             'TYPE BODY')
UNION ALL
SELECT user_id id, username owner, NULL name
   FROM dba_users;

SQL> SQL>
SQL> desc mapping
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                            NUMBER
 OWNER                            VARCHAR2(128)
 NAME                            VARCHAR2(120)

SQL> select count(*) from mapping;

  COUNT(*)
----------
     25393

Steps 3=> Getting trace file location and creating directory for trace.

[oracle@ace2oracledb ~]$ cd /oracle_home/app/oracle/diag/rdbms/stdb/STDB/trace/
[oracle@ace2oracledb trace]$
 [oracle@ace2oracledb trace]$ ls –lrt STDB_ora_23400.trc

[oracle@ace2oracledb trace]$ cp STDB_ora_23400.trc /tmp/

SQL>  CREATE DIRECTORY  LOAD_STS_TRACE as '/tmp';

Directory created.

Steps 4=> Creating STS and Loading STS using trace file.
SQL>
DECLARE
   cur sys_refcursor;
BEGIN
   OPEN cur FOR
   SELECT value(p)
     FROM TABLE(
        DBMS_SQLSET.SELECT_SQL_TRACE(
           directory=>'LOAD_STS_TRACE',
           file_name=>'%trc',
           mapping_table_name=>'mapping',
           mapping_table_owner=>'SYS')) p;
   DBMS_SQLSET.LOAD_SQLSET('SQLT_FROM_TRACE', cur);
   CLOSE cur;
END;
/
PL/SQL procedure successfully completed.

SQL>

Steps 5=> Querying details for Loaded STS as below.

SELECT * FROM   TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'SQLT_FROM_TRACE' ) );



E=> Loading SQL TUNING SET using SQL Query.(A user-defined workload)

In this part we will load Sql Tuning Sets using SQL Query and this is called user-defined load.I am assuming to load 3 SQL Queries in STS
and will use filter from SQLSET_ROW as below.

Step 1=> Connect to SH schema and create a sample source table ,that will be used to store info.

SQL> CREATE TABLE user_workload_testing
(
username             varchar2(128),  /* User who executes statement */
module               varchar2(64),       /* Application module name */
action               varchar2(64),       /* Application action name */
elapsed_time         number,              /* Elapsed time for query */
cpu_time             number,                  /* CPU time for query * /
buffer_gets          number,       /* Buffer gets consumed by query */
disk_reads           number,        /* Disk reads consumed by query */
rows_processed       number,        /* # of rows processed by query */
executions           number,           /* # of times query executed */
optimizer_cost       number,            /* Optimizer cost for query */
priority             number,            /* User-priority (1,2 or 3) */
last_execution_date  date,              /* Last time query executed */
stat_period          number,         /* Window exec time in seconds */
sql_text             clob                          /* Full SQL Text */
);
Table created.
SQL>

Step 2=> Populate testing tables with data as below for SQL_TEXTS.

-- aggregation with selection
INSERT INTO user_workload_testing (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
'SELECT   t.week_ending_day, p.prod_subcategory,
          SUM(s.amount_sold) AS dollars, s.channel_id, s.promo_id
 FROM     sales s, times t, products p
 WHERE    s.time_id = t.time_id
 AND      s.prod_id = p.prod_id
 AND      s.prod_id > 10
 AND      s.prod_id < 50
 GROUP BY t.week_ending_day, p.prod_subcategory, s.channel_id, s.promo_id')
/
 
-- aggregation with selection
INSERT INTO user_workload_testing (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
 'SELECT   t.calendar_month_desc, SUM(s.amount_sold) AS dollars
  FROM     sales s , times t
  WHERE    s.time_id = t.time_id
  AND      s.time_id BETWEEN TO_DATE(''01-JAN-2000'', ''DD-MON-YYYY'')
  AND      TO_DATE(''01-JUL-2000'', ''DD-MON-YYYY'')
  GROUP BY t.calendar_month_desc')
/
 
-- order by
INSERT INTO user_workload_testing (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
 'SELECT   c.country_id, c.cust_city, c.cust_last_name
  FROM     customers c
  WHERE    c.country_id IN (52790, 52789)
  ORDER BY c.country_id, c.cust_city, c.cust_last_name')
/
COMMIT;

1 row created.
1 row created.
1 row created.
Commit complete.

Step 3=> Create Blank STS as below.

SQL>  BEGIN
  DBMS_SQLSET.CREATE_SQLSET (sqlset_name  => 'STS_FROM_QUERY' ,description  => 'Creating STS from Query'
);END;
/
PL/SQL procedure successfully completed.
SQL>

Step 4=> Load SQL TUNING SET as below using SQLSET_ROW

DECLARE
  sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN sqlset_cur FOR
    SELECT SQLSET_ROW(null,null, SQL_TEXT, null, null, 'SH', module,
                     'Action', 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, null, 2, 3,
                     sysdate, 0, 0, null, 0, null, null)
    FROM sh.user_workload_testing;
  DBMS_SQLTUNE.LOAD_SQLSET('STS_FROM_QUERY', sqlset_cur);
END;
/
PL/SQL procedure successfully completed.

Step 5=> Verify if STS created with Queries as below

SELECT NAME, STATEMENT_COUNT AS "SQLCNT", DESCRIPTION FROM   USER_SQLSET;

NAME --------SQLCNT--- DESCRIPTION
---------------------------------------------------
STS_FROM_QUERY 3 Creating STS from Query

3=> Dropping a SQL Tuning Set Using DROP_SQLSET

To drop an STS from the database, use the DROP_SQLSET procedure in the DBMS_SQLTUNE or DBMS_SQLSET package.
SQL> BEGIN
  DBMS_SQLSET.DROP_SQLSET( sqlset_name => 'SQLT_FROM_AWR' );
END;
/
PL/SQL procedure successfully completed.
SQL>

Major Procedure/package/Functions/Views used for STS.

CREATE_SQLSET Procedure and Function
LOAD_SQLSET Procedure
-SELECT_CURSOR_CACHE Function
-SELECT_SQL_TRACE Function
-SELECT_SQLSET Function
-SELECT_WORKLOAD_REPOSITORY Function
DROP_SQLSET Procedure
USER_SQLSET View