Databases Reference
In-Depth Information
These steps are explained in the following subsections.
Creating a Tuning Task
A tuning task allows you to specify the source of the SQL statement(s) to be used for input into the SQL
Tuning Advisor. You can use the following as inputs when creating a SQL tuning task:
•
Text for a specific SQL statement
•
SQL identifier for a specific SQL statement from the cursor cache in memory
•
Single SQL statement from the AWR given a range of snapshot IDs
•
SQL tuning set name
We'll show examples of each of these techniques.
Note
The user creating the tuning task needs the ADMINISTER SQL MANAGEMENT OBJECT system privilege.
Text for a Specific SQL Statement
One simple way to tune a specific statement is to use the SQL query itself when creating a tuning task.
Here's an example:
DECLARE
tune_task VARCHAR2(30);
tune_sql CLOB;
BEGIN
tune_sql := 'select count(*) from mgmt_db_feature_usage_ecm';
tune_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => tune_sql
,user_name => 'MV_MAINT'
,scope => 'COMPREHENSIVE'
,time_limit => 60
,task_name => 'tune_test'
,description => 'Provide SQL text'
);
END;
/
