SQL Access Advisor的使用
原文地址：
-- 先放一张图
--- 创建sql tuning set
conn sh/sh
SET SERVEROUTPUT ON;
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
VARIABLE workload_name VARCHAR2(255);
-- 创建sql tuning set
EXECUTE :workload_name := 'MY_STS_WORKLOAD';
EXECUTE DBMS_SQLTUNE.CREATE_SQLSET(:workload_name, 'test purpose');
[email protected]>EXECUTE :workload_name := 'MY_STS_WORKLOAD';
PL/SQL procedure successfully completed.
[email protected]>EXECUTE DBMS_SQLTUNE.CREATE_SQLSET(:workload_name, 'test purpose');
PL/SQL procedure successfully completed.
[email protected]>-- Populating a SQL Tuning Set with a User-Defined Workload
-- 创建user_workload 表 ，用来存放那些要调优的SQL语句
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 */
);
[email protected]>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 */
); 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Table created.
[email protected]>-- 将以下查询加载到表中 （又一种创建STS的方法），实际上就是sts中对应的sql语句
-- 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;--- 将SQL语句加载到sts中
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;
/
[email protected]>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; 2 3 4 5 6 7 8 9 10
11 /
PL/SQL procedure successfully completed.
[email protected]>-- 查看结果，sql语句被load进去了
[email protected]>select name,statement_count from user_sqlset where name='MY_STS_WORKLOAD';
NAME STATEMENT_COUNT
---------------- ---------------
MY_STS_WORKLOAD 3
[email protected]>-- 创建SQL Access Advisor task
EXECUTE :task_name := 'MYTASK';
EXECUTE DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', :task_id, :task_name);
[email protected]>EXECUTE :task_name := 'MYTASK';
PL/SQL procedure successfully completed.
[email protected]>EXECUTE DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', :task_id, :task_name);
PL/SQL procedure successfully completed.
[email protected]>-- 设置task的参数
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'TIME_LIMIT', 30);
EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'ANALYSIS_SCOPE', 'ALL');
[email protected]>EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'TIME_LIMIT', 30);
PL/SQL procedure successfully completed.
[email protected]>EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, 'ANALYSIS_SCOPE', 'ALL');
PL/SQL procedure successfully completed.
[email protected]>-- 建立sql access advisor和SQL Set之间的联系
EXECUTE DBMS_ADVISOR.ADD_STS_REF(:task_name, 'SH', :workload_name);
[email protected]>EXECUTE DBMS_ADVISOR.ADD_STS_REF(:task_name, 'SH', :workload_name);
PL/SQL procedure successfully completed.
[email protected]>-- 执行SQL Access advisor task
EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);
[email protected]>EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);
PL/SQL procedure successfully completed.
[email protected]>-- 查看task 状态
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;
[email protected]>SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE FROM USER_ADVISOR_LOG;
TASK_ID TASK_NAME STATUS STATUS_MESSAGE
------- ------------------------- ----------- -------------------------
3662 STA_MY_STS COMPLETED
3673 MYTASK COMPLETED Access advisor execution completed-- 查看SQL Access Advisor的建议
-- 查看产生的建议 ，建议id，建议等级，benefit等等
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;
[email protected]>SELECT REC_ID, RANK, BENEFIT
FROM USER_ADVISOR_RECOMMENDATIONS
WHERE TASK_NAME = :task_name
ORDER BY RANK; 2 3 4
REC_ID RANK BENEFIT
---------- ---------- ----------
1 1 1
2 2 455
3 3 6
[email protected]>
[email protected]>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; 2 3 4 5 6
SQL_ID REC_ID PRECOST POSTCOST PERCENT_BENEFIT
------------- ---------- ---------- ---------- ---------------
3kq8jnpbyd6z4 2 586 131 77.6450512
bvfmfuafa2aak 3 112 106 5.35714286
12g5wbs4hznuc 1 1077 1076 .092850511
[email protected]>
[email protected]>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; 2 3 4
REC_ID ACTION_ID COMMAND
------ --------- --------------
1 1 CREATE INDEX
2 2 CREATE INDEX
3 3 CREATE INDEX
[email protected]>-- 创建存储过程，查看完整的建议，也可以直接查询 。
select * from dba_advisor_actions where task_id=3673 -- 或者这样查询，简单一些
-- 创建存储过程，获取值
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);
[email protected]>SET SERVEROUTPUT ON SIZE 99999
[email protected]>EXECUTE show_recm(:task_name);
=========================================
Task_name = MYTASK
Action ID: 1
Command : CREATE INDEX
Attr1 (name) : "SH"."PRODUCTS_IDX$$_0E590000"
Attr2 (tablespace):
Attr3 : "SH"."PRODUCTS"
Attr4 : BTREE
Attr5 :
----------------------------------------
Action ID: 2
Command : CREATE INDEX
Attr1 (name) : "SH"."CUSTOMERS_IDX$$_0E590001
Attr2 (tablespace):
Attr3 : "SH"."CUSTOMERS"
Attr4 : BTREE
Attr5 :
----------------------------------------
Action ID: 3
Command : CREATE INDEX
Attr1 (name) : "SH"."TIMES_IDX$$_0E590002"
Attr2 (tablespace):
Attr3 : "SH"."TIMES"
Attr4 : BTREE
Attr5 :
----------------------------------------
=========END RECOMMENDATIONS============
PL/SQL procedure successfully completed.
[email protected]>-- 产生脚本，上面给出了建议，但是没有脚本，比如没有创建索引，创建分区的脚本
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('MYTASK'),'DUMP', 'advscript.sql');
[email protected]>EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('MYTASK'),'DUMP', 'advscript.sql');
PL/SQL procedure successfully completed.
[email protected]>
[[email protected] dump]$ more advscript.sql
Rem SQL Access Advisor: Version 12.2.0.1.0 - Production
Rem
Rem Username: SH
Rem Task: MYTASK
Rem Execution date:
Rem
CREATE INDEX "SH"."PRODUCTS_IDX$$_0E590000"
ON "SH"."PRODUCTS"
("PROD_ID","PROD_SUBCATEGORY")
COMPUTE STATISTICS;
CREATE INDEX "SH"."CUSTOMERS_IDX$$_0E590001"
ON "SH"."CUSTOMERS"
("COUNTRY_ID","CUST_CITY","CUST_LAST_NAME")
COMPUTE STATISTICS;
CREATE INDEX "SH"."TIMES_IDX$$_0E590002"
ON "SH"."TIMES"
("TIME_ID","CALENDAR_MONTH_DESC")
COMPUTE STATISTICS;
[[email protected] dump]$ end