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

loinooo

  在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 Tuning Advisor :
1. 在匿名PL/SQL块中，为SQL 语句定义调整任务。
declare   
    tune_task_name varchar2(30)     ;  
    bad_sql_stmt clob;  
begin  
    bad_sql_stmt := 'select distinct id from demo.txcl';  
    tune_task_name := dbms_sqltune.create_tuning_task  
        ( sql_text => bad_sql_stmt,  
          user_name => 'DEMO',  
          scope => 'COMPREHENSIVE',  
          time_limit => 60,  
          task_name => 'xcl_sql_tuing_task',  
          description => 'See what is wrong with the SELECT'  
            ) ;  
end;  
/  
2.  设置任务时指定的时限值是60分钟.
begin  
    dbms_sqltune.set_tuning_task_parameter  
        (task_name => 'xcl_sql_tuing_task',  
         parameter => 'TIME_LIMIT',  
         value => 30  
         );  
end;  
/  
3. 使用EXECUTE_TUNING_TASK过程启动调整任务
begin  
    dbms_sqltune.execute_tuning_task   
     (task_name => 'xcl_sql_tuing_task');  
end;  
/  
4. 通过连接DBA_ADVISOR_TASKS和V$ADVISOR_PROGRESS来查任务状态
select task_name,status,sofar,totalwork
from dba_advisor_tasks   
join v$advisor_progress using(task_id)   
where task_name = 'xcl_sql_tuing_task';  
5. 查看调整任务给出的建议
select dbms_sqltune.report_tuning_task('xcl_sql_tuing_task') from dual;  

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两个用户的表的顾问功能,所能要分析的表不要用这两个用户建。

官网相关文档: http://docs.oracle.com/cd/E11882 ... isor.htm#PFGRF94881

群殴死999

爱情里没有谁对谁错，因为没有一个标准可以衡量