Databases Reference
In-Depth Information
SELECT value(x)
FROM table(dbms_sqltune.select_workload_repository(
26800,26900, null, null,'disk_reads',
null, null, null, 15)) x;
--
dbms_sqltune.load_sqlset(
sqlset_name => 'MY_TUNING_SET',
populate_cursor => cur,
load_option => 'MERGE');
END;
/
This technique allows you to add SQL statements to an existing SQL tuning set without having to drop
and recreate it.
Dropping a SQL Tuning Set
If you need to drop a SQL tuning set object, use the
DBMS_SQLTUNE.DROP_SQLSET
procedure to drop a
tuning set. The following example drops a tuning set named
MY_TUNING_SET
:
SQL> EXEC DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'MY_TUNING_SET');
You can confirm the tuning set has been dropped by querying the
DBA_SQLSET
view.
Running the SQL Tuning Advisor
Figure 9-4 shows the SQL Tuning Advisor architecture. This tool takes as input any of the following:
•
Single SQL statement
SQL_ID
from a statement in memory or the AWR
•
•
Set of SQL statements contained in a SQL tuning set
This tool provides useful advice regarding the creation of indexes, restructuring the SQL statement,
stale statistics, and so on. You can manually execute the SQL Tuning Advisor from
DBMS_SQLTUNE
PL/SQL
package, SQL Developer, or Enterprise Manager.
