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