SQL Access Advisor的 DBMS_ADVISOR.QUICK_TUNE 使用

gholay發表於2014-03-08
SQLUI
SQL Access Advisor是伴隨著10G出現的一個優化工具,提供對錶分割槽,物化檢視,索引,物化檢視日誌優化建議.下邊通過對單個SQL的執行,檢視Oracle給出的建議.
 
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;
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29196873/viewspace-1103534/，如需轉載，請註明出處，否則將追究法律責任。

相關文章