Databases Reference
In-Depth Information
In the following screenshot, we can see the report obtained in step 4, on a test environment,
informing us that the optimizer statistics on the
SALES_CUST_BIX
bitmap index are stale. So,
they need to be updated, in order to be sure that the execution plan of our query is optimal.
In step 5, we clean the database by dropping the tuning task and revoking grants from
the user
SH
.
There's more...
In this recipe, we have seen how to use SQL Tuning Advisor with a single SQL statement. The
same tool can be executed over a single statement providing the
sql_id
, from the Automatic
Workload Repository, or from an SQL Tuning Set. In the previous situation, we can use the
following syntax to execute the SQL Tuning Advisor over the
test_tuning_set
SQL Tuning Set:
BEGIN
DBMS_SQLTUNE.CREATE_TUNING_TASK( -
sqlset_name => 'test_tuning_set', -
rank1 => 'BUFFER_GETS', -
time_limit => 3600, -
description => 'Tuning a SQL Tuning Set');
END;