f Oracle SAA with STS ~ 迪貝之家

Oracle SAA with STS

 --- 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');

Tuning Advisor 的權限 :
grant all on dbms_advisor to urname;
grant advisor to urname;
grant ADMINISTER SQL TUNING SET to urname;