advisors oracle,Oracle调整顾问(SQL Tuning Advisor 与 SQL Access Advisor )

SQL Access Advisor:

使用 dbms_advisor.quick_tune来对单个SQL语句进行调整

它的执行很像SQL Tuning Advisor,但执行的分析层次却深入得多，生成的建议也更多。

1. 指定一个存放输出文件的目录

--输出文件目录

create directory tune_scripts as '/demo/tune_scripts';

2.采集，分析并给出建议

declare

v_task_name VARCHAR2(255);

v_sql_stmt  VARCHAR2(4000);

v_tune_sql_filename VARCHAR2(255);

v_cfile_directory VARCHAR2(255);

begin

v_sql_stmt := 'select  * from  demo.txcl t where t.id = :1 ';

v_task_name :='tune_demo_txcl';

v_tune_sql_filename :=  'tune_demo_txcl.sql';

v_cfile_directory := 'TUNE_SCRIPTS';

dbms_stats.gather_table_stats(

ownname => 'DEMO',

tabname => 'TXCL',

cascade => true);

--dbms_advisor.reset_task(v_task_name);

--分析并得到建议

dbms_advisor.quick_tune(DBMS_ADVISOR.SQLACCESS_ADVISOR,

v_task_name,

v_sql_stmt);

--将建议输出到指定文件中

-- select * from dba_advisor_actions where task_name ='tune_demo_txcl' ;

-- select * user_advisor_sqla_wk_stmts where task_name ='tune_demo_txcl'  ;

dbms_advisor.create_file(

dbms_advisor.get_task_script(v_task_name),

v_cfile_directory,

v_tune_sql_filename

);

--删除任务

dbms_advisor.delete_task(v_task_name);

exception

when others then

dbms_output.put_line(sqlerrm);

end;

/

常见错误:

ORA-13600: error encountered in Advisor

QSM-00794: the statement can not be stored due to a violation of the invalid table reference filter

原因是dbms_advisor.quick_tune不支持为SYS和SYSTEM两个用户的表的顾问功能,所能要分析的表不要用这两个用户建。