Databases Reference
In-Depth Information
END;
/
Managing SQL Tuning Sets
Before detailing how to manually run the SQL Tuning Advisor, let's first cover SQL tuning sets. As
mentioned, SQL tuning sets are a grouping of SQL statements and associated execution metrics. SQL
tuning sets are used as inputs to many of Oracle's tuning tools (such as the SQL Tuning Advisor and SQL
Access Advisor). Therefore, it's critical that you understand how to create and manage SQL tuning sets.
Figure 9-2 displays the SQL tuning set architecture.
Figure 9-2. SQL tuning set srchitecture
To fully comprehend how a SQL tuning set is populated, it's instructional to manually run queries
that retrieve high-resource SQL from the AWR and/or memory. The basic idea is that the result sets from
these queries can be used as input to populate a SQL tuning set.
Viewing Resource-Intensive SQL in the AWR
The
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY
function can be used to extract resource-intensive SQL
stored in the AWR. Before querying this function, first determine which snapshot IDs you want to select
from.
SQL> select snap_id from dba_hist_snapshot order by 1;
For example, this particular query selects queries in the AWR between snapshots 8200 and 8201
ordered by the top 10 in the disk reads usage category:
