--获得sql_id
SELECT * FROM V$SQL WHERE UPPER(SQL_TEXT) LIKE '%WITH%'
AND UPPER(SQL_TEXT) LIKE '%TCNT%'
--创建执行优化任务
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => 'crfjrqx3tnr9z',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'test_tuning_task');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'test_tuning_task');
END;
/
--查看任务状态
SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='test_tuning_task';
--查询结果
set long 888888
set serveroutput on size 888888
set lines 100
SELECT dbms_sqltune.report_tuning_task('test_tuning_task') FROM dual;
--删除优化任务
exec dbms_sqltune.drop_tuning_task('test_tuning_task');
PS:根据反馈结果进行操作,这里一般都是一些收集统计信息,加索引,绑定sql_profier等的建议操作。
具体优化方式还要根据sql的实际情况来,这里的建议只能作为参考,是否生效还是需要自己判断。