oraclet通過DBMS_SQLTUNE.EXECUTE_TUNING_TASK獲取優化建議

DBMS_SQLTUNE.EXECUTE_TUNING_TASK

1 通過取得來自cursor cache 的sql_id來指定sql語句來建立任務

2 sql調優的集合包括的多個語句來建立任務

3 單一sql語句的文本來建立任務

4 通過用awr中相應的sql_id來取得sql語句建立任務

用sql_id獲取當sql_id不在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;

提示輸入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用sql文本獲取優化建議

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'

??

??

??