Oracle 19C 查看SQL Access Advisor任务结果

原创 Asher.HU 2021-02-04

1351

您可以使用几个数据字典视图查看SQL Access Advisor生成的每个建议。

表25-2中总结了这些视图。但是，使用DBMS_ADVISOR.GET_TASK_SCRIPT过程或Cloud Control 更容易，该过程或图形化显示建议并提供超链接以快速查看哪些SQL语句可从建议中受益。

SQL Access Advisor产生的每个建议都链接到它所受益的SQL语句。每个建议对应一个或多个动作。每个动作都有一个或多个属性。

每个动作都具有与访问结构属性有关的属性。每个适用的访问结构的名称和表空间位于的ATTR1和ATTR2列中USER_ADVISOR_ATTRIBUTES。每个新访问结构所占用的空间在该NUM_ATTR1列中。每个操作的其他属性都不同。

表25-2显示任务结果的视图

数据字典视图（DBA，USER）	描述
`DBA_ADVISOR_TASKS`	显示有关顾问程序任务的信息。要查看SQL Access Advisor任务，请选择`ADVISOR_NAME = 'SQL Access Advisor'.`
`DBA_ADVISOR_RECOMMENDATIONS`	显示数据库中所有建议的分析结果。推荐可以有多个与之关联的动作。该`DBA_ADVISOR_ACTIONS`视图描述了动作。一项建议还指出了一组提出该建议的理由/理由的理由。该`DBA_ADVISOR_RATIONALE`视图描述了基本原理。
`DBA_ADVISOR_ACTIONS`	显示有关与数据库中所有建议关联的操作的信息。每个动作由指定`COMMAND`并`ATTR1`通过`ATTR6`列。每个命令定义如何使用属性列。
`DBA_ADVISOR_RATIONALE`	显示有关数据库中所有建议的基本原理的信息。
`DBA_ADVISOR_SQLA_WK_STMTS`	在执行SQL Access Advisor分析后，显示有关数据库中所有工作负载对象的信息。成本前和成本后的数字是根据优化器的估计成本（如所示`EXPLAIN PLAN`）表示的，没有使用建议的访问结构。

假设条件

本教程假定您要查看在“ 执行SQL Access Advisor任务 ”中执行的任务的结果。

要查看SQL Access Advisor任务的结果，请执行以下操作：

使用适当的特权将SQL * Plus连接到数据库，然后查询顾问程序建议。

例如，执行以下语句（包括示例输出）：

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;

    REC_ID       RANK    BENEFIT
---------- ---------- ----------
         1          1        236
         2          2        356

前面的输出显示了rec_idSQL Access Advisor运行产生的建议（）以及它们的排名和总收益。等级是对推荐帮助查询的重要性的度量。好处是，使用该建议的所有查询的执行成本（就优化程序成本而言）总体上得到了改善。

确定哪个查询从哪个推荐中受益。

例如，执行以下查询USER_ADVISOR_SQLA_WK_STMTS（包含示例输出）：

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;

SQL_ID            REC_ID    PRECOST   POSTCOST PERCENT_BENEFIT
------------- ---------- ---------- ---------- ---------------
fn4bsxdm98w3u          2        578        222      61.5916955
29bbju72rv3t2          1       5750       5514      4.10434783
133ym38r6gbar          0        772        772               0

EXPLAIN PLAN无论是否进行建议的访问结构更改，预成本和后成本数字都是根据优化器的估算成本（如中所示）得出的。

显示这组建议的不同操作数。

例如，使用以下查询（包括示例输出）：

SELECT 'Action Count', COUNT(DISTINCT action_id) cnt
FROM   USER_ADVISOR_ACTIONS 
WHERE  TASK_NAME = :task_name;

'ACTIONCOUNT        CNT
------------ ----------
Action Count          4

显示这组建议的操作。

例如，使用以下查询（包括示例输出）：

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;

    REC_ID  ACTION_ID COMMAND
---------- ---------- ------------------------------
         1          1 PARTITION TABLE
         1          2 RETAIN INDEX
         2          1 PARTITION TABLE
         2          3 RETAIN INDEX
         2          4 RETAIN INDEX

显示建议的属性。

例如，创建以下PL / SQL过程show_recm，然后执行它以查看操作的属性：

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

以下输出显示了建议中操作的属性：

=========================================
Task_name = MYTASK
Action ID: 1
Command : PARTITION TABLE
Attr1 (name)      : "SH"."SALES"
Attr2 (tablespace):
Attr3             : ("TIME_ID")
Attr4             : INTERVAL
Attr5             :
----------------------------------------
Action ID: 2
Command : RETAIN INDEX
Attr1 (name)      : "SH"."PRODUCTS_PK"
Attr2 (tablespace):
Attr3             : "SH"."PRODUCTS"
Attr4             : BTREE
Attr5             :
----------------------------------------
Action ID: 3
Command : RETAIN INDEX
Attr1 (name)      : "SH"."TIMES_PK"
Attr2 (tablespace):
Attr3             : "SH"."TIMES"
Attr4             : BTREE
Attr5             :
----------------------------------------
Action ID: 4
Command : RETAIN INDEX
Attr1 (name)      : "SH"."SALES_TIME_BIX"
Attr2 (tablespace):
Attr3             : "SH"."SALES"
Attr4             : BITMAP
Attr5             :
----------------------------------------
=========END RECOMMENDATIONS============

也可以看看：

“ DBA_ADVISOR_ACTIONS视图中的操作属性 ”
《 Oracle数据库PL / SQL软件包和类型参考》，有关Attr5和的详细信息Attr6

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 19C 查看SQL Access Advisor任务结果

评论