Working with sql tuning set

Working with sql tuning set (create, update, taging. . .)

SH@ABC>BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET (
    sqlset_name  => 'SQLT_WKLD_STS'
,   description  => 'STS to store SQL form my work load' );
END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.
-- execute some sql
SELECT /* QuerySTS 2 */ ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND c.cust_state_province = 'CA' AND ch.channel_desc in ('Internet','Catalog') AND t.calendar_quarter_desc IN ('1999-01','1999-02') GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
--
SELECT /* QuerySTS 3 */ ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND c.cust_state_province = 'CA' AND ch.channel_desc in ('Internet','Catalog') AND t.calendar_quarter_desc IN ('1999-03','1999-04') GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
--
SELECT /* QuerySTS */ c.country_id, c.cust_city, c.cust_last_name FROM sh.customers c WHERE c.country_id in (52790, 52798) ORDER BY c.country_id, c.cust_city, c.cust_last_name;
--
SELECT /* QuerySTS 5 */ * FROM sh.customers jfv WHERE cust_state_province = 'CA';
--
select /*+ USE_NL(s c) FULL(s) FULL(c) QuerySTS 1 */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;-
- To load an STS:

DECLARE

  c_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR;

BEGIN

 OPEN c_sqlarea_cursor FOR

   SELECT VALUE(p)

   FROM   TABLE( 

            DBMS_SQLTUNE.SELECT_CURSOR_CACHE(

            ' SQL_TEXT LIKE ''%/* Query%'' AND parsing_schema_name = ''SH'' ')

          ) p;

-- load the tuning set

  DBMS_SQLTUNE.LOAD_SQLSET (  

    sqlset_name     => 'SQLT_WKLD_STS'

,   populate_cursor =>  c_sqlarea_cursor 

);

END;

/

--Displaying the Contents of a SQL Tuning Set

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' ) );

SQL_ID        SCH SQL_TEXT                          ELAPSED BUFFER_GETS

------------- --- ------------------------------ ---------- -----------

05k38z0jgnjsu SH   SELECT /*+ first_rows(1) */ s      14956          88

                  ql_id, force_matching_signatur

                  e, sql_text, cast(NU

0v2kp7dc0a4wh SH  SELECT VALUE(P) FROM TABLE( DB      32727         723

                  MS_SQLTUNE.SELECT_CURSOR_CACHE

                  ( 'sql_text like ''%

4q6xv2h4jj6xp SH  SELECT /* QuerySTS 3 */ ch.cha      64213        1827

                  nnel_class, c.cust_city, t.cal

                  endar_quarter_desc,

SQL_ID        SCH SQL_TEXT                          ELAPSED BUFFER_GETS

------------- --- ------------------------------ ---------- -----------

5xw5xq2csab3r SH  SELECT /* QuerySTS 2 */ ch.cha     101786        3958

                  nnel_class, c.cust_city, t.cal

                  endar_quarter_desc,

a1gyb3qpkj1mp SH  SELECT /* QuerySTS 5 */ * FROM      42352        2158

                   sh.customers jfv WHERE cust_s

                  tate_province = 'CA'

azs3qj5avy0zg SH  SELECT /* QuerySTS */ c.countr      57855        1533

                  y_id, c.cust_city, c.cust_last

SQL_ID        SCH SQL_TEXT                          ELAPSED BUFFER_GETS

------------- --- ------------------------------ ---------- -----------

                  _name FROM sh.custom

g7cfxhs6w9wfn SH  DECLARE                             78298        1115

                    c_sqlarea_cursor DBMS_SQLTUN

                  E.SQLSET_CURSOR;

                  BEGIN

                   OPEN c_sqlarea_cur

7 rows selected.

--Modifying a SQL Tuning Set

BEGIN

  DBMS_SQLTUNE.DELETE_SQLSET (

      sqlset_name  => 'SQLT_WKLD_STS'

,     basic_filter => 'sql_id in (''g7cfxhs6w9wfn'', ''05k38z0jgnjsu'')'

);

END;

/

-- Set attribute values for SQL statements.

BEGIN

  DBMS_SQLTUNE.UPDATE_SQLSET ( 

      sqlset_name     => 'SQLT_WKLD_STS'    

,     sql_id          => '5xw5xq2csab3r'    

,     attribute_name  => 'PRIORITY'         

,     attribute_value =>  1

);

END;

/

--

SELECT SQL_ID, ELAPSED_TIME, FETCHES, EXECUTIONS, PRIORITY

FROM   TABLE(DBMS_SQLTUNE.SELECT_SQLSET('SQLT_WKLD_STS'));

SQL_ID        ELAPSED_TIME    FETCHES EXECUTIONS   PRIORITY

------------- ------------ ---------- ---------- ----------

0v2kp7dc0a4wh        32727          5          2

4q6xv2h4jj6xp        64213          3          1

5xw5xq2csab3r       101786          3          1          1

a1gyb3qpkj1mp        42352        224          1

azs3qj5avy0zg        57855       1236          1