oracle SQL Tuning Advisor使用步骤

1.授权

grant ADVISOR to 用户名 ；

2. sqlrun

declare
my_task_name varchar2(30);
my_sqltext clob;
begin
my_sqltext := '你具体的sql语句'；
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text  => my_sqltest，
user_name => '用户名全大写'，
scope => 'COMPREHENSIVE'，
time_limit  => 90，        -- 时间限制（秒） 
 task_name => 'my_tuning_task'，
description => 'Tuning')；
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'my_tuning_task')；
END；
/

– 避免换行看不到

set long 99999
set serveroutput on size 99999
set line 2000

– 检查分析状态

select task_name ，status from USER_ADVISOR_TASKS WHERE TASK_NAME ='my_tuning_task'；

– 查分析的信息

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_tuning_task') FROM DUAL;

–删除任务

EXEC ``DBMS_SQLTUNE.DROP_TUNING_TASK('my_tuning_task');