Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.