It takes a certain amount of database access to be able to create a sql tuning set. It is more than a little fuzzy to me what kind of object they are in the database. My question though is a little more focused than that ( maybe ha ha ).
How can you tell how much space a given SQL Tuning set is occupying in the database? Also where are they stored ( SYSAUX ? / the default tablespace of the owner of the SQL Tuning set / the bermuda triangle )?
You can see how many statements by viewing dba_sqlset but can we get any closer?
Sometimes one just has to ask weird questions right?
Thanks John
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