在Oracle数据库出现性能问题时，使用Oracle本身的工具包，给出合理的调优建议是比较省力的做法。


下面两种包的介绍及具体做法。

       SQL Tuning Advisor 粒度最小的调整工具.

将一条或多条SQL语句做为输入内容，分析所有访问路径，然后生成改进SQL语句的建议,并提出索引，物化视图和分区方案来提高应用程序的总体性能。
另外,在维护窗口中,Oracle也会针对Automatic Workload Repository(AWR) 来确定和记录的高负载语句自动化运行SQL Tuning Advisor

SQL Access Advisor 

它涉及工作量中所有SQL语句，并提出索引，特化视图和分区方案来提高应用程序的总体性能。

小区别:

SQL Tuning Advisor 分别调整每条SQL语句(我之前的博文已经介绍这种方式如何使用)

SQL Access Advisor 同时调整所有SQL语句


SQL Access Advisor:


使用 dbms_advisor.quick_tune来对单个SQL语句进行调整
它的执行很像SQL Tuning Advisor,但执行的分析层次却深入得多，生成的建议也更多。

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

?

1
2
--输出文件目录
create directory tune_scripts as '/demo/tune_scripts' ;

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


?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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两个用户的表的顾问功能,所能要分析的表不要用这两个用户建。

官网相关文档: 

https://docs.oracle.com/cd/E11882_01/server.112/e16638/advisor.htm#PFGRF94881