Hi John,

Below are the answers to you questions regarding SQL Tuning Sets.


1. Where are SQL Tuning Sets stored?

SQL Tuning Sets are stored in the sysaux tablespace, in an area called SQL_MANAGEMENT_BASE, just like SQL Profiles and SQL Plan Baselines. They're stored in the various WRI$_* tables.

2. How can you tell how much space a given SQL Tuning Set is occupying in the database?

The total amount of space taken up SQL Tuning Sets, SQL Profiles and SQL Plan Baselines can be monitored by query v$sysaux_occupants.

SEELCT  occupant_name, space_usage_kbytes 
FROM      v$sysaux_occupants
 WHERE occupant_name ='SQL_MANAGEMENT_BASE';

The easiest way to determine the size or storage space required for a given SQL Tuning Set is to export the SQL Tuning Set into an empty staging table and then look at the size of the staging table.

-- Create the staging table

BEGIN
   DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(table_name =>'STS_TEST');
END;
/

-- Export the SQL Tuning set using the DBMS_SQLTUNE.PACK_STGTAB_SQLSET procedure

BEGIN
  DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name => 'test_sql_tuning_set',staging_table_name => 'STS_TEST');
END;
/

-- Check the size of the staging table

SELECT segment_name, sum(bytes)/1024 KB
FROM    user_segments
WHERE segment_name='STS_TEST'
GROUP BY segment_name;

3. You can see how many statements are in an STS by viewing dba_sqlset but can we get any closer?

You can get detailed information regarding the SQL statements within a SQL Tuning Set and the execution environment information recorded about then in the view dba_sqlset_statements.

Alternatively you can select the information from the SQL Tuning Set itself via the DBMS_SQLTUNE package. Executing the statement below with a SELECT * to see the full list of information recorded.

SELECT sql_id, sql_text, parsing_schema_name 
FROM    TABLE(DBMS_SQLTUNE.select_sqlset('test_sql_tuning_set'))

SQL_ID       SQL_TEXT        PARSING_SCHEMA_NAME
------------- ----------------------------------------------------------------- ---------------------
94qn6y14kw01g  SELECT NVL(TO_NUMBER(EXTRACT(XMLTYPE(:B2 ), :B1 )), 0) FROM DUAL SYS