Oraclet gets optimization advice through DBMS_SQLTUNE.EXECUTE_TUNING_TASK

DBMS_SQLTUNE.EXECUTE_TUNING_TASK

1 create the task by specifying the sql statement by getting the sql_id from cursor cache

2 the set of sql tuning includes multiple statements to create the task

3 the text of a single sql statement to create the task

4 get the sql statement establishment task by using the corresponding sql_id in awr

Use sql_id to get an empty optimization suggestion when sql_id is not in v$session a, v$process b, v$sqlarea.

DECLARE

My_task_name VARCHAR2 (30)

Sql_id VARCHAR2 (30)

BEGIN

Sql_id: ='& sqlid'

My_task_name: = DBMS_SQLTUNE.CREATE_TUNING_TASK

(sql_id= > sql_id

Scope = > 'comprehensive'

Time_limit= > 60

Task_name= > 'my_sql_tuning_' | | sql_id

Description = > 'Tuning Task')

DBMS_SQLTUNE.EXECUTE_TUNING_TASK ('my_sql_tuning_' | | sql_id)

END

Prompt for sql_id

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK ('my_sql_tuning_&sqlid') FROM DUAL

BEGIN dbms_sqltune.drop_tuning_task ('my_sql_tuning_520mkxqpf15q8'); END

BEGIN dbms_sqltune.drop_tuning_task ('my_sql_tuning_&sqlid'); END

DBMS_SQLTUNE.EXECUTE_TUNING_TASK uses sql text to get optimization suggestions

DECLARE

MY_TASK_NAME VARCHAR2 (30)

MY_SQLTEXT CLOB

BEGIN

MY_SQLTEXT: = 'SELECT * FROM TEST_OBJECT_TTX WHERE OBJECT_ID =: BND'

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 = > 60

TASK_NAME = > 'SQL_TUNING_TEST'

DESCRIPTION= > 'TUNING TASK'

);

END

Select a.program, b.spid, c.sql_text,c.SQL_ID

From v$session a, v$process b, v$sqlarea c

Where a.paddr = b.addr

And a.sql_hash_value = c.hash_value

And a.username is not null

And c.SQL_ID = '7hjb2wh4nprf7'