Sql Tuning Set (STS) creation in 19c

 Dear Readers,

In this blog, we will see how we can create a Sql Tuning Set in 19c Oracle database.

Procedure to create Sql Tuning Set

Step 1)
Ensure you have Tuning Pack licesnse.

Step 2)
We will use Pl/Sql API to create and load the sql tuning set. To create the STS...

Command:

BEGIN
  DBMS_SQLSET.CREATE_SQLSET (
    sqlset_name  => 'SQLT_WKLD_STS',description  => 'STS to store SQL from the private SQL area');
END;

Actual execution:

SQL> BEGIN
  DBMS_SQLSET.CREATE_SQLSET (
    sqlset_name  => 'SQLT_WKLD_STS',description  => 'STS to store SQL from the private SQL area');
END;  2    3    4
  5  /
PL/SQL procedure successfully completed.
SQL>

Step 3)
Select the sqls to be loaded into the STS.

Command:

DECLARE
  c_sqlarea_cursor DBMS_SQLSET.SQLSET_CURSOR;
BEGIN
 OPEN c_sqlarea_cursor FOR
   SELECT VALUE(p)
   FROM   TABLE( 
            DBMS_SQLSET.SELECT_WORKLOAD_REPOSITORY(
            begin_snap =>17
            ,end_snap=>18
            ,basic_filter => 
            ' module = ''wish8.6@10.0.2.15 (TNS V1-V3)'' ')
          ) p;
-- load the tuning set
  DBMS_SQLSET.LOAD_SQLSET (  
    sqlset_name     => 'SQLT_WKLD_STS'
,   populate_cursor =>  c_sqlarea_cursor 
);
END;
/

Actual execution:

SQL> DECLARE
  c_sqlarea_cursor DBMS_SQLSET.SQLSET_CURSOR;
BEGIN
 OPEN c_sqlarea_cursor FOR
   SELECT VALUE(p)
   FROM   TABLE(
            DBMS_SQLSET.SELECT_WORKLOAD_REPOSITORY(
            begin_snap =>17
            ,end_snap=>18
            ,basic_filter =>
            ' module = ''wish8.6@10.0.2.15 (TNS V1-V3)'' ')
          ) p;
-- load the tuning set
  DBMS_SQLSET.LOAD_SQLSET (
    sqlset_name     => 'SQLT_WKLD_STS'
,   populate_cursor =>  c_sqlarea_cursor
);
END;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19
PL/SQL procedure successfully completed.
SQL>

--> less than 1 minute for the above step.

Step 4)
Verify the loaded sqls.

COLUMN SQL_TEXT FORMAT a30   
COLUMN SCH FORMAT a3
COLUMN ELAPSED FORMAT 999999999
SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT, 
       ELAPSED_TIME AS "ELAPSED", BUFFER_GETS
FROM   TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'SQLT_WKLD_STS' ) )
order by 4;

>> by default it sorts in asc.

Now the result:

SQL_ID        SCH SQL_TEXT                          ELAPSED BUFFER_GETS
------------- --- ------------------------------ ---------- -----------
ap249zmtsh0hc TPC INSERT INTO new_order (no_o_id      75898        1775
              C   , no_d_id, no_w_id) values (:o
                  _id, :o_d_id, :o_w_i
70khh6rkrtwgj TPC INSERT INTO history (h_c_id, h     184359        5337
              C   _c_d_id, h_c_w_id, h_w_id, h_d
                  _id, h_date, h_amoun
...
aw9ttz9acxbc3 TPC BEGIN payment(:p_w_id,:p_d_id,    7997748      158122
              C   :p_c_w_id,:p_c_d_id,:p_c_id,:b
                  yname,:p_h_amount,:p
16dhat4ta7xs9 TPC begin neword(:no_w_id,:no_max_   12292007     1170785
              C   w_id,:no_d_id,:no_c_id,:no_o_o
                  l_cnt,:no_c_discount

27 rows selected.

If you look at the last row... comparing this aginsts AWR report....

        Elapsed                  Elapsed Time
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
            12.3         12,579          0.00    9.3   95.2     .3 16dhat4ta7xs9
Module: wish8.6@10.0.2.15 (TNS V1-V3)
begin neword(:no_w_id,:no_max_w_id,:no_d_id,:no_c_id,:no_o_ol_cnt,:no_c_discount
,:no_c_last,:no_c_credit,:no_d_tax,:no_w_tax,:no_d_next_o_id,TO_DATE(:timestamp,
'YYYYMMDDHH24MISS')); END;
             8.0         12,819          0.00    6.0   94.0     .0 aw9ttz9acxbc3
Module: wish8.6@10.0.2.15 (TNS V1-V3)
BEGIN payment(:p_w_id,:p_d_id,:p_c_w_id,:p_c_d_id,:p_c_id,:byname,:p_h_amount,:p
_c_last,:p_w_street_1,:p_w_street_2,:p_w_city,:p_w_state,:p_w_zip,:p_d_street_1,
:p_d_street_2,:p_d_city,:p_d_state,:p_d_zip,:p_c_first,:p_c_middle,:p_c_street_1
,:p_c_street_2,:p_c_city,:p_c_state,:p_c_zip,:p_c_phone,:p_c_since,:p_c_credit,:

You can notice the top 2 sqls by elapsed time shown in AWR are matching with our STS.

Thanks