[每日一题] 11gOCP 1z0-053 :2013-10-5 SQL Access Advisor.........................................23

转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/12314717

答案BC

 

SQL访问顾问

SQL访问顾问（SQL Access Advisor）有要对段访问使用工作负荷规范进行总体SQL性能的分析。工作负荷规范的类型如下：

● 单条SQL语句

● SQL语句调整集

● 当前SQL缓存内容

● 来自一组对象的DDL假定的工作负荷

SQL访问顾问给出的建议信息包括新建索引丶物化视图和分区。有一个通过Enterprise Manager实现的图形界面和一个PL/SQL API.

1丶  通过Database Control使用SQL Access Advisor

下面列出了创建一系列建议的4个步骤。

(1)   创建任务。

(2)   定义工作负荷。

(3)   生成建议。

(4)   查看并实施建议。

         如1-1下图所示Advisor Central页面上，单击SQL Access Advisor链接。可以在此页面中执行以下两个任务之一：确认是否在使用诸如索引和视物化视图的现有结构或是提出使用新结构的建议。

                                                                                                                                          图1-1

        在本例中需要查找新访问结构，因些选择第二个单选按钮。如果选中的Inherit Options复选框，可以选择适用环境的模板（如OLTP或OLAP）。单击Continue按钮时，将看到如1-2图的第一个步骤。

                                                                                                                  图1-2

       对于调整活动的来源，可以选择以下三个来源之一：缓存中的最新SQL丶现有的SQL调整集或是对于一个或多个模式中的对象的结构，基于可能出现的查询类型生成的工作负荷。本例需要分析所有当前和最新SQL活动。因此，单击相应的单选按钮，然后单击NEXT按钮。

      此时按将显示下一个页面，如1-3图所示，在此页面中选择SQL Access Advisor建议的访问结构类型：索引丶物化视图和分区。另外，可以向SQL Access Advisor发出指令，以便仅针对高版本语句执行有限分析，或针对指定工作负荷中的表之间的所有关系执行用时较长的分析。在Advanced Options部分中，可以根据磁盘空间的大小限制进一步完善分析，并为建议的索引和物化视图指定备用位置。选中Indexes和Materialized Views复选框，然后选中Comprehensive单选按钮。最后单击Next按钮。

 

                                                                                                                               图1-3

              如1-4图中显示的下一个页面指定调整任务的调度选项。可以看到，EM将自动创建任务。此页面中的其他选项包括SQL Access Advistor生成的日志记录量丶此任务在数据库中的执行时间长度丶分配给任务的总时间以及何时启动此任务。

        此图中接受默认选项，然后单击Next按钮，继续执行向导的第4个步骤，如1-5图所示。第4个步骤总结了选择的选项，并允许在提交作业之前修改选项。

                                                                                                                            

                                                                                                                                                     图 1-4

        注意图1-5中的SHOW ALL按钮。它将显示向导生成的API调用。这对于脚本调整作业（可以作为批处理作业的一部分执行）来说是个很好的代码来源，脚本调整作业中包含了无法轻易地在EM中以复执行的其他SQL命令和进程。

                                                                                                                                图1-5

              单击图1-5中的Submit按钮启动分析。在Advisor Central页面中，可以监视作业的进度。作业完成时，选择相应的作业，然后单击View Result按钮。图1-6中的页面总结了如果实现第二个选项中的建议可以得到的改进。SQL Statements选项卡显示已分经分析的语句，并且给出实现建议的选项。Details选项卡概括为运行分析选择的选顶。在这个特定分析中，如果实现了建议信息，将近半数的SQL语句可以享受到极大的好处。

2丶  通过DBMS_ADVISOR使用SQL Acces Advisor

通过DBMS_ADVISOR使用SQL Acces Advisor十分复杂。在运行大多数日常分析，使用EM会更加方便。

（1）建表并生成测试数据

SQL> connect scott/scott
Connected.
SQL> drop table tb_test;
SQL> create table tb_test(id number not null,name varchar2(30));
Table created.
SQL> create index idx_tb_test on tb_test(id);
Index created.
SQL> declare
begin
  for i in 1 .. 100000 loop
    insert into tb_test values (i, \'test\');
    commit;
  end loop;
end;
 

（2）分析表

connect / as sysdba
begin
  dbms_stats.gather_table_stats(ownname => \'SCOTT\', tabname => \'TB_TEST\',cascade => true);
end;

（3）使用dbms_advisor.quick_tune生成优化建议

connect / as sysdba
declare
  l_task_name VARCHAR2(255);
  l_sql_stmt  VARCHAR2(4000);
begin
  l_sql_stmt  := \'select /*+ full(t) */ * from scott.tb_test t where t.id = :1\';
  l_task_name := \'MY_FULL_ACCESS_TEST\';
  DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR,
                          l_task_name,
                          l_sql_stmt);
  exception when others then dbms_output.put_line(sqlerrm);                     
end;

我们这里造一个走全表扫描的索引,很明显走索引才是正确的,这里的目的主要是看oracle会给出什么样的建议.

（4）查看优化建议

SQL> set serveroutput on;
SQL> set long 999999999;
SQL> begin
       show_recm(\'MY_FULL_ACCESS_TEST\');
     end;
=========================================
Task_name = MY_FULL_ACCESS_TEST
Action ID: 1
Command : RETAIN INDEX
Attr1 (name)      : "SCOTT"."IDX_TB_TEST_N1"
Attr2 (tablespace):
Attr3             : "SCOTT"."TB_TEST"
Attr4             : BTREE
Attr5             :
----------------------------------------
=========END RECOMMENDATIONS============
PL/SQL procedure successfully completed.

从以上输出可以看出oracle给出的建议是走索引.

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

答案A不正确，Wordload 包含一个或者多个SQL 语句，及其统计信息和每个语句的属性。

答案D不正确，SQL AccessAdvisor 索引的推荐包括bitmap，function-based，B-tree 索引。

QQ：252803295

技术交流QQ群：
DSI&Core Search  Ⅰ 群：127149411（2000人技术群:未满）
DSI&Core Search  Ⅱ 群：177089463（1000人技术群:未满）
DSI&Core Search  Ⅲ 群：284596437（500人技术群:未满）
DSI&Core Search  Ⅳ 群：192136702（500人技术群:未满）
DSI&Core Search  Ⅴ 群：285030382（500人闲聊群:未满）

MAIL：[e-mail]

BLOG： http://blog.csdn.net/guoyjoe

WEIBO：

ITPUB： http://www.itpub.net/space-uid-28460966.html

OCM：