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;