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

BEGIN
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
    task_name => 'test_sql_tuning',
    parameter => 'TIME_LIMIT', value => 300);
END;
/

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
DECLARE
  v_text  PLS_INTEGER;
BEGIN
  v_text := DBMS_SPM.drop_sql_plan_baseline(sql_handle => 'SYS_SQL_7b76323ad90440b9',plan_name  => NULL); 
  DBMS_OUTPUT.put_line(v_text);
END;
/

上一篇:WIN 10 安装 mysql-5.7.17


下一篇:Linux 分配 swap 交换分区