Databases Reference
In-Depth Information
Shows whether a SQL tuning set is active.
DBA_SQLSET_REFERENCES
You can also use the
DBMS_SQLTUNE.SELECT_SQLSET
function to retrieve information about SQL tuning
sets, like so:
SELECT
sql_id, elapsed_time, cpu_time, buffer_gets, disk_reads, sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('&&sqlset_name'));
Whether you use the
DBMS_SQLTUNE.SELECT_SQLSET
function or directly query the data dictionary
views depends entirely on your personal preference or business requirement.
You can also manage SQL tuning sets from within Enterprise Manager. From the main page
navigate to the Performance page and then to the SQL Tuning Sets (in the Additional Monitoring Links)
section. From there you should see a page similar to the one shown in Figure 9-3.
Figure 9-3. Managing SQL tuning sets
From this screen you can create and manage SQL tuning sets. Clicking on the SQL tuning set name
will display all of the SQL within the tuning set and associated metrics.
Selectively Deleting Statements from a SQL Tuning Set
Once you've established a SQL tuning set, you may want to prune statements out of it. For example,
suppose you want to prune SQL statements from an STS that don't meet a performance measure, such
as queries that have less than two million disk reads. First, view the existing SQL information associated
with an STS, like so:
select sqlset_name, disk_reads, cpu_time, elapsed_time, buffer_gets
