sql
alter session set container=pdb11;
grant ADVISOR to hr;
sqlplus hr/hr@pdb11
SELECT /*+ ORDERED */ *
FROM employees e, locations l, departments d
WHERE e.department_id = d.department_id
AND l.location_id = d.location_id
AND e.employee_id < 1;
-- 1. 「SQLチューニング・タスクの作成」
sql
alter session set container=pdb11;
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT /*+ ORDERED */ * ' ||
'FROM employees e, locations l, departments d ' ||
'WHERE e.department_id = d.department_id AND ' ||
'l.location_id = d.location_id AND ' ||
'e.employee_id < :bnd';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
sql_text => my_sqltext
, bind_list => sql_binds(anydata.ConvertNumber(100))
, user_name => 'HR'
, scope => 'COMPREHENSIVE'
, time_limit => 60
, task_name => 'STA_SPECIFIC_EMP_TASK'
, description => 'Task to tune a query on a specified employee'
);
END;
/
COL TASK_ID FORMAT 999999
COL TASK_NAME FORMAT a25
COL STATUS_MESSAGE FORMAT a33
SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE
FROM DBA_ADVISOR_LOG
order by TASK_ID;
-- 2. 「SQLチューニング・タスクの構成」
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
task_name => 'STA_SPECIFIC_EMP_TASK'
, parameter => 'TIME_LIMIT'
, value => 300
);
END;
/
COL PARAMETER_NAME FORMAT a25
COL VALUE FORMAT a15
SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"
FROM DBA_ADVISOR_PARAMETERS
WHERE TASK_NAME = 'STA_SPECIFIC_EMP_TASK'
AND PARAMETER_VALUE != 'UNUSED'
ORDER BY PARAMETER_NAME;
-- 3. 「SQLチューニング・タスクの実行」
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'STA_SPECIFIC_EMP_TASK');
END;
/
COL TASK_ID FORMAT 999999
COL TASK_NAME FORMAT a25
COL STATUS_MESSAGE FORMAT a33
SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE
FROM DBA_ADVISOR_LOG
order by TASK_ID;
-- 4. 「SQLチューニング・タスクの監視」
SELECT STATUS
FROM DBA_ADVISOR_TASKS
WHERE TASK_NAME = 'STA_SPECIFIC_EMP_TASK';
VARIABLE my_tid NUMBER;
EXEC :my_tid := 131
COL ADVISOR_NAME FORMAT a20
COL SOFAR FORMAT 999
COL TOTALWORK FORMAT 999
SELECT TASK_ID, ADVISOR_NAME, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$ADVISOR_PROGRESS
WHERE TASK_ID = :my_tid;
-- 5. 「SQLチューニング・タスクの結果の表示」
SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'STA_SPECIFIC_EMP_TASK' )
FROM DUAL;
