ORACLE SQL TUNING ADVISOR
Main steps for using sql tunning advisor:
1 establish tunning task
2 execute task
3 display tunning results
4 run the corresponding tuning method according to the recommendation-ADVISOR authorization
1 create 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 set up 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 establish 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 study:
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
