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'
