SQL执行优化：利用DBMS_SQLTUNE进行性能调优

SELECT * FROM USER_ADVISOR_LOG

--注册优化任务
DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext   CLOB;
BEGIN
  my_sqltext   := 'XXXX';
  my_task_name := dbms_sqltune.create_tuning_task(sql_text    => my_sqltext
                                                 ,user_name   => 'APPSVIEW'
                                                 , --注意是大小写否则会报错提示用户无效
                                                  scope       => 'COMPREHENSIVE'
                                                 ,time_limit  => 60
                                                 ,task_name   => 'GET_CUSTOMER_CREDIT'
                                                 ,description => 'Task to tune a query on a specified table');
 --创建优化任务
  dbms_sqltune.execute_tuning_task(task_name => 'ocpyang_tuning_sql_test');
END;

--删除已有的优化任务
BEGIN
dbms_sqltune.drop_tuning_task('GET_CUSTOMER_CREDIT');
END;


--查看优化状态
SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='GET_CUSTOMER_CREDIT';


--查看优化建议