快速参考：利用dbms_sqltune进行sql优化

快速参考：利用dbms_sqltune进行sql优化

1. 创建sql tuning task
用sys用户执行以下sql,其中sql_id部分就是你想要优化sql的sql_id，请根据实际情况改,task_name也由你取名
set serveroutput on
 DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
 BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
       sql_id      => 'aruh0jp61r0h7',
       scope       => DBMS_SQLTUNE.scope_comprehensive,
       time_limit  => 60,
       task_name   => 'aruh0jp61r0h7',
       description => 'Tuning task for statement aruh0jp61r0h7.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
 END;
 /

2 执行dbms_sqltune,其中task_name就是你上一步定义的内容
 EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'aruh0jp61r0h7');

3 查看sql tune report,可以根据oracle的提示内部作相应优化了
SELECT DBMS_SQLTUNE.report_tuning_task('aruh0jp61r0h7') AS recommendations FROM dual;

4.删除
exec dbms_sqltune.drop_tuning_task('aruh0jp61r0h7');