Oracle 調優助手的簡單用法

var tuning_task varchar2(1000);

declare

  l_sql_id      v$session.prev_sql_id%type;

  l_tuning_task varchar2(30);

begin

  l_sql_id      := '0wyjcdn7bm08d';

  l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);

  :tuning_task  := l_tuning_task;

  dbms_sqltune.execute_tuning_task(l_tuning_task);

  dbms_output.put_line(l_tuning_task);

end;

/

print tuning_task;

set long 100000

set longchunksize 10000

select dbms_sqltune.report_tuning_task(task_name => :tuning_task) from dual;

exec dbms_sqltune.drop_tuning_task(task_name => :tuning_task);

  dbms_sqltune.set_tuning_task_parameter(

    task_name => 'test_sql_tuning',

    parameter => 'time_limit', value => 300);

col opname for a20

col advisor_name for a20

select sid,serial#,username,opname,advisor_name,target_desc,start_time sofar, totalwork 

from   v$advisor_progress 

where  username = 'test';

col snap_interval format a20

col retention format a20

col src_dbname for a30

select * from dba_hist_wr_control;

exec dbms_workload_repository.modify_snapshot_settings(interval=>60, retention=>60*24*60);

set serveroutput on

  v_text  pls_integer;

  v_text := dbms_spm.drop_sql_plan_baseline(sql_handle => 'sys_sql_7b76323ad90440b9',plan_name  => null); 

  dbms_output.put_line(v_text);