f Oracle STA with STS ~ 迪貝之家

Oracle STA with STS

 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;

/


SELECT sqlset_name, elapsed_time, cpu_time, buffer_gets, disk_reads, sql_text

FROM dba_sqlset_statements where sqlset_name = 'MY_TUNING_SET';



SELECT

sql_id, elapsed_time, cpu_time, buffer_gets, disk_reads, sql_text

FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('MY_TUNING_SET'));



variable mytt varchar2(30);

exec :mytt := DBMS_SQLTUNE.CREATE_TUNING_TASK(sqlset_name => 'MY_TUNING_SET');

print :mytt


 exec dbms_sqltune.execute_tuning_task(task_name => :mytt);

 set long 10000 longchunksize 10000 linesize 132 pagesize 200

 select dbms_sqltune.report_tuning_task(:mytt) from dual;


exec DBMS_SQLTUNE.DROP_TUNING_TASK(:mytt);

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;