--- SQL Tuing Set ----
BEGIN
dbms_sqltune.create_sqlset(
sqlset_name => 'MY_TUNING_SET'
,description => 'MY STS from AWR');
END;
/
DECLARE
base_cur dbms_sqltune.sqlset_cursor;
BEGIN
OPEN base_cur FOR
SELECT value(x)
FROM table(dbms_sqltune.select_workload_repository(
5153,5171, null, null,'disk_reads',
null, null, null, 15)) x;
--
dbms_sqltune.load_sqlset(
sqlset_name => 'MY_TUNING_SET',
populate_cursor => base_cur);
END;
/
-- SQL Access Advisor
-- Check current task
select TASK_ID,TASK_NAME,ADVISOR_NAME,CREATED,LAST_MODIFIED,LAST_EXECUTION,
EXECUTION_TYPE,STATUS,STATUS_MESSAGE,RECOMMENDATION_COUNT from user_advisor_tasks
where ADVISOR_NAME in ('SQL Access Advisor','SQL Tuning Advisor');
/*Creating a Access Advisor task */
DECLARE
task_id NUMBER;
task_name VARCHAR2(30);
BEGIN
task_name := 'ACCESSFORTEST01';
DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, task_name);
END;
/
--- 連結 SAA與STS
exec dbms_advisor.ADD_STS_REF ('ACCESSFORTEST01','YOUR USER NAME','MY_TUNING_SET');
-- 執行SAA 分析工作
exec dbms_advisor.execute_task('ACCESSFORTEST01');
-- 查看建議
select dbms_advisor.get_task_script('ACCESSFORTEST01') from dual;
--砍掉SAA Task
exec dbms_advisor.delete_task('ACCESSFORTEST01');
--砍掉STS
EXEC DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'MY_TUNING_SET');