grant ADMINISTER SQL TUNING SET to sh;
sqlplus sh/sh@pdb11
-- 1. SQLチューニング・セットの作成
SET SERVEROUTPUT ON;
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
VARIABLE workload_name VARCHAR2(255);
EXECUTE :workload_name := 'MY_STS_WORKLOAD';
EXECUTE DBMS_SQLTUNE.CREATE_SQLSET(:workload_name, 'test purpose');
-- 2. SQLチューニング・セットのロード
DROP TABLE user_workload;
CREATE TABLE user_workload
(
username varchar2(128), /* User who executes statement */
module varchar2(64), /* Application module name */
action varchar2(64), /* Application action name */
elapsed_time number, /* Elapsed time for query */
cpu_time number, /* CPU time for query */
buffer_gets number, /* Buffer gets consumed by query */
disk_reads number, /* Disk reads consumed by query */
rows_processed number, /* # of rows processed by query */
executions number, /* # of times query executed */
optimizer_cost number, /* Optimizer cost for query */
priority number, /* User-priority (1,2 or 3) */
last_execution_date date, /* Last time query executed */
stat_period number, /* Window exec time in seconds */
sql_text clob /* Full SQL Text */
);
-- aggregation with selection
INSERT INTO user_workload (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
'SELECT t.week_ending_day, p.prod_subcategory,
SUM(s.amount_sold) AS dollars, s.channel_id, s.promo_id
FROM sales s, times t, products p
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND s.prod_id > 10
AND s.prod_id < 50
GROUP BY t.week_ending_day, p.prod_subcategory, s.channel_id, s.promo_id')
/
-- aggregation with selection
INSERT INTO user_workload (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
'SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars
FROM sales s , times t
WHERE s.time_id = t.time_id
AND s.time_id BETWEEN TO_DATE(''01-JAN-2000'', ''DD-MON-YYYY'')
AND TO_DATE(''01-JUL-2000'', ''DD-MON-YYYY'')
GROUP BY t.calendar_month_desc')
/
-- order by
INSERT INTO user_workload (username, module, action, priority, sql_text)
VALUES ('SH', 'Example1', 'Action', 2,
'SELECT c.country_id, c.cust_city, c.cust_last_name
FROM customers c
WHERE c.country_id IN (52790, 52789)
ORDER BY c.country_id, c.cust_city, c.cust_last_name')
/
COMMIT;
DECLARE
sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN sqlset_cur FOR
SELECT SQLSET_ROW(null,null, SQL_TEXT, null, null, 'SH', module,
'Action', 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, null, 2, 3,
sysdate, 0, 0, null, 0, null, null)
FROM USER_WORKLOAD;
DBMS_SQLTUNE.LOAD_SQLSET('MY_STS_WORKLOAD', sqlset_cur);
END;
/
-- 3. タスクの作成および構成
EXEC :task_name := 'MYTASK';
EXEC DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', :task_id, :task_name);
EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'TIME_LIMIT', 30);
EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'ANALYSIS_SCOPE', 'ALL');
EXECUTE DBMS_ADVISOR.ADD_STS_REF(:task_name, 'SH', :workload_name);
-- 4. タスクの実行
EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);
COL TASK_ID FORMAT 999
COL TASK_NAME FORMAT a25
COL STATUS_MESSAGE FORMAT a25
SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE
FROM USER_ADVISOR_LOG;
-- 5. 推奨事項の表示
VARIABLE workload_name VARCHAR2(255);
VARIABLE task_name VARCHAR2(255);
EXECUTE :task_name := 'MYTASK';
EXECUTE :workload_name := 'MY_STS_WORKLOAD';
SELECT REC_ID, RANK, BENEFIT
FROM USER_ADVISOR_RECOMMENDATIONS
WHERE TASK_NAME = :task_name
ORDER BY RANK;
SELECT SQL_ID, REC_ID, PRECOST, POSTCOST,
(PRECOST-POSTCOST)*100/PRECOST AS PERCENT_BENEFIT
FROM USER_ADVISOR_SQLA_WK_STMTS
WHERE TASK_NAME = :task_name
AND WORKLOAD_NAME = :workload_name
ORDER BY percent_benefit DESC;
SELECT 'Action Count', COUNT(DISTINCT action_id) cnt
FROM USER_ADVISOR_ACTIONS
WHERE TASK_NAME = :task_name;
SELECT REC_ID, ACTION_ID, SUBSTR(COMMAND,1,30) AS command
FROM USER_ADVISOR_ACTIONS
WHERE TASK_NAME = :task_name
ORDER BY rec_id, action_id;
CREATE OR REPLACE PROCEDURE show_recm (in_task_name IN VARCHAR2) IS
CURSOR curs IS
SELECT DISTINCT action_id, command, attr1, attr2, attr3, attr4
FROM user_advisor_actions
WHERE task_name = in_task_name
ORDER BY action_id;
v_action number;
v_command VARCHAR2(32);
v_attr1 VARCHAR2(4000);
v_attr2 VARCHAR2(4000);
v_attr3 VARCHAR2(4000);
v_attr4 VARCHAR2(4000);
v_attr5 VARCHAR2(4000);
BEGIN
OPEN curs;
DBMS_OUTPUT.PUT_LINE('=========================================');
DBMS_OUTPUT.PUT_LINE('Task_name = ' || in_task_name);
LOOP
FETCH curs INTO
v_action, v_command, v_attr1, v_attr2, v_attr3, v_attr4 ;
EXIT when curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Action ID: ' || v_action);
DBMS_OUTPUT.PUT_LINE('Command : ' || v_command);
DBMS_OUTPUT.PUT_LINE('Attr1 (name) : ' || SUBSTR(v_attr1,1,30));
DBMS_OUTPUT.PUT_LINE('Attr2 (tablespace): ' || SUBSTR(v_attr2,1,30));
DBMS_OUTPUT.PUT_LINE('Attr3 : ' || SUBSTR(v_attr3,1,30));
DBMS_OUTPUT.PUT_LINE('Attr4 : ' || v_attr4);
DBMS_OUTPUT.PUT_LINE('Attr5 : ' || v_attr5);
DBMS_OUTPUT.PUT_LINE('----------------------------------------');
END LOOP;
CLOSE curs;
DBMS_OUTPUT.PUT_LINE('=========END RECOMMENDATIONS============');
END show_recm;
/
SET SERVEROUTPUT ON SIZE 99999
EXECUTE show_recm(:task_name);
