使用STA来优化语句

DECLARE
   l_task_id   VARCHAR2 (20);
   l_sql       VARCHAR2 (3000);
BEGIN
   l_sql := 'SELECT id FROM TB1';
   l_task_id :=
      DBMS_SQLTUNE.create_tuning_task (sql_text     => l_sql,
                                       user_name    => 'SCOTT',
                                       scope        => 'COMPREHENSIVE',
                                       time_limit   => 60,
                                       task_name    => 'test_sql_tuning_task');
   DBMS_SQLTUNE.execute_tuning_task ('test_sql_tuning_task');
END;

SELECT task_name, ADVISOR_NAME, STATUS
  FROM user_advisor_tasks
 WHERE task_name LIKE 'test_sql_tuning_task%';

SELECT DBMS_SQLTUNE.report_tuning_task ('test_sql_tuning_task') FROM DUAL;

删除执行统计信息

DELETE FROM user_advisor_tasks
      WHERE task_name = 'test_sql_tuning_task';