19 Managing SQL Tuning Sets

Execute the DBMS_SQLTUNE.CREATE_SQLSET procedure to create an empty STS in the database. Using the function instead of the procedure causes the database to generate a name for the STS.

Table 19-1 describes some procedure parameters. See Oracle Database PL/SQL Packages and Types Reference for complete reference information.

Assumptions

This tutorial assumes that you want to create an STS named SQLT_WKLD_STS.

To create an STS:

1. Connect SQL*Plus to the database with the appropriate privileges, and then run the DBMS_SQLTUNE.CREATE_SQLSET procedure.

   For example, execute the following PL/SQL program:

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

2. Optionally, confirm that the STS was created.

   The following example queries the status of all SQL tuning sets owned by the current user:

   COLUMN NAME FORMAT a20
   COLUMN COUNT FORMAT 99999
   COLUMN DESCRIPTION FORMAT a30
   
   SELECT NAME, STATEMENT_COUNT AS "SQLCNT", DESCRIPTION
   FROM   USER_SQLSET;
   

   Sample output appears below:

   NAME                 SQLCNT DESCRIPTION
   -------------------- ------ ------------------------------
   SQLT_WKLD_STS             2 SQL Cache
   

To load an STS with SQL statements, execute the DBMS_SQLTUNE.LOAD_SQLSET procedure. The standard sources for populating an STS are AWR, another STS, or the shared SQL area. For both the workload repository and SQL tuning sets, predefined table functions can select columns from the source to populate a new STS.

Table 19-2 describes some DBMS_SQLTUNE.LOAD_SQLSET procedure parameters. See Oracle Database PL/SQL Packages and Types Reference for complete reference information.

The DBMS_SQLTUNE.SELECT_CURSOR_CACHE function collects SQL statements from the shared SQL area according to the specified filter. This function returns one SQLSET_ROW per SQL ID or PLAN_HASH_VALUE pair found in each data source.

Use the CAPTURE_CURSOR_CACHE_SQLSET function to repeatedly poll the shared SQL area over a specified interval. This function is more efficient than repeatedly calling the SELECT_CURSOR_CACHE and LOAD_SQLSET procedures. This function effectively captures the entire workload, as opposed to the AWR, which only captures the workload of high-load SQL statements, or the LOAD_SQLSET procedure, which accesses the data source only once.

Prerequisites

This tutorial has the following prerequisites:

• Filters provided to the SELECT_CURSOR_CACHE function are evaluated as part of SQL statements run by the current user. As such, they are executed with that user's security privileges and can contain any constructs and subqueries that user can access, but no more.

• The current user must have privileges on the shared SQL area views.

Assumptions

This tutorial assumes that you want to load the SQL tuning set named SQLT_WKLD_STS with statements from the shared SQL area.

To load an STS:

1. Connect SQL*Plus to the database as a user with the appropriate privileges.

2. Run the DBMS_SQLTUNE.LOAD_SQLSET procedure.

   For example, execute the following PL/SQL program to populate a SQL tuning set with all cursor cache statements that belong to the sh schema:

   DECLARE
     c_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
   BEGIN
    OPEN c_sqlarea_cursor FOR
      SELECT VALUE(p)
      FROM   TABLE( 
               DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
               ' module = ''SQLT_WKLD'' 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;
   /
   

After an STS has been created and populated, execute the DBMS_SQLTUNE.SELECT_SQLSET function to read the contents of the STS, optionally using filtering criteria.

You select the output of SELECT_SQLSET using a PL/SQL pipelined table function, which accepts a collection of rows as input. You invoke the table function as the operand of the table operator in the FROM list of a SELECT statement.

Table 19-3 describes some SELECT_SQLSET function parameters. See Oracle Database PL/SQL Packages and Types Reference for complete reference information.

Table 19-4 describes some attributes of the SQLSET_ROW object. These attributes appears as columns when you query TABLE(DBMS_SQLTUNE.SELECT_SQLSET()).

Assumptions

This tutorial assumes that you want to display the contents of an STS named SQLT_WKLD_STS.

To display the contents of an STS:

1. Connect SQL*Plus to the database with the appropriate privileges, and then query the STS contents using the TABLE function.

   For example, execute the following query:

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

   Sample output appears below:

   SQL_ID        SCH SQL_TEXT                          ELAPSED BUFFER_GETS
   ------------- --- ------------------------------ ---------- -----------
   79f8shn041a1f SH  select * from sales where quan    8373148       24016
                     tity_sold < 5 union select * f
                     rom sales where quantity_sold
                     > 500
    
   2cqsw036j5u7r SH  select promo_name, count(*) c     3557373         309
                     from promotions p, sales s whe
                     re s.promo_id = p.promo_id and
                      p.promo_category = 'internet'
                      group by p.promo_name order b
                     y c desc
    
   fudq5z56g642p SH  select sum(quantity_sold) from    4787891       12118
                      sales s, products p where s.p
                     rod_id = p.prod_id and s.amoun
                     t_sold > 20000 and p.prod_name
                      = 'Linen Big Shirt'
    
   bzmnj0nbvmz8t SH  select * from sales where amou     442355       15281
                     nt_sold = 4
   

2. Optionally, filter the results based on user-specific criteria.

   The following example displays statements with a disk reads to buffer gets ratio greater than or equal to 50%:

   COLUMN SQL_TEXT FORMAT a30   
   COLUMN SCH FORMAT a3
   COLUMN BUF_GETS FORMAT 99999999
   COLUMN DISK_READS FORMAT 99999999
   COLUMN %_DISK FORMAT 9999.99
   SELECT sql_id, parsing_schema_name as "SCH", sql_text, 
          buffer_gets as "B_GETS",
          disk_reads, ROUND(disk_reads/buffer_gets*100,2) "%_DISK"
   FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 
               'SQLT_WKLD_STS',
               '(disk_reads/buffer_gets) >= 0.50' ) );
   

   Sample output appears below:

   SQL_ID        SCH SQL_TEXT                       B_GETS DISK_READS %_DISK
   ------------- --- ------------------------------ ------ ---------- -------
   79f8shn041a1f SH  select * from sales where quan  24016      17287  71.98
                     tity_sold < 5 union select * f
                     rom sales where quantity_sold
                     > 500
    
   fudq5z56g642p SH  select sum(quantity_sold) from  12118       6355  52.44
                      sales s, products p where s.p
                     rod_id = p.prod_id and s.amoun
                     t_sold > 20000 and p.prod_name
                      = 'Linen Big Shirt'
   

Use the DBMS_SQLTUNE.DELETE_SQLSET procedure to delete SQL statements from an STS. You can use the UPDATE_SQLSET procedure to update the attributes of SQL statements (such as PRIORITY or OTHER) in an existing STS identified by STS name and SQL ID. See Oracle Database PL/SQL Packages and Types Reference for more information.

Assumptions

This tutorial assumes that you want to modify SQLT_WKLD_STS as follows:

• You want to delete all SQL statements with fetch counts over 100.

• You want to change the priority of the SQL statement with ID fudq5z56g642p to 1. You can use priority as a ranking criteria when running SQL Tuning Advisor.

To modify the contents of an STS:

1. Connect SQL*Plus to the database with the appropriate privileges, and then optionally query the STS contents using the TABLE function.

   For example, execute the following query:

   SELECT SQL_ID, ELAPSED_TIME, FETCHES, EXECUTIONS
   FROM   TABLE(DBMS_SQLTUNE.SELECT_SQLSET('SQLT_WKLD_STS'));
   

   Sample output appears below:

   SQL_ID        ELAPSED_TIME    FETCHES EXECUTIONS
   ------------- ------------ ---------- ----------
   2cqsw036j5u7r      3407459          2          1
   79f8shn041a1f      9453965      61258          1
   bzmnj0nbvmz8t       401869          1          1
   fudq5z56g642p      5300264          1          1
   

2. Delete SQL statements based on user-specified criteria.

   Use the basic_filter predicate to filter the SQL from the STS defined on attributes of the SQLSET_ROW. The following example deletes all statements in the STS with fetch counts over 100:

   BEGIN
     DBMS_SQLTUNE.DELETE_SQLSET (
         sqlset_name  => 'SQLT_WKLD_STS'
   ,     basic_filter => 'fetches > 100'
   );
   END;
   /
   

3. Set attribute values for SQL statements.

   The following example sets the priority of statement 2cqsw036j5u7r to 1:

   BEGIN
     DBMS_SQLTUNE.UPDATE_SQLSET ( 
         sqlset_name     => 'SQLT_WKLD_STS'    
   ,     sql_id          => '2cqsw036j5u7r'    
   ,     attribute_name  => 'PRIORITY'         
   ,     attribute_value =>  1
   );
   END;
   /
   

4. Optionally, query the STS to confirm that the intended modifications were made.

   For example, execute the following query:

   SELECT SQL_ID, ELAPSED_TIME, FETCHES, EXECUTIONS, PRIORITY
   FROM   TABLE(DBMS_SQLTUNE.SELECT_SQLSET('SQLT_WKLD_STS'));
   

   Sample output appears below:

   SQL_ID        ELAPSED_TIME    FETCHES EXECUTIONS   PRIORITY
   ------------- ------------ ---------- ---------- ----------
   2cqsw036j5u7r      3407459          2          1          1
   bzmnj0nbvmz8t       401869          1          1
   fudq5z56g642p      5300264          1          1
   

Execute the DBMS_SQLTUNE.DROP_SQLSET procedure to drop an STS from the database.

Prerequisites

Ensure that no tuning task is currently using the STS to be dropped. If a tuning task exists that is using this STS, then drop the task before dropping the STS. Otherwise, the database issues an ORA-13757 error.

Assumptions

This tutorial assumes that you want to drop an STS named SQLT_WKLD_STS.

To drop an STS:

1. Connect SQL*Plus to the database with the appropriate privileges, and then run the DBMS_SQLTUNE.DROP_SQLSET procedure.

   For example, execute the following PL/SQL program:

   BEGIN
     DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'SQLT_WKLD_STS' );
   END;
   /
   

2. Optionally, confirm that the STS was deleted.

   The following example counts the number of SQL tuning sets named SQLT_WKLD_STS owned by the current user (sample output included):

   SELECT COUNT(*) 
   FROM   USER_SQLSET 
   WHERE  NAME = 'SQLT_WKLD_STS';
   

     COUNT(*)
   ----------
            0