Use the SQL Access Advisor

数据库SQL Access Advisor使用步骤：

 
基本步骤：
1.创建一个任务，包括SQL负载数据对象。
2.设置TASK以及SQLWKLD的参数。
3.收集SQL负载。
4.分析数据，生成建议。
 
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 SQL Access Advisor task.
  DBMS_ADVISOR.create_task (
    advisor_name => DBMS_ADVISOR.sqlaccess_advisor,
    task_name    => l_taskname,
    task_desc    => l_task_desc);
   
  -- Reset the task.为了初始化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);
  END IF;
 
  -- Link the workload to the task.建立workload和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);
 
  -- 将当前sql cache里的信息导入workload
  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 resulting script.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_script('test_sql_access_task') AS script
FROM   dual;
SET PAGESIZE 24
 
 
 
-- 对单独的SQL语句，可以使用DBMS_ADVISOR.quick_tune过程，较为快速的分析
BEGIN
  DBMS_ADVISOR.quick_tune(
    advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
    task_name    => 'test_quick_tune',
    attr1        => 'select * from PART_TAB1 where UPPER(name) =''V$LOGFILE''');
END;
/
 
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_script('test_quick_tune') AS script
FROM   dual;
SET PAGESIZE 24
 

-- 相关视图
DBA_ADVISOR_TASKS           --现有任务的基本信息
DBA_ADVISOR_LOG             --现有任务的状态信息
DBA_ADVISOR_FINDINGS        --现有任务发现的问题
DBA_ADVISOR_RECOMMENDATIONS --发现文件的推荐优化方案