SQL：SQL Access Advisor.Quick Tune

一、功能：  
     Materialized views, partitions, and indexes are essential when tuning a database to achieveoptimum performance for complex, data-intensive queries.
       SQL AccessAdvisor helps you achieve your performance goals by recommending the proper set of materialized views, materialized view logs, partitions,
    and indexes for agiven workload.
    --数据库对负责查询的优化的本质是对物化视图，分区和索引的优化，而SQL Access Advisor 可以帮助我们实现优化，其可以提供物化视图，分区和索引的推荐值。

二、使用SQLAccess Advisor需要的权限
      1、ADVISOR权限
           grant advisor to xxx;
      2、指定表的select 的权限，否则会报QSM-774错误。
            
      3、 ADMINISTER SQL TUNING SET的权限来创建和管理workload。
            grant ADMINISTER SQL TUNING SET to xxx;

       4、常见错误:

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


      
三、手工生成SQL Access Advisor 示例

2、Quick Tune
If you just wantto tune an individual statement you can use the QUICK_TUNE procedureas follows.
--如果仅仅是调整一个独立的语句，可以使用QUICK_TUNE过程：

BEGIN
 DBMS_ADVISOR.quick_tune(
   advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
   task_name    =>'emp_quick_tune',
   attr1        => 'SELECT e.*FROM emp e WHERE UPPER(e.ename) = ''SMITH''');
END;
/

Any recommendations can then be displayed using the previous query with the correct task name specified.
查询输出结果和之前的一样，使用:
    Select DBMS_ADVISOR.get_task_script('emp_quick_tune') from dual;

      1、DBMS_ADVISOR
          The DBMS_ADVISOR package can be used to create and execute any advisor tasks, including SQL Access Advisor tasks. The following example shows how it is used to create, executeand display a typical SQL Access Advisor script for the current workload.
--DBMS_ADVISOR 包可以用来创建和执行advisor 任务。

DECLARE
 l_taskname     VARCHAR2(30)  := 'test_sql_access_task';
 l_task_desc    VARCHAR2(128)  := 'Test SQL Access Task';
 l_wkld_name    VARCHAR2(30)   := 'test_work_load';
 l_saved_rows   NUMBER         := 0;
 l_failed_rows  NUMBER         := 0;
 l_num_found    NUMBER;
BEGIN
  -- Create an SQLAccess Advisor task.
 DBMS_ADVISOR.create_task (
   advisor_name => DBMS_ADVISOR.sqlaccess_advisor,
   task_name    => l_taskname,
   task_desc    => l_task_desc);
  -- Reset the task.
 DBMS_ADVISOR.reset_task(task_name => l_taskname);
  -- Create a workload.
 SELECT COUNT(*)
 INTO   l_num_found
 FROM   user_advisor_sqlw_sum
 WHERE  workload_name =l_wkld_name;

  IF l_num_found = 0 THEN
   DBMS_ADVISOR.create_sqlwkld(workload_name => l_wkld_name);
  ENDIF;

  -- Link the workload to the task.
 SELECT count(*)
   INTO   l_num_found
 FROM   user_advisor_sqla_wk_map
 WHERE  task_name     = l_taskname
 AND    workload_name =l_wkld_name;

  IF l_num_found = 0 THEN
   DBMS_ADVISOR.add_sqlwkld_ref(
     task_name     => l_taskname,
     workload_name => l_wkld_name);
  END IF;

  -- Set workload parameters.
  DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name,'MODULE_LIST', DBMS_ADVISOR.ADVISOR_UNUSED);
  DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'SQL_LIMIT',DBMS_ADVISOR.ADVISOR_UNLIMITED);
  DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'ORDER_LIST', 'PRIORITY,OPTIMIZER_COST');
  DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
  DBMS_ADVISOR.import_sqlwkld_sqlcache(l_wkld_name, 'REPLACE', 2,l_saved_rows, l_failed_rows);
  -- Set task parameters.
  DBMS_ADVISOR.set_task_parameter(l_taskname, '_MARK_IMPLEMENTATION','FALSE');
  DBMS_ADVISOR.set_task_parameter(l_taskname, 'EXECUTION_TYPE','INDEX_ONLY');
  DBMS_ADVISOR.set_task_parameter(l_taskname, 'MODE', 'COMPREHENSIVE');
  DBMS_ADVISOR.set_task_parameter(l_taskname, 'STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED);
  DBMS_ADVISOR.set_task_parameter(l_taskname, 'DML_VOLATILITY', 'TRUE');
  DBMS_ADVISOR.set_task_parameter(l_taskname, 'ORDER_LIST','PRIORITY,OPTIMIZER_COST');
  DBMS_ADVISOR.set_task_parameter(l_taskname, 'WORKLOAD_SCOPE','PARTIAL');
  DBMS_ADVISOR.set_task_parameter(l_taskname, 'DEF_INDEX_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
  DBMS_ADVISOR.set_task_parameter(l_taskname, 'DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
  DBMS_ADVISOR.set_task_parameter(l_taskname, 'DEF_MVIEW_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
  DBMS_ADVISOR.set_task_parameter(l_taskname, 'DEF_MVIEW_OWNER', DBMS_ADVISOR.ADVISOR_UNUSED);
  -- Execute the task.
  DBMS_ADVISOR.execute_task(task_name => l_taskname);
END;
/
-- Display the resultingscript.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_script('test_sql_access_task') AS script FROM  dual;
SET PAGESIZE 24
The value for the SET LONG commandshould be adjusted to allow the whole script to be displayed.
在我测试环境上的输入结果如下：
PL/SQL procedure successfully completed.
SCRIPT                                                                         
--------------------------------------------------------------------------------
Rem  SQL AccessAdvisor: Version 10.2.0.4.0 - Production                        
Rem                                                                             
Rem  Username:        SYS                                                      
Rem  Task:            test_sql_access_task                                      
Rem  Executiondate:  31/01/2012 21:50                                          
Rem                                                                            

CREATE BITMAP INDEX "QSOA"."DATA_OA_MESSAGE_IDX$$_167F0001"                     
    ON"QSOA"."DATA_OA_MESSAGE"                                                
   ("MESS_TYPE")                                                              
    COMPUTE STATISTICS;                                                                                       

CREATE INDEX"ZHAOKA"."CFG_GAME_AREA_S_IDX$$_167F0004"
    ON "ZHAOKA"."CFG_GAME_AREA_SERVER"                                          
   ("AREA_ID","AREA_NAME","SERVER_ID","SERVER_NAME")                           
    COMPUTE STATISTICS;    

3、Related Views
The following views can be used to display the SQL Access Advisor output without using Enterprise Manager or the get_task_script function:
--可以使用以下视图来查看advisor的输出：
(1)    DBA_ADVISOR_TASKS：Basic information about existing tasks.
(2)    DBA_ADVISOR_LOG ：Status information about existing asks.
(3)    DBA_ADVISOR_FINDINGS ： Findings identified for an existing task.
(4)    DBA_ADVISOR_RECOMMENDATIONS ： Recommendations for the problems identified by an existing task.


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

1. 指定一个存放输出文件的目录
  --输出文件目录
   create directory tune_scripts as '/home/oracle/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 a ';
    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_01/server.112/e16638/advisor.htm#PFGRF94881
MAIL:xcl_168@aliyun.com
BLOG:http://blog.csdn.net/xcl168