Please go thru this.
This must be very useful to tune a sql query, when we start building new programs at least at an initial level.
SQL queries can be auto tuned using the DBMS_SQLTUNE package.
Here are the steps to tune a sample statement.
Let’s take the example of a real life query used.
STEP 1: Create a Tuning Task
DECLARE
my_sqltext CLOB;
task_name VARCHAR2(30);
BEGIN
my_sqltext := < Query >;
task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sqltext,
user_name => 'APPS',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'sql_tuning');
END;
STEP 2: Execute the Task
Begin
dbms_sqltune.execute_tuning_task ( 'sql_tuning');
end;
STEP 3: Check if the task has completed
SELECT task_name, status FROM dba_advisor_log WHERE task_name= 'sql_tuning';
STEP 4: Generate Report
select dbms_sqltune.report_tuning_task('sql_tuning') from dual;
The suggestion below is to analyze the table and then gives the recommendation.
Hope this is useful. You can do further study on this package for the full set of features.
