Dav_笔记13：SQL Access Advisor 之 2 使用SQL Access Advisor-4

本文链接：https://blog.youkuaiyun.com/Dav_2099/article/details/141202854

使用SQL Access Advisor的示例

本节说明了使用SQL Access Advisor的一些典型方案。

Oracle数据库提供了一个脚本，其中包含本章的示例aadvdemo.sql。

来自用户定义的工作负载的建议

以下示例从用户定义的表SH.USER_WORKLOAD导入工作负载。然后，它会创建一个名为MYTASK的任务，将存储预算设置为100 MB，然后运行该任务。 PL / SQL过程打印建议。最后，该示例生成一个脚本，您可以使用该脚本来实现建议。

步骤1准备USER_WORKLOAD表

使用SQL语句加载USER_WORKLOAD表，如下所示：

CONNECT SH/SH;

-- 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')

/

--Load all SQL queries.

INSERT INTO user_workload (username, module, action, priority, sql_text)

VALUES ('SH', 'Example1', 'Action', 2,

'SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,

SUM(s.amount_sold) sales_amount

FROM sales s, times t, customers c, channels ch

WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id

AND s.channel_id = ch.channel_id AND c.cust_state_province = ''CA''

AND ch.channel_desc IN (''Internet'',''Catalog'')

AND t.calendar_quarter_desc IN (''1999-Q1'',''1999-Q2'')

GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_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;

CONNECT SH/SH;

set serveroutput on;

VARIABLE task_id NUMBER;

VARIABLE task_name VARCHAR2(255);

VARIABLE workload_name VARCHAR2(255);

步骤2创建名为MYWORKLOAD的SQL调优集

EXECUTE :workload_name := 'MYWORKLOAD';

EXECUTE DBMS_SQLTUNE.CREATE_SQLSET（：workload_name，'test purposeV）;

步骤3从用户定义的表SH.USER_WORKLOAD加载SQL调优集

DECLARE

sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR; /*a sqlset cursor variable*/

BEGIN

OPEN sqlset_cur FOR

SELECT

SQLSET_ROW(null, sql_text, null, null, username, null,

null, 0,0,0,0,0,0,0,0,0,null, 0,0,0,0)

AS ROW

FROM USER_WORKLOAD;

DBMS_SQLTUNE.LOAD_SQLSET(:workload_name, sqlset_cur);

END;

步骤4创建名为MYTASK的任务

EXECUTE :task_name := 'MYTASK';

EXECUTE DBMS_ADVISOR.CREATE_TASK（'SQL Access Advisor'，：task_id，：task_name）;

步骤5设置任务参数

EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER（：task_name，'STORAGE_CHANGE'，100）;

EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER（：task_name，'ANALYSIS_SCOPE，INDEX'）;

步骤6在SQL调优集和任务之间创建链接

EXECUTE DBMS_ADVISOR.ADD_STS_REF（：task_name，：workload_name）;

步骤7执行任务

EXECUTE DBMS_ADVISOR.EXECUTE_TASK（：task_name）;

步骤8查看建议

-- See the number of recommendations and the status of the task.

SELECT rec_id, rank, benefit

FROM user_advisor_recommendations WHERE task_name = :task_name;

有关更多详细信息，请参阅dav_笔记13的“查看建议”或dav_笔记13的“生成SQL脚本”。

-- See recommendation for each query.

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;

-- See the actions for each recommendations.

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;

-- See what the actions are using sample procedure.

SET SERVEROUTPUT ON SIZE 99999

EXECUTE show_recm(:task_name);

步骤9生成脚本以实施建议

EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name),

'ADVISOR_RESULTS', 'Example1_script.sql');

使用任务模板生成建议

以下示例创建模板，然后使用它来创建任务。然后，它使用此任务从用户定义的表生成建议，类似于dav_笔记13的“来自用户定义的工作负载的建议”。

CONNECT SH/SH;

VARIABLE template_id NUMBER;

VARIABLE template_name VARCHAR2(255);

步骤1创建名为MY_TEMPLATE的模板

EXECUTE :template_name := 'MY_TEMPLATE';

EXECUTE DBMS_ADVISOR.CREATE_TASK ( -

'SQL Access Advisor',:template_id, :template_name, is_template=>'TRUE');

步骤2设置模板参数

为推荐索引和物化视图设置命名约定。