here is a test scenario -- we use tables and scheduler Job to execute SQL Tuning Tasks which have been created by normal developers
drop USER "PERF" cascade;
drop ROLE PERF_OWN;
CREATE USER "PERF" identified by perf;
-- Create Role and assign Advisor,etc to it...
CREATE ROLE PERF_OWN;
GRANT ADMINISTER SQL TUNING SET TO "PERF_OWN";
GRANT CREATE JOB TO "PERF_OWN";
GRANT ADVISOR TO "PERF_OWN";
GRANT SELECT ANY DICTIONARY TO "PERF_OWN";
GRANT CREATE SESSION TO "PERF_OWN";
-- Assign the Role to the User and enable Admin Option
GRANT "PERF_OWN" TO "PERF" WITH ADMIN OPTION;
conn perf/perf
CREATE TABLE "PERF"."TASKS"
( "TASKID" NUMBER(12,0) NOT NULL ENABLE,
"SQL_ID" VARCHAR2(24) NOT NULL ENABLE,
"PLAN_HASH_VALUE" NUMBER(24,0),
"TIME_LIMIT" NUMBER(24,0) NOT NULL ENABLE,
"TASK_SCOPE" VARCHAR2(24) DEFAULT 'COMPREHENSIVE',
"TASK_NAME" VARCHAR2(24) NOT NULL ENABLE,
"DESCRIPTION" VARCHAR2(24),
"SNAP_FIRST" NUMBER(24,0),
"SNAP_LAST" NUMBER(24,0),
"STATUS" VARCHAR2(24),
"TO_BE_DELETED" VARCHAR2(4),
"ERROR_MESSAGE" CLOB
);
CREATE TABLE "PERF"."TASKS_RESULT" (
"TASKID" NUMBER(12,0),
"RESULT" CLOB
);
CREATE OR REPLACE EDITIONABLE PROCEDURE "PERF"."LOAD_TASKS" AS
stmt_task VARCHAR2(64);
err_code VARCHAR2(24);
err_msg VARCHAR2(4000);
output CLOB;
task_counter NUMBER(24);
CURSOR C IS SELECT * FROM PERF.tasks;
BEGIN
FOR cr IN C LOOP
BEGIN
IF cr.to_be_deleted = 'Y' THEN
DELETE FROM PERF.tasks WHERE
taskid = cr.taskid;
DELETE FROM PERF.tasks_result WHERE
taskid = cr.taskid;
COMMIT;
dbms_sqltune.drop_tuning_task(cr.task_name);
ELSIF ( cr.to_be_deleted <>'Y' or cr.to_be_deleted is null) AND (cr.status <> 'EXECUTED' or cr.status is null) THEN
task_counter := cr.taskid;
stmt_task := dbms_sqltune.create_tuning_task(
begin_snap => cr.snap_first,
end_snap => cr.snap_last,
sql_id => cr.sql_id,
scope => cr.task_scope,
time_limit => cr.time_limit,
task_name => cr.task_name,
description => cr.description
);
dbms_sqltune.execute_tuning_task(cr.task_name);
SELECT
dbms_sqltune.report_tuning_task(
cr.task_name,
'TEXT',
'ALL'
)
INTO
output
FROM
dual;
INSERT INTO PERF.tasks_result (
taskid,
result
) VALUES (
cr.taskid,
output
);
UPDATE PERF.tasks
SET
status = 'EXECUTED'
WHERE
taskid = cr.taskid;
END IF;
EXCEPTION
WHEN OTHERS THEN
err_code := sqlcode;
err_msg := sqlerrm;
UPDATE PERF.tasks
SET
status = 'FAILED', error_message =err_code || ':' || err_msg
WHERE
taskid = cr.taskid;
END;
END LOOP;
COMMIT;
END;
/
BEGIN
dbms_scheduler.create_job('"EXECUTE_TASKS"',
job_type=>'STORED_PROCEDURE', job_action=>'LOAD_TASKS',
number_of_arguments=>0,
start_date=>NULL,
end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"',
enabled=>FALSE,
auto_drop=>FALSE,comments=>'Execute Tuning Tasks'
);
sys.dbms_scheduler.set_attribute('"EXECUTE_TASKS"','NLS_ENV','NLS_LANGUAGE=''GERMAN'' NLS_TERRITORY=''GERMANY'' NLS_CURRENCY=''€'' NLS_ISO_CURRENCY=''GERMANY'' NLS_NUMERIC_CHARACTERS='',.'' NLS_CALENDAR=''GREGORIAN'' NLS_DATE_FORMAT=''DD.MM.RR'' NLS_DATE_LANGUAGE=''GERMAN'' NLS_SORT=''GERMAN'' NLS_TIME_FORMAT=''HH24:MI:SSXFF'' NLS_TIMESTAMP_FORMAT=''DD.MM.RR HH24:MI:SSXFF'' NLS_TIME_TZ_FORMAT=''HH24:MI:SSXFF TZR'' NLS_TIMESTAMP_TZ_FORMAT=''DD.MM.RR HH24:MI:SSXFF TZR'' NLS_DUAL_CURRENCY=''€'' NLS_COMP=''BINARY'' NLS_LENGTH_SEMANTICS=''BYTE'' NLS_NCHAR_CONV_EXCP=''FALSE''');
dbms_scheduler.enable('"EXECUTE_TASKS"');
COMMIT;
END;
/
-- Test the functionality : get an SQL_ID from DBA_HIST_SQLSTAT and two SNAP_IDs (SNAP_FIRST & SNAP_LAST) that references time intervals when the SQL ran
Insert into PERF.TASKS (TASKID,SQL_ID,TIME_LIMIT,TASK_NAME,DESCRIPTION,SNAP_FIRST,SNAP_LAST) VALUES (1,??,30,'TEST','TEST',??,??);
COMMIT;
EXEC DBMS_SCHEDULER.RUN_JOB('EXECUTE_TASKS',FALSE);
-- Check result
SELECT * from PERF.TASKS_RESULT;