SQL Access Advisor的使用

SQL Access Advisor的使用
环境:
OS:Red Hat Linux As 5
DB:10.2.0.4
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;。