ORACLE SQL TUNING ADVISOR

The main steps used by SQL Tunning Advisor:
1 Setting up Tunning task
2 Execute Task
3 showing tunning results
4 Follow the recommendations to run the appropriate tuning method----Advisor Authorization

1 creating tasks based on SQL text

FUNCTION Create_tuning_task (
Sql_text in CLOB,
Bind_list in Sql_binds: = NULL,
User_name in VARCHAR2: = NULL,
Scope in VARCHAR2: = Scope_comprehensive,
Time_limit in Number: = Time_limit_default,
Task_name in VARCHAR2: = NULL,
Description in VARCHAR2: = NULL)
RETURN VARCHAR2;

2 building tasks based on sql_id
FUNCTION Create_tuning_task (
sql_id in VARCHAR2,
Plan_hash_value in Number: = NULL,
Scope in VARCHAR2: = Scope_comprehensive,
Time_limit in Number: = Time_limit_default,
Task_name in VARCHAR2: = NULL,
Description in VARCHAR2: = NULL)
RETURN VARCHAR2;

3 Setting up tasks based on awr snapshot interval and corresponding sql_id
FUNCTION Create_tuning_task (
Begin_snap in number,
End_snap in number,
sql_id in VARCHAR2,
Plan_hash_value in Number: = NULL,
Scope in VARCHAR2: = Scope_comprehensive,
Time_limit in Number: = Time_limit_default,
Ask_name in VARCHAR2: = NULL,
Description in VARCHAR2: = NULL)
RETURN VARCHAR2;

Case:
DECLARE
My_task_name VARCHAR2 (30);
My_sqltext CLOB;
BEGIN
My_sqltext: = ' SELECT 1 from dual ';
My_task_name: = Dbms_sqltune. Create_tuning_task (Sql_text = My_sqltext,
Bind_list=>sql_binds (ANYDATA. Convertnumber (9)),
user_name = ' Noap ',
Scope=> ' comprehensive ',
Time_limit = 600,
Task_name = ' Sql_tuning_test ',
Description=> ' TUNING TASK '
);
END;

BEGIN Dbms_sqltune. Execute_tuning_task (' sql_tuning_test '); END;
SELECT status from user_advisor_tasks WHERE task_name = ' sql_tuning_test ';
SELECT Dbms_sqltune. Report_tuning_task (' Sql_tuning_test ') from DUAL;
BEGIN dbms_sqltune.drop_tuning_task (' sql_tuning_test '); END;

ORACLE SQL TUNING ADVISOR