2016年9月 – 但行好事莫问前程

dbms_addm执行oracle数据库诊断

Posted on 2016年9月30日2016年9月30日 by YongJing(敬勇)

为了诊断数据库性能问题，首先查看ADDM分析报告，通常它是在生成AWR快照时自动创建的。如果缺省的分析不能满足，可以手动执行ADDM分析操作。ADDM可以对任何两个AWR快执行分析，只要快照仍然存储在数据库中而没有被清除掉。当在生成AWR快照时如果出现了严重错误，ADDM将不会对实例进行分析。在这种情况下，ADDM将只能对实例的最大子集（没有出错的部分）进行分析。

手动执行ADDM分析可以使用dbms_addm包来执行操作，ADDM分析主要包括以下几种模式:
.以数据库模式来执行ADDM分析
.以实例模式来执行ADDM分析
.以部分模式来执行ADDM分析
.显式ADDM分析报告

以数据库模式来执行ADDM分析
对于Oracle RAC来说，可以以数据库模式来执行ADDM来分析数据库的所有实例。对于单实例数据库，仍然可以以数据库模式来执行ADDM分析，如果以实例模式运行那么ADDM将简化其行为。

使用dbms_addm.analyze_db过程来以数据库模式执行ADDM:

begin
  dbms_addm.analyze_db(
    task_name       in out varchar2,
    begin_snapshot  in     number,
    end_snapshot    in     number,
    db_id           in     number :=null
    );
end;
/

task_name参数指定将要被创建的分析任务名称。begin_snapshot参数指定分析周期的开始快照。 end_snapshot参数指定分析周期的快照。db_id参数指定将要被分析的数据库标识。如果没有指定，这个参数将使用当前所连接的数据库标识。

下面的例了创建一个以数据库模式来执行ADDM的任务，并且对快照481到484之间的时间周期对整个数据库执行性能诊断。

SQL> var tname varchar2(30)
SQL> begin
  2    :tname:='ADDM for snapshot 481 to 484';
  3    dbms_addm.analyze_db(:tname,481,484);
  4  end;
  5  /

PL/SQL procedure successfully completed.

以实例模式执行ADDM
为了对数据库的特定实例进行分析，可以以实例模式来执行ADDM。使用dbms_addm.analyze_inst过程来进行操作:

begin
  dbms_addm.analyze_inst(
    task_name       in out varchar2,
    begin_snapshot  in     number,
    end_snapshot    in     number,
    instance_number in     number :=null,
    db_id           in     number :=null
    );
end;
/

task_name参数指定将要被创建的分析任务名称。begin_snapshot参数指定分析周期的开始快照。 end_snapshot参数指定分析周期的快照。instance_number参数指定将会被分析的实例编号，如果没有指定，将会使用当前所连接的实例。db_id参数指定将要被分析的数据库标识。如果没有指定，这个参数将使用当前所连接的数据库标识。

下面的例子以实例模式来执行ADDM，并且对实例1的471到474的快照执行性能论断:

SQL> var tname varchar2(30)
SQL> begin
  2    :tname:='my addm for 471 to 474';
  3    dbms_addm.analyze_inst(:tname,471,474,1);
  4  end;
  5  /

PL/SQL procedure successfully completed.

以部分模式来执行ADDM
为了对所有实例中的部分实例执行分析，可以以部分模式来执行ADDM。可以使用 dbms_addm.analyze_partial过程来执行:

begin
  dbms_addm.analyze_partial(
    task_name       in out varchar2,
    instance_number in     number,
    begin_snapshot  in     number,
    end_snapshot    in     number,
    db_id           in     number :=null
    );
end;
/

task_name参数指定将要被创建的分析任务名称。instance_number参数用分号来隔开将会被分析的实例编号。begin_snapshot参数指定分析周期的开始快照。end_snapshot参数指定分析周期的快照。 db_id参数指定将要被分析的数据库标识。如果没有指定，这个参数将使用当前所连接的数据库标识。

下面的例子将以部分模式来创建ADDM诊断任务，并且对实例1,2,4的137到145之间的快照执行性能诊断：

var tname varchar2(30)
begin
  :tname:='my addm for 137 to 145';
  dbms_addm.analyze_partial(:tname,'1,2,4',137,145);
end;
/

显示ADDM报告
为了以文本方式显示一个已经执行的ADDM任务的报告，可以使用dbms_addm.get_report函数:

dbms_addm.get_report(task_name in varchar2 return clob);

下面的例子使用tname变量指定addm任务名，使用dbms_addm.get_report来以文本方式来显示ADDM报告:

SQL> set long 1000000 pagesize 0;
SQL> select dbms_addm.get_report(:tname) from dual;
          ADDM Report for Task 'my addm for 471 to 474'
          ---------------------------------------------

Analysis Period
---------------
AWR snapshot range from 471 to 474.
Time period starts at 28-SEP-16 03.00.18 AM
Time period ends at 28-SEP-16 06.00.39 AM

Analysis Target
---------------
Database 'SJJH' with DB ID 4134995129.
Database version 11.2.0.4.0.
ADDM performed an analysis of instance sjjh, numbered 1 and hosted at
localhost.localdomain.

Activity During the Analysis Period
-----------------------------------
Total database time was 13999 seconds.
The average number of active sessions was 1.29.

Summary of Findings
-------------------
   Description                               Active Sessions      Recommendation
s                                              Percent of Activity
   ----------------------------------------  -------------------  --------------
- 1  Top SQL Statements                        .91 | 69.99          6
2  Top Segments by "User I/O" and "Cluster"  .17 | 13.43          3
3  Undersized Redo Log Buffer                .13 | 10.23          1
4  Log File Switches                         .1 | 7.59            2
5  Buffer Busy - Hot Objects                 .06 | 4.37           3
6  Commits and Rollbacks                     .04 | 2.72           1
7  "Network" Wait Class                      .03 | 2.24           0


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


          Findings and Recommendations
          ----------------------------

Finding 1: Top SQL Statements
Impact is .91 active sessions, 69.99% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.

   Recommendation 1: SQL Tuning
   Estimated benefit is .27 active sessions, 21.07% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the CREATE MATERIALIZED VIEW  statement with SQL_ID
      "7v89hvfv38196" for possible performance improvements. You can
      supplement the information given here with an ASH report for this
      SQL_ID.
      Related Object
         SQL statement with SQL_ID 7v89hvfv38196.
         create materialized view mt_fee_fin build immediate
         refresh fast with primary key on demand
         start with sysdate next sysdate+1
         as select * from mt_fee_fin@dbl_yb
   Rationale
      The SQL Tuning Advisor cannot operate on CREATE MATERIALIZED VIEW
      statements.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.

   Recommendation 2: SQL Tuning
   Estimated benefit is .17 active sessions, 13.41% of total activity.
   -------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the INSERT statement with SQL_ID
      "f64qufxuu0r5g".
      Related Object
         SQL statement with SQL_ID f64qufxuu0r5g.
         /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO
         "SJGX_YB"."DWGRBTXX"("个人保险号","姓名","身份证","性别","人员类别","单
位代码","单位名称","应缴
         期间","险种","款项","补退金额","计算基数") SELECT
         "I"."INSR_CODE","I"."NAME","I"."IDCARD",DECODE("I"."SEX",0,'女','男'),"
          BPT"."PERS_NAME","C"."CORP_CODE","C"."CORP_NAME","IP"."PERIOD","PDI".
         "INSR_DETAIL_NAME","PMI"."MONEY_NAME","IP"."PAY_MONEY","IP"."CALC_BAS
         E" FROM "LV_INDIPAR" "IP","LV_CROPFUNDPAR" "CF","LV_INSR_TOPAY"
         "IT","BS_CORP" "C","BS_INSURED" "I","BS_PERSON_TYPE"
         "BPT","PFS_INSUR_DETAIL_INFO" "PDI","PFS_MONEY_INFO" "PMI" WHERE
         "IT"."PAY_INFO_NO"="CF"."PAY_INFO_NO" AND
         "CF"."MONEY_NO"="IP"."MONEY_NO" AND "PMI"."MONEY_ID"="CF"."MONEY_ID"
         AND "C"."CORP_ID"="IT"."CORP_ID" AND "I"."INDI_ID"="IP"."INDI_ID" AND
         "IT"."INSR_DETAIL_CODE"="PDI"."INSR_DETAIL_CODE"(+) AND
         ("IT"."BUSI_ASG_NO"=(-999) OR "IT"."BUSI_ASG_NO"=(-998) OR
         "IT"."BUSI_ASG_NO"=(-997) OR "IT"."BUSI_ASG_NO"=(-981) OR
         "IT"."BUSI_ASG_NO"=(-980) OR NVL("IT"."BUSI_ASG_NO",0)=0) AND
         "IT"."INDI_PAY_FLAG"=0 AND "BPT"."PERS_TYPE"="I"."PERS_TYPE" AND
         "BPT"."CENTER_ID"='430701' AND "IT"."TOPAY_TYPE"=3 AND
         "IT"."INSR_DETAIL_CODE"<>'21'
   Rationale
      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "f64qufxuu0r5g" was executed 18 times and had
      an average elapsed time of 87 seconds.
   Rationale
      Full scan of TABLE "SJGX_YB.LV_INDIPAR" with object ID 89473 consumed
      77% of the database time spent on this SQL statement.
   Rationale
      Top level calls to execute the PL/SQL statement with SQL_ID
      "2wkfgbnhtqcj9" are responsible for 100% of the database time spent on
      the INSERT statement with SQL_ID "f64qufxuu0r5g".
      Related Object
         SQL statement with SQL_ID 2wkfgbnhtqcj9.
         DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
         broken BOOLEAN := FALSE; BEGIN
         dbms_refresh.refresh('"SJGX_YB"."DWGRBTXX"'); :mydate := next_date;
         IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

   Recommendation 3: SQL Tuning
   Estimated benefit is .16 active sessions, 12.12% of total activity.
   -------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the INSERT statement with SQL_ID
      "c1fw0514uxxrs".
      Related Object
         SQL statement with SQL_ID c1fw0514uxxrs.
         INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "SJGX_YB"."V_DWJKXX" select
         单位名称,
         单位编码,
         组织机构代码,
         业务单号,
         缴款来源,
         经办人,
         缴款日期,
         缴款金额,
         险种名称,
         分配金额,
         使用待转金额,
         生成待转金额,
         审核状态,
         凭证号
         from
         (
         select    distinct(a.busi_bill_sn) as 业务单号,
         bc.corp_name 单位名称,
         bc.corp_code 单位编码,
         bc.insur_org_code 组织机构代码,
         pm.pay_method_name as 缴款来源,
         a.make_bill 经办人,
         to_char(a.make_bill_tm, 'yyyy-mm-dd') 缴款日期,
         c.pay_money 缴款金额,
         d.insr_detail_name 险种名称,
         c.pay_money-c.bld_wait_money+c.use_wait_money as 分配金额,
         c.use_wait_money 使用待转金额,
         c.bld_wait_money 生成待转金额,
         (case a.audit_flag
         when 1 then
         '已审核'
         else
         '未审核'
         end) 审核状态,
         id.cred_no 凭证号
         from lv_busi_bill a,
         lv_busi_record b,
         lv_busi_assign c,
         (select insr_detail_code, insr_detail_name
         from pfs_insur_detail_info
         union
         select 999, '铺底险种' from dual) d,
         inte_data id,
         bs_corp bc,
         bs_pay_method pm
         where a.busi_bill_sn = b.busi_bill_sn
         and bc.corp_id = b.pay_object_id
         and bc.center_id = id.center_id
         and id.obj_code=bc.corp_id
         -- and a.center_id='430701'
         and a.pay_object = 1
         -- and b.pay_object_id='989'
         and id.bill_no = to_char(a.busi_bill_sn)
         and c.busi_reco_no = b.busi_reco_no
         and d.insr_detail_code = c.insr_detail_code
         and a.pay_method = pm.pay_method(+)
         --  and to_char(a.make_bill_tm, 'yyyymm') between '201601' and
         '201601'
         --and to_char(b.fact_pay_date, 'yyyymm') between '201601' and
         '201601'
         order by  to_char(a.make_bill_tm,'yyyy-mm-dd'))
   Rationale
      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      I/O and Cluster wait for TABLE "SJGX_YB.INTE_DATA" with object ID 89405
      consumed 100% of the database time spent on this SQL statement.
   Rationale
      Top level calls to execute the PL/SQL statement with SQL_ID
      "2h0f0svtyt4c7" are responsible for 100% of the database time spent on
      the INSERT statement with SQL_ID "c1fw0514uxxrs".
      Related Object
         SQL statement with SQL_ID 2h0f0svtyt4c7.
         DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
         broken BOOLEAN := FALSE; BEGIN
         dbms_refresh.refresh('"SJGX_YB"."V_DWJKXX"'); :mydate := next_date;
         IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

   Recommendation 4: SQL Tuning
   Estimated benefit is .16 active sessions, 12.04% of total activity.
   -------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the DELETE statement with SQL_ID
      "3bvqft1u53xqz". Additionally, investigate this statement for possible
      performance improvements. You can supplement the information given here
      with an ASH report for this SQL_ID.
      Related Object
         SQL statement with SQL_ID 3bvqft1u53xqz.
         /* MV_REFRESH (DEL) */ delete from "SJGX_YB"."GRCBXX"
   Rationale
      The SQL spent 51% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
      Look at data given below and an ASH report for further performance
      improvements.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "3bvqft1u53xqz" was executed 18 times and had
      an average elapsed time of 79 seconds.
   Rationale
      Waiting for event "log buffer space" in wait class "Configuration"
      accounted for 46% of the database time spent in processing the SQL
      statement with SQL_ID "3bvqft1u53xqz".
   Rationale
      Top level calls to execute the PL/SQL statement with SQL_ID
      "8fn8wsfvd344t" are responsible for 100% of the database time spent on
      the DELETE statement with SQL_ID "3bvqft1u53xqz".
      Related Object
         SQL statement with SQL_ID 8fn8wsfvd344t.
         DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
         broken BOOLEAN := FALSE; BEGIN
         dbms_refresh.refresh('"SJGX_YB"."GRCBXX"'); :mydate := next_date; IF
         broken THEN :b := 1; ELSE :b := 0; END IF; END;

   Recommendation 5: SQL Tuning
   Estimated benefit is .1 active sessions, 7.74% of total activity.
   -----------------------------------------------------------------
   Action
      Investigate the INSERT statement with SQL_ID "0b6acnpktxcqd" for
      possible performance improvements. You can supplement the information
      given here with an ASH report for this SQL_ID.
      Related Object
         SQL statement with SQL_ID 0b6acnpktxcqd.
         /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO
         "SJGX_YB"."GRCBXX"("姓名","身份证号","社保编号","险种子项类别","开始时
间","视同缴费月数","个人险种状
         态") SELECT "BI"."NAME","BI"."IDCARD","BI"."INSR_CODE","DI"."INSR_DETA
         IL_NAME",TO_CHAR("PI"."BEGIN_DATE",'yyyy-mm-dd'),"PI"."ALI_PAY_MONS",
         CASE "PI"."INDI_JOIN_STA" WHEN 0 THEN '无效' ELSE '有效' END  FROM
         "BS_PRES_INSUR" "PI","PFS_INSUR_DETAIL_INFO" "DI","BS_INSURED" "BI"
         WHERE "PI"."INDI_ID"="BI"."INDI_ID" AND
         "PI"."INSR_DETAIL_CODE"="DI"."INSR_DETAIL_CODE"
   Rationale
      The SQL spent only 22% of its database time on CPU, I/O and Cluster
      waits. Therefore, the SQL Tuning Advisor is not applicable in this case.
      Look at performance data for the SQL to find potential improvements.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "0b6acnpktxcqd" was executed 18 times and had
      an average elapsed time of 49 seconds.
   Rationale
      Waiting for event "log buffer space" in wait class "Configuration"
      accounted for 51% of the database time spent in processing the SQL
      statement with SQL_ID "0b6acnpktxcqd".
   Rationale
      Top level calls to execute the PL/SQL statement with SQL_ID
      "8fn8wsfvd344t" are responsible for 100% of the database time spent on
      the INSERT statement with SQL_ID "0b6acnpktxcqd".
      Related Object
         SQL statement with SQL_ID 8fn8wsfvd344t.
         DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
         broken BOOLEAN := FALSE; BEGIN
         dbms_refresh.refresh('"SJGX_YB"."GRCBXX"'); :mydate := next_date; IF
         broken THEN :b := 1; ELSE :b := 0; END IF; END;

   Recommendation 6: SQL Tuning
   Estimated benefit is .05 active sessions, 3.61% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SELECT statement with SQL_ID
      "35vy818ghp687".
      Related Object
         SQL statement with SQL_ID 35vy818ghp687.
         SELECT
         lutt.name 姓名,
         lutt.idcard 身份证,
         '城镇居民医疗保险' as 险种,
         lutt.calc_prd as 计算年月,
         lutt.curr_year 所属期间,
         lutt.pay_money as 缴费基数,
         lutt.pay_money as 缴费金额,
         lutt.urban_type_name as 缴款类型,
         lutt.policy_item_name as 款项类别,
         Case
         When Nvl(lutt.busi_asg_no, 0) = 0 Then
         '未缴'
         When lutt.busi_asg_no In (-999, -998, -997, -981, -980) Then
         '未缴'
         Else
         '已缴'
         End as 缴费标志,
         pt.pers_name as 人员类别,
         '个体' as 个体缴费标志,
         bc.corp_name 缴费单位,
         bc.corp_code  单位编码,
         lbr.reco_time 计算时间,
         to_char(lutt.fac_pay_date, 'yyyy-mm-dd') as 实际缴款时间,
         Case
         When Nvl(lutt.busi_asg_no, 0) = 0 Then
         '未注资'
         When lutt.busi_asg_no In (-999, -998, -997, -981, -980) Then
         '未注资'
         Else
         '已注资'
         End as 注资标志,
         lbr.reco_staff 计算人
         FROM lv_urban_topay_tmp lutt,
         bs_corp bc,
         bs_person_type pt,
         /* lv_busi_bill lbb,*/
         lv_busi_record lbr,
         lv_busi_assign lba
         WHERE
         bc.corp_id=lutt.corp_id
         and pt.pers_type=lutt.pers_type and pt.center_id=lutt.center_id
         and lba.busi_asg_no(+)=lutt.busi_asg_no
         and lba.busi_reco_no=lbr.busi_reco_no(+)
         ORDER BY lutt.curr_year, lutt.src_type, lutt.policy_item_code
   Rationale
      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "35vy818ghp687" was executed 1 times and had
      an average elapsed time of 427 seconds.
   Rationale
      I/O and Cluster wait for TABLE "SJGX_YB.LV_URBAN_TOPAY_TMP" with object
      ID 90256 consumed 100% of the database time spent on this SQL statement.


Finding 2: Top Segments by "User I/O" and "Cluster"
Impact is .17 active sessions, 13.43% of total activity.
--------------------------------------------------------
Individual database segments responsible for significant "User I/O" and
"Cluster" waits were found.

   Recommendation 1: Segment Tuning
   Estimated benefit is .12 active sessions, 9.1% of total activity.
   -----------------------------------------------------------------
   Action
      Investigate application logic involving I/O on TABLE "SJGX_YB.INTE_DATA"
      with object ID 89405.
      Related Object
         Database object with ID 89405.
   Action
      Look at the "Top SQL Statements" finding for SQL statements consuming
      significant I/O on this segment. For example, the INSERT statement with
      SQL_ID "c1fw0514uxxrs" is responsible for 100% of "User I/O" and
      "Cluster" waits for this segment.
   Rationale
      The I/O usage statistics for the object are: 1 full object scans, 263894
      physical reads, 414 physical writes and 0 direct reads.

   Recommendation 2: Segment Tuning
   Estimated benefit is .03 active sessions, 2.35% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate application logic involving I/O on TABLE
      "SJGX_YB.LV_URBAN_TOPAY_TMP" with object ID 90256.
      Related Object
         Database object with ID 90256.
   Action
      Look at the "Top SQL Statements" finding for SQL statements consuming
      significant I/O on this segment. For example, the SELECT statement with
      SQL_ID "35vy818ghp687" is responsible for 100% of "User I/O" and
      "Cluster" waits for this segment.
   Rationale
      The I/O usage statistics for the object are: 4 full object scans,
      1361966 physical reads, 682018 physical writes and 680863 direct reads.

   Recommendation 3: Segment Tuning
   Estimated benefit is .03 active sessions, 1.98% of total activity.
   ------------------------------------------------------------------
   Action
      Run "Segment Advisor" on TABLE "SJGX_YB.LV_INDIPAR" with object ID
      89473.
      Related Object
         Database object with ID 89473.
   Action
      Investigate application logic involving I/O on TABLE
      "SJGX_YB.LV_INDIPAR" with object ID 89473.
      Related Object
         Database object with ID 89473.
   Action
      Look at the "Top SQL Statements" finding for SQL statements consuming
      significant I/O on this segment. For example, the INSERT statement with
      SQL_ID "f64qufxuu0r5g" is responsible for 100% of "User I/O" and
      "Cluster" waits for this segment.
   Rationale
      The I/O usage statistics for the object are: 18 full object scans,
      36784620 physical reads, 0 physical writes and 0 direct reads.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "User I/O" was consuming significant database time.
      Impact is .25 active sessions, 19.52% of total activity.


Finding 3: Undersized Redo Log Buffer
Impact is .13 active sessions, 10.23% of total activity.
--------------------------------------------------------
Waits for redo log buffer space were consuming significant database time.

   Recommendation 1: Host Configuration
   Estimated benefit is .13 active sessions, 10.23% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the possibility of improving the performance of I/O to the
      online redo log files.
   Rationale
      The average size of writes to the online redo log files was 1590 K and
      the average time per write was 129 milliseconds.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Configuration" was consuming significant database time.
      Impact is .23 active sessions, 17.94% of total activity.


Finding 4: Log File Switches
Impact is .1 active sessions, 7.59% of total activity.
------------------------------------------------------
Log file switch operations were consuming significant database time while
waiting for checkpoint completion.
This problem can be caused by use of hot backup mode on tablespaces.  DML to
tablespaces in hot backup mode causes generation of additional redo.

   Recommendation 1: Database Configuration
   Estimated benefit is .1 active sessions, 7.59% of total activity.
   -----------------------------------------------------------------
   Action
      Verify whether incremental shipping was used for standby databases.

   Recommendation 2: Database Configuration
   Estimated benefit is .1 active sessions, 7.59% of total activity.
   -----------------------------------------------------------------
   Action
      Increase the size of the log files to 2048 M to hold at least 20 minutes
      of redo information.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Configuration" was consuming significant database time.
      Impact is .23 active sessions, 17.94% of total activity.


Finding 5: Buffer Busy - Hot Objects
Impact is .06 active sessions, 4.37% of total activity.
-------------------------------------------------------
Read and write contention on database blocks was consuming significant
database time.

   Recommendation 1: Schema Changes
   Estimated benefit is 0 active sessions, .35% of total activity.
   ---------------------------------------------------------------
   Action
      Consider using ORACLE's recommended solution of automatic segment space
      management in a locally managed tablespace for the tablespace "SYSTEM"
      containing the TABLE "SYS.AUD$" with object ID 407. Alternatively, you
      can move this object to a different tablespace that is locally managed
      with automatic segment space management.
      Related Object
         Database object with ID 407.
   Rationale
      There was significant read and write contention on TABLE "SYS.AUD$" with
      object ID 407.
      Related Object
         Database object with ID 407.

   Recommendation 2: Schema Changes
   Estimated benefit is 0 active sessions, .35% of total activity.
   ---------------------------------------------------------------
   Action
      Consider partitioning the TABLE "SYS.AUD$" with object ID 407 in a
      manner that will evenly distribute concurrent DML across multiple
      partitions.
      Related Object
         Database object with ID 407.

   Recommendation 3: Schema Changes
   Estimated benefit is 0 active sessions, .35% of total activity.
   ---------------------------------------------------------------
   Action
      A temporary solution may be achieved by increasing the number of free
      lists in segment "SYS.AUD$".
      Related Object
         Database object with ID 407.
   Action
      A temporary solution may be achieved by increasing the number of free
      list groups in segment "SYS.AUD$".
      Related Object
         Database object with ID 407.
   Rationale
      There was significant read and write contention on TABLE "SYS.AUD$" with
      object ID 407.
      Related Object
         Database object with ID 407.

   Symptoms That Led to the Finding:
   ---------------------------------
      Read and write contention on database blocks was consuming significant
      database time.
      Impact is .06 active sessions, 4.37% of total activity.
         Wait class "Concurrency" was consuming significant database time.
         Impact is .06 active sessions, 4.38% of total activity.


Finding 6: Commits and Rollbacks
Impact is .04 active sessions, 2.72% of total activity.
-------------------------------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.

   Recommendation 1: Host Configuration
   Estimated benefit is .04 active sessions, 2.72% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate the possibility of improving the performance of I/O to the
      online redo log files.
   Rationale
      The average size of writes to the online redo log files was 1590 K and
      the average time per write was 129 milliseconds.
   Rationale
      The total I/O throughput on redo log files was 0 K per second for reads
      and 2.9 M per second for writes.
   Rationale
      The redo log I/O throughput was divided as follows: 0% by RMAN and
      recovery, 100% by Log Writer, 0% by Archiver, 0% by Streams AQ and 0% by
      all other activity.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Commit" was consuming significant database time.
      Impact is .04 active sessions, 2.72% of total activity.


Finding 7: "Network" Wait Class
Impact is .03 active sessions, 2.24% of total activity.
-------------------------------------------------------
Wait class "Network" was consuming significant database time.

   No recommendations are available.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

          Additional Information
          ----------------------

Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
CPU was not a bottleneck for the instance.
Session connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was not consuming significant database time.

使用dbms_advisor优化物化视图

Posted on 2016年9月8日 by YongJing(敬勇)

为了快速刷新与查询重写优化物化视图
dbms_mview有多个过程可以帮助你来创建物化视图并为了快速刷新与查询重写而进行优化。 explain_mview过程可以告诉你一个物化视图是否可以快速刷新或满足一般的查询重写，而 explain_rewrite过程将会告诉你查询重写是否会执行。然而也会告诉你如何完成快速刷新或查询重写。

为了能更好的使用物化视图，dbms_advisor.tune_mview过程将会显示如何来优化你的create materialized view语句来满足其它的要求，比如物化视图日志与为了快速刷新与常见查询重重写等价关系。dbms_advisor.tune_mview过程分析与处理create materialized view语句并生成两组输出：一组是物化视图的实现与其它撤消创建物化视图的操作。两组输出结果可以通过Oracle视图来访问或由SQL Access Advisor创建一个外部脚本文件。这些外部脚本文件是可以用来执行实现物化视图的。

使用dbms_advisor.tune_mview过程，你将不再为了创建物化视图而需要详细理解物化视图，因为物化视与它所需要的组件（比如物化视图日志）将会通过这个过程来正确的创建。

dbms_advisor.tune_mview的语法与操作
下面是dbms_advisor.tune_mview的语法:

dbms_advisor.tune_mview(task_name in out varchar2, mv_create_stmt in [clob | varchar2])

dbms_advisor.tune_mview有两个输入参数:task_name与mv_create_stmt。task_name是一个用户提供的任务标识用来访问输出结果。mv_create_stmt是一个要被优化的完整的create materialized view 语句。如果输入的create materialized view语句没有包含refresh fast或enable query rewrite或者这两者的话，dbms_advisor.tune_mview将使用缺省子句refresh force与disable query rewrite 来优化语句使它能快速刷新或者能完全刷新。

dbms_advisor.tune_mview过程处理create materialized view语句的范围比较广，它可以是任意查询。查询可能是一个简单的select语句或一个有集合操作，或内联视图的复杂查询。当物化视图的定义查询包含refresh fast子句时，dbms_advisor.tune_mview过程将会分析查询并且检查看是否能满足快速刷新。如果物化视图已经满足了快速刷新的要求，那么dbms_advisor.tune_mview过程将返回消息说”这个物化视图已经是优化的了并且将不会执行优化处理”，否则，dbms_advisor.tune_mview 过程将对指定的语句启动优化操作。

dbms_advisor.tune_mview过程为了让物化视图能够满足fast refresh的要求可以通过增加额外的列比如聚合操作列或修复物化视图日志来生成正确定义的查询语句。在物化视图使用复杂查询的情况下，dbms_advisor.tune_mview过程可能会分解查询并且生成两个或多个快速刷新的物化视图或者为了满足快速刷新的要求而重新定义物化视图。dbms_advisor.tune_mview过程支持以下复杂的查询结构:
.集合操作(union,union all,minus与intersect)
.count distinct
.select distinct
.内联视图

当物化视图定义查询语句指定了enable query rewrite子句时，dbms_advisor.tune_mview过程也将会使用类似于处理refresh fast的方式来修复语句，它将重新定义物化视图，因此尽可能多的使用更先进的查询重写形式。

dbms_advisor.tune_mview过程将以可执行语句的方式来生成两组输出结果。一组输出是物化视图的实现与所请求的组件，比如物化视图日志或为了实现快速刷新和尽可能的查询重写与原语句等价的重写语句。另一组输出是用于删除物化视图和与原语句等价的重写语句。

实现过程的输出语句包括:
.create materialized view log语句:创建为了快速刷新而抽失的物化视图日志
.alter materialized view log force语句:修复任何物化视图日志为了快速刷新而缺失的过滤列，序列等等
.一个或多个create materialized view语句:在这种情况下会输出一个由原始查询语句直接重写与转换而来的语句。简单的查询转换可能是调整所请求的列。例如，为物化联接视图增加rowid列，并且为物化聚合视图增加聚合列。在分解复杂语句的情况下，由原始语句会生成多个create materialized view语句并且通过一个新的顶级物化视图来引用一个或多个子物化视图来形成一个嵌套的物化视图。这将尽可能地完成快速刷新与查询重写。
.build_safe_rewrite_equivalence语句:使用子物化视图来重写顶级物化视图。它被要求当组合出现时能够使用查询重写。

分解的结果就子物化视图没有被共享。在分解情况下，dbms_advisor.tune_mview过程将总是包含新的子物化视图并且它将在现有物化视图中将不再被引用。

撤消操作的输出语句包括:
.drop materialized view语句是为了撤消实现处理输出中所创建的物化视图。
.drop_rewrite_equivalence语句用来删除实现处理输出中与原始查询等价的语句。

注意撤消处理不会包含删除物化视图日志的操作。这是因为物化视图日志可以被多个不同的物化视图所共享，有一些还可能存储在远程的数据库中。

访问dbms_advisor.tune_mview输出结果
有两种方法可以来访问dbms_advisor.tune_mview输出结果:
.使用dbms_advisor.get_task_script函数与dbms_advisor.create_file过程来生成脚本。
.使用user_tune_mview或dba_tune_mview视图。

user_tune_mview与dba_tune_mview视图
在执行tune_mview后，输出结果会插入到SQL Access Advisor档案库表中并且可以通过Oracle视图 user_tune_mview与dba_tune_mview来进行访问。

使用dbms_advisor函数与过程来生成脚本
使用dbms_advisor.get_task_script过程来对建议生成脚本最简单的方式。下面是一个简单的例子。首先，创建一个目录用来存储脚本文件:

create directory tune_results as '/tmp/script_dir';
grant read, write on directory tune_results to public;

再次就是生成实现与撤消脚本并将它们分别存储到/tmp/script_dir/mv_create.sql 与/tmp/script_dir/mv_undo.sql中。

execute dbms_advisor.create_file(dbms_advisor.get_task_script (:task_name),'TUNE_RESULTS', 'mv_create.sql');
execute dbms_advisor.create_file(dbms_advisor.get_task_script(:task_name,'UNDO'),  'TUNE_RESULTS', 'mv_undo.sql');

下面介绍几个使用dbms_advisor.tune_mview过程来优化物化视图的例子
1.对快速刷新物化视图的查询语句进行优化
这个例子将介绍如何使用dbms_advisor.tune_mview过程来改变物化视图的查询语句来使用它满足快速刷新的要求。create materialized view语句使用变量create_mv_ddl来进行定义，它包含一个 fast refresh子句。这个查询语句包含单个查询块，并且包含了一个聚合列sum(s.amount_sold)，但是聚合列不支持快速刷新。如果对这个物化视图创建语句执行dbms_advisor.tune_mview过程，那么输出结果是建议物化视图快速刷新。

SQL> variable task_cust_mv varchar2(30);
SQL> variable create_mv_ddl varchar2(4000);
SQL> execute :task_cust_mv := 'cust_mv';

PL/SQL procedure successfully completed.

SQL> execute :create_mv_ddl := 'create materialized view cust_mv refresh fast disable  query rewrite as select s.prod_id, s.cust_id, sum(s.amount_sold) sum_amount from sales  s, customers cs where s.cust_id = cs.cust_id group by s.prod_id, s.cust_id';

PL/SQL procedure successfully completed.

SQL> execute dbms_advisor.tune_mview(:task_cust_mv, :create_mv_ddl);

PL/SQL procedure successfully completed.

使用dbms_advisor.get_task_script函数与dbms_advisor.create_file过程来生成脚本文本的方式来查看dbms_advisor.tune_mview的输出结果:

创建存储脚本文件的目录

SQL> create directory tune_results as '/backup';
grant read, write on directory tune_results to public;
Directory created.


SQL> grant read, write on directory tune_results to public;

Grant succeeded.

生成脚本文件

SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('cust_mv'),'TUNE_RESULTS', 'jy_mv_create.sql');

PL/SQL procedure successfully completed.

SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('cust_mv','UNDO'),'TUNE_RESULTS', 'jy_mv_undo_create.sql');

PL/SQL procedure successfully completed.

查看脚本文件内容:

[root@weblogic28 backup]# cat jy_mv_create.sql
Rem  SQL Access Advisor: Version 10.2.0.5.0 - Production
Rem
Rem  Username:        SH
Rem  Task:            cust_mv
Rem  Execution date:
Rem

CREATE MATERIALIZED VIEW LOG ON
    "SH"."SALES"
    WITH ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."SALES"
    ADD ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "SH"."CUSTOMERS"
    WITH ROWID, SEQUENCE("CUST_ID")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."CUSTOMERS"
    ADD ROWID, SEQUENCE("CUST_ID")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW SH.CUST_MV
    REFRESH FAST WITH ROWID
    DISABLE QUERY REWRITE
    AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM("SH"."SALES"."AMOUNT_SOLD")
       M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS,
       SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID GROUP BY SH.SALES.CUST_ID,
       SH.SALES.PROD_ID;

使用user_tune_mview或dba_tune_mview视图来查看dbms_advisor.tune_mview过程的输出结果:

SQL> set long 99999
SQL> col statement for a200
SQL> select statement from dba_tune_mview where owner='SH' and task_name='cust_mv' and  script_type='IMPLEMENTATION';

STATEMENT
---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ------------------------
CREATE MATERIALIZED VIEW LOG ON "SH"."SALES" WITH ROWID, SEQUENCE  ("PROD_ID","CUST_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."SALES" ADD ROWID, SEQUENCE  ("PROD_ID","CUST_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW LOG ON "SH"."CUSTOMERS" WITH ROWID, SEQUENCE ("CUST_ID")   INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."CUSTOMERS" ADD ROWID, SEQUENCE ("CUST_ID")   INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW SH.CUST_MV   REFRESH FAST WITH ROWID DISABLE QUERY REWRITE AS  SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM("SH"."SALES"."AMOUNT_SOLD") M1,  COUNT("SH"."SALES"."AMOUNT_S
OLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS, SH.SALES WHERE SH.SALES.CUST_ID =  SH.CUSTOMERS.CUST_ID GROUP BY SH.SALES.CUST_ID, SH.SALES.PROD_ID

dbms_advisor.tune_mview过程的输出结果包含了一个优化后的物化视图定义查询语句如下:

CREATE MATERIALIZED VIEW SH.CUST_MV
    REFRESH FAST WITH ROWID
    DISABLE QUERY REWRITE
    AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM("SH"."SALES"."AMOUNT_SOLD")
       M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS,
       SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID GROUP BY SH.SALES.CUST_ID,
       SH.SALES.PROD_ID;

dbms_advisor.tune_mview过程的撤消输出结果下:

[root@weblogic28 backup]# cat jy_mv_undo_create.sql
Rem  SQL Access Advisor: Version 10.2.0.5.0 - Production
Rem
Rem  Username:        SH
Rem  Task:            cust_mv
Rem  Execution date:
Rem

DROP MATERIALIZED VIEW SH.CUST_MV;

或者

SQL> select statement from dba_tune_mview where owner='SH' and task_name='cust_mv' and  script_type='UNDO';

STATEMENT
------------------------------------------------
DROP MATERIALIZED VIEW SH.CUST_MV

2.通过创建多个物化视图来满足查询重写的要求
这个例子介绍了一个使用了集合操作union的物化视图查询语句，它不支持查询重写，但通过分解成多个子物化视图后可以满足查询重写的要求。对于物化视图所引用的基表sales,customers与 countries，它们没有物化视图日志。

首先对创建物化视图的语句执行dbms_advisor.tune_mview过程

SQL> variable task_cust_mv varchar2(30);
SQL> variable create_mv_ddl varchar2(4000);
SQL> execute :task_cust_mv := 'cust_mv_2';

PL/SQL procedure successfully completed.

SQL> execute :create_mv_ddl := 'create materialized view cust_mv enable query rewrite as  select s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold) sum_amount from sales s,  customers cs, countries cn where s.cust_id = cs.cust_id and cs.country_id =  cn.country_id and cn.country_name in (''usa'',''canada'') group by s.prod_id, s.cust_id  union select s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold) sum_amount from  sales s, customers cs where s.cust_id = cs.cust_id and s.cust_id in (1005,1010,1012)  group by s.prod_id, s.cust_id';

PL/SQL procedure successfully completed.

SQL> execute dbms_advisor.tune_mview(:task_cust_mv, :create_mv_ddl);

PL/SQL procedure successfully completed.

查看dbms_advisor.tune_mview优化的实现输出结果

SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('cust_mv_2'),'TUNE_RESULTS', 'jy_mv_2_create.sql');

PL/SQL procedure successfully completed.

SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('cust_mv_2','UNDO'),'TUNE_RESULTS', 'jy_mv_2_undo_create.sql');

PL/SQL procedure successfully completed.

[root@localhost backup]# cat jy_mv_2_create.sql
Rem  SQL Access Advisor: Version 11.2.0.4.0 - Production
Rem
Rem  Username:        SH
Rem  Task:            cust_mv_2
Rem  Execution date:
Rem

CREATE MATERIALIZED VIEW LOG ON
    "SH"."COUNTRIES"
    WITH ROWID, SEQUENCE("COUNTRY_ID","COUNTRY_NAME")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."COUNTRIES"
    ADD ROWID, SEQUENCE("COUNTRY_ID","COUNTRY_NAME")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "SH"."SALES"
    WITH ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."SALES"
    ADD ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "SH"."CUSTOMERS"
    WITH ROWID, SEQUENCE("CUST_ID","COUNTRY_ID")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."CUSTOMERS"
    ADD ROWID, SEQUENCE("CUST_ID","COUNTRY_ID")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."SALES"
    ADD ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."CUSTOMERS"
    ADD ROWID, SEQUENCE("CUST_ID")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW SH.CUST_MV$SUB1
    REFRESH FAST WITH ROWID ON COMMIT
    ENABLE QUERY REWRITE
    AS SELECT SH.COUNTRIES.COUNTRY_NAME C1, SH.SALES.CUST_ID C2, SH.SALES.PROD_ID C3,
       SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD")
       M2, COUNT(*) M3 FROM SH.CUSTOMERS, SH.SALES, SH.COUNTRIES WHERE SH.SALES.CUST_ID
       = SH.CUSTOMERS.CUST_ID AND SH.COUNTRIES.COUNTRY_ID = SH.CUSTOMERS.COUNTRY_ID
       AND (SH.COUNTRIES.COUNTRY_NAME IN ('usa', 'canada')) GROUP BY  SH.COUNTRIES.COUNTRY_NAME,
       SH.SALES.CUST_ID, SH.SALES.PROD_ID;

CREATE MATERIALIZED VIEW SH.CUST_MV$SUB2
    REFRESH FAST WITH ROWID ON COMMIT
    ENABLE QUERY REWRITE
    AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM("SH"."SALES"."AMOUNT_SOLD")
       M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS,
       SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND (SH.SALES.CUST_ID
       IN (1012, 1010, 1005)) GROUP BY SH.SALES.CUST_ID, SH.SALES.PROD_ID;

CREATE MATERIALIZED VIEW SH.CUST_MV
    REFRESH FORCE WITH ROWID
    ENABLE QUERY REWRITE
    AS (SELECT "CUST_MV$SUB1"."C3" "PROD_ID","CUST_MV$SUB1"."C2" "CUST_ID",SUM("CUST_MV $SUB1"."M3")
       "CNT",SUM("CUST_MV$SUB1"."M1") "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV $SUB1"
       GROUP BY "CUST_MV$SUB1"."C3","CUST_MV$SUB1"."C2")UNION (SELECT "CUST_MV $SUB2"."C2"
       "PROD_ID","CUST_MV$SUB2"."C1" "CUST_ID","CUST_MV$SUB2"."M3" "CNT","CUST_MV $SUB2"."M1"
       "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB2" "CUST_MV$SUB2");

begin
  sys.dbms_advanced_rewrite.build_safe_rewrite_equivalence('SH.CUST_MV$RWEQ','select  s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold) sum_amount from sales s,  customers cs, countries cn where s.cust_id = cs.cust_id and cs.country_id =  cn.country_id and cn.country_name in (''usa'',''canada'') group by s.prod_id, s.cust_id  union select s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold) sum_amount from  sales s, customers cs where s.cust_id = cs.cust_id and s.cust_id in (1005,1010,1012)  group by s.prod_id, s.cust_id',' (SELECT "CUST_MV$SUB1"."C3" "PROD_ID","CUST_MV $SUB1"."C2" "CUST_ID",SUM("CUST_MV$SUB1"."M3") "CNT",SUM("CUST_MV$SUB1"."M1")  "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" GROUP BY "CUST_MV $SUB1"."C3","CUST_MV$SUB1"."C2")UNION (SELECT "CUST_MV$SUB2"."C2" "PROD_ID","CUST_MV $SUB2"."C1" "CUST_ID","CUST_MV$SUB2"."M3" "CNT","CUST_MV$SUB2"."M1" "SUM_AMOUNT" FROM  "SH"."CUST_MV$SUB2" "CUST_MV$SUB2")',-2066096392);
end
;
/

或者

SQL> select statement from dba_tune_mview where owner='SH' and task_name='cust_mv_2' and  script_type='IMPLEMENTATION';

STATEMENT
---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ------------------------------------------------
CREATE MATERIALIZED VIEW LOG ON "SH"."COUNTRIES" WITH ROWID, SEQUENCE  ("COUNTRY_ID","COUNTRY_NAME")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."COUNTRIES" ADD ROWID, SEQUENCE  ("COUNTRY_ID","COUNTRY_NAME")  INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW LOG ON "SH"."SALES" WITH ROWID, SEQUENCE  ("PROD_ID","CUST_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."SALES" ADD ROWID, SEQUENCE  ("PROD_ID","CUST_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW LOG ON "SH"."CUSTOMERS" WITH ROWID, SEQUENCE  ("CUST_ID","COUNTRY_ID")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."CUSTOMERS" ADD ROWID, SEQUENCE  ("CUST_ID","COUNTRY_ID")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."SALES" ADD ROWID, SEQUENCE  ("PROD_ID","CUST_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."CUSTOMERS" ADD ROWID, SEQUENCE ("CUST_ID")   INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW SH.CUST_MV$SUB1   REFRESH FAST WITH ROWID ON COMMIT ENABLE  QUERY REWRITE AS SELECT SH.COUNTRIES.COUNTRY_NAME C1, SH.SALES.CUST_ID C2,  SH.SALES.PROD_ID C3, SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT ("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS, SH.SALES, SH.COUNTRIES  WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND SH.COUNTRIES.COUNTRY_ID =  SH.CUSTOMERS.COU
NTRY_ID AND (SH.COUNTRIES.COUNTRY_NAME IN ('usa', 'canada')) GROUP BY  SH.COUNTRIES.COUNTRY_NAME, SH.SALES.CUST_ID, SH.SALES.PROD_ID

CREATE MATERIALIZED VIEW SH.CUST_MV$SUB2   REFRESH FAST WITH ROWID ON COMMIT ENABLE  QUERY REWRITE AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM ("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM  SH.CUSTOMERS, SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND  (SH.SALES.CUST_ID IN (1012, 1010, 1005)) GROUP BY SH.SALES.CUST_ID, SH.SALES.PROD_ID
CREATE MATERIALIZED VIEW SH.CUST_MV   REFRESH FORCE WITH ROWID ENABLE QUERY REWRITE AS   (SELECT "CUST_MV$SUB1"."C3" "PROD_ID","CUST_MV$SUB1"."C2" "CUST_ID",SUM("CUST_MV $SUB1"."M3") "CNT",SUM("CUST_MV$SUB1"."M1") "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1"  "CUST_MV$SUB1" GROUP BY "CUST_MV$SUB1"."C3","CUST_MV$SUB1"."C2")UNION (SELECT "CUST_MV $SUB2"."C2" "PROD_ID","CUST_MV$SUB2"."C1" "CUST_ID","CUST_MV$SUB
2"."M3" "CNT","CUST_MV$SUB2"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB2" "CUST_MV$SUB2")

DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE ('SH.CUST_MV$RWEQ','select  s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold) sum_amount from sales s,  customers cs, countries cn where s.cust_id = cs.cust_id and cs.country_id =  cn.country_id and cn.country_name in ('usa','canada') group by s.prod_id, s.cust_id  union select s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold) sum_amount f
rom sales s, customers cs where s.cust_id = cs.cust_id and s.cust_id in (1005,1010,1012)  group by s.prod_id, s.cust_id',' (SELECT "CUST_MV$SUB1"."C3" "PROD_ID","CUST_MV $SUB1"."C2" "CUST_ID",SUM("CUST_MV$SUB1"."M3") "CNT",SUM("CUST_MV$SUB1"."M1")  "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" GROUP BY "CUST_MV $SUB1"."C3","CUST_MV$SUB1"."C2")UNION (SELECT "CUST_MV$SUB2"."C2" "PROD_ID","CUST_MV
$SUB2"."C1" "CUST_ID","CUST_MV$SUB2"."M3" "CNT","CUST_MV$SUB2"."M1" "SUM_AMOUNT" FROM  "SH"."CUST_MV$SUB2" "CUST_MV$SUB2")',-2066096392)

查看dbms_advisor.tune_mview优化的撤消输出结果

[root@localhost backup]# cat jy_mv_2_undo_create.sql
Rem  SQL Access Advisor: Version 11.2.0.4.0 - Production
Rem
Rem  Username:        SH
Rem  Task:            cust_mv_2
Rem  Execution date:
Rem

DROP MATERIALIZED VIEW SH.CUST_MV$SUB1;

DROP MATERIALIZED VIEW SH.CUST_MV$SUB2;

DROP MATERIALIZED VIEW SH.CUST_MV;

begin
  sys.dbms_advanced_rewrite.drop_rewrite_equivalence('SH.CUST_MV$RWEQ');
end;
/

原始物化视图cust_mv的查询语句已经被分解成了两个子物化视图cust_mv$sub1和cust_mv$sub2。有一个额外的count(amount_sold)列增加到了cust_mv$sub1中让物化视图满足快速刷新要求。

原始物化视图cust_mv的查询语句已经变成引用两个子物化视图了，使用两个子物化视图来满足快速刷新与查询重写的需要。

为了让两个子物化视图能够满足快速刷新，需要对所引用的基表增加物化视图日志。注意，每个基表会生成两个创建物化视图日志的语句：一个是create materialized view log语句，另一个是alter materialized view log force语句。这可以确保create脚本可以执行多次。

dbms_advanced_rewrite.build_safe_rewrite_equivalence所创建的语句结合了原始物化视图查询语句来定义新的顶级物化视图查询语句。它可以确保使用新的顶级物化视图可以满足查询重写的需要。

使用优化的子物化视图来满足快速刷新的例子
这个例子介绍如何使用dbms_advisor.tune_mview来优化物化视图来满足快速刷新的需要。在这个例子中，物化视图的查询语句使用了集合操作，它被换为一个子物化视图与一个新的顶级物化视图。

这个例子中的物化视图的查询语句包含union集合操作，因此物化视图本身不能满足快速刷新。然而物化视图中的两个子查询可以合成一个单独的查询语句。

SQL> variable task_cust_mv varchar2(30);
SQL> variable create_mv_ddl varchar2(4000);
SQL> execute :task_cust_mv := 'cust_mv3';

PL/SQL procedure successfully completed.

SQL> execute :create_mv_ddl := 'create materialized view cust_mv refresh fast on demand  enable query rewrite as select s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold)  sum_amount from sales s, customers cs where s.cust_id = cs.cust_id and s.cust_id in  (2005,1020) group by s.prod_id, s.cust_id union select s.prod_id, s.cust_id, count(*)  cnt, sum(s.amount_sold) sum_amount from sales s, customers cs where s.cust_id =  cs.cust_id and s.cust_id in (1005,1010,1012) group by s.prod_id, s.cust_id';

PL/SQL procedure successfully completed.



SQL> execute dbms_advisor.tune_mview(:task_cust_mv, :create_mv_ddl);

PL/SQL procedure successfully completed.

查看dbms_advisor.tune_mview优化的实现输出结果
下面的建议使用一个优化的子物化视图（包含两个子查询）与引用子物化视图的新的顶级物化视图

SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('cust_mv3'),'TUNE_RESULTS', 'jy_mv3_create.sql');

PL/SQL procedure successfully completed.



SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('cust_mv3','UNDO'),'TUNE_RESULTS', 'jy_mv3_undo_create.sql');

PL/SQL procedure successfully completed.


[root@localhost backup]# cat jy_mv3_create.sql
Rem  SQL Access Advisor: Version 11.2.0.4.0 - Production
Rem
Rem  Username:        SH
Rem  Task:            cust_mv3
Rem  Execution date:
Rem

CREATE MATERIALIZED VIEW LOG ON
    "SH"."SALES"
    WITH ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."SALES"
    ADD ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "SH"."CUSTOMERS"
    WITH ROWID, SEQUENCE("CUST_ID")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."CUSTOMERS"
    ADD ROWID, SEQUENCE("CUST_ID")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW SH.CUST_MV$SUB1
    REFRESH FAST WITH ROWID ON COMMIT
    ENABLE QUERY REWRITE
    AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM("SH"."SALES"."AMOUNT_SOLD")
       M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS,
       SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND (SH.SALES.CUST_ID
       IN (2005, 1020, 1012, 1010, 1005)) GROUP BY SH.SALES.CUST_ID, SH.SALES.PROD_ID;

CREATE MATERIALIZED VIEW SH.CUST_MV
    REFRESH FORCE WITH ROWID
    ENABLE QUERY REWRITE
    AS (SELECT "CUST_MV$SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID","CUST_MV $SUB1"."M3"
       "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1"
       WHERE "CUST_MV$SUB1"."C1"=2005 OR "CUST_MV$SUB1"."C1"=1020)UNION (SELECT
       "CUST_MV$SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID","CUST_MV$SUB1"."M3"
       "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1"
       WHERE "CUST_MV$SUB1"."C1"=1012 OR "CUST_MV$SUB1"."C1"=1010 OR "CUST_MV $SUB1"."C1"=1005);

begin
  sys.dbms_advanced_rewrite.build_safe_rewrite_equivalence('SH.CUST_MV$RWEQ','select  s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold) sum_amount from sales s,  customers cs where s.cust_id = cs.cust_id and s.cust_id in (2005,1020) group by  s.prod_id, s.cust_id union select s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold)  sum_amount from sales s, customers cs where s.cust_id = cs.cust_id and s.cust_id in  (1005,1010,1012) group by s.prod_id, s.cust_id',' (SELECT "CUST_MV$SUB1"."C2"  "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID","CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1"  "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" WHERE "CUST_MV$SUB1"."C1"=2005 OR  "CUST_MV$SUB1"."C1"=1020)UNION (SELECT "CUST_MV$SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1"  "CUST_ID","CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV $SUB1" "CUST_MV$SUB1" WHERE "CUST_MV$SUB1"."C1"=1012 OR "CUST_MV$SUB1"."C1"=1010 OR  "CUST_MV$SUB1"."C1"=1005)',-179817297);
end
;
/

或者

SQL> select statement from dba_tune_mview where owner='SH' and task_name='cust_mv3' and  script_type='IMPLEMENTATION';

STATEMENT
---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ------------------------------------------------
CREATE MATERIALIZED VIEW LOG ON "SH"."SALES" WITH ROWID, SEQUENCE  ("PROD_ID","CUST_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."SALES" ADD ROWID, SEQUENCE  ("PROD_ID","CUST_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW LOG ON "SH"."CUSTOMERS" WITH ROWID, SEQUENCE ("CUST_ID")   INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."CUSTOMERS" ADD ROWID, SEQUENCE ("CUST_ID")   INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW SH.CUST_MV$SUB1   REFRESH FAST WITH ROWID ON COMMIT ENABLE  QUERY REWRITE AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM ("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM  SH.CUSTOMERS, SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND  (SH.SALES.CUST_ID IN (2005, 1020, 1012, 1010, 1005)) GROUP BY SH.SALES.CUST_ID, SH.SAL
ES.PROD_ID

CREATE MATERIALIZED VIEW SH.CUST_MV   REFRESH FORCE WITH ROWID ENABLE QUERY REWRITE AS   (SELECT "CUST_MV$SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID","CUST_MV$SUB1"."M3"  "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" WHERE  "CUST_MV$SUB1"."C1"=2005 OR "CUST_MV$SUB1"."C1"=1020)UNION (SELECT "CUST_MV$SUB1"."C2"  "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID","CUST_MV$SUB
1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1"  WHERE "CUST_MV$SUB1"."C1"=1012 OR "CUST_MV$SUB1"."C1"=1010 OR "CUST_MV$SUB1"."C1"=1005)

DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE ('SH.CUST_MV$RWEQ','select  s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold) sum_amount from sales s,  customers cs where s.cust_id = cs.cust_id and s.cust_id in (2005,1020) group by  s.prod_id, s.cust_id union select s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold)  sum_amount from sales s, customers cs where s.cust_id = cs.cust_id and
s.cust_id in (1005,1010,1012) group by s.prod_id, s.cust_id',' (SELECT "CUST_MV $SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID","CUST_MV$SUB1"."M3" "CNT","CUST_MV $SUB1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" WHERE "CUST_MV $SUB1"."C1"=2005 OR "CUST_MV$SUB1"."C1"=1020)UNION (SELECT "CUST_MV$SUB1"."C2"  "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID","CUST_MV$SUB1"."M3" "CNT","CUST_MV$SU
B1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" WHERE "CUST_MV $SUB1"."C1"=1012 OR "CUST_MV$SUB1"."C1"=1010 OR "CUST_MV$SUB1"."C1"=1005)',-179817297)

原始物化视图cust_mv查询被优化成一个包含谓词条件的两个子查询的子物化视图cust_mv$sub1。为了能让子物化视图满足快速刷新的需要，对所引用的基表增加物化视图日志。

查看dbms_advisor.tune_mview过程的撤消输出结果:

[root@localhost backup]# cat jy_mv3_undo_create.sql
Rem  SQL Access Advisor: Version 11.2.0.4.0 - Production
Rem
Rem  Username:        SH
Rem  Task:            cust_mv3
Rem  Execution date:
Rem

DROP MATERIALIZED VIEW SH.CUST_MV$SUB1;

DROP MATERIALIZED VIEW SH.CUST_MV;

begin
  sys.dbms_advanced_rewrite.drop_rewrite_equivalence('SH.CUST_MV$RWEQ');
end;
/

使用dbms_advisor来执行sql access advisor

Posted on 2016年9月6日 by YongJing(敬勇)

使用dbms_advisor来执行sql access advisor主要有以下几个步骤:
1.创建任务
2.定义工作量
3.生成建议
4.查看与实现建议

步骤1 创建任务
在任务建议被生成之前，必须创建一个任务。任务很重要因为它是与建议进程相关的所有信息的居住地，包括建议处理的结果。如果使用dbms_advisor.quick_tune过程，那么会自动创建任务。在所有其它的情况下，必须手动执行dbms_advisor.create_task过程来创建任务。通过使用dbms_adviosr.set_task_parameter过程来定义参数可以控制任务的执行。

步骤2 定义工作量
工作量是SQL Access Advisor的主要输入，并且它由一个或多个SQL语句组成，并且加上用来完全描述每个SQL语句的各种统计信息与属性。如果工作量包含了一个目标业务系统的所有SQL语句，那么这个工作量可以认为是一个完全工作量，如果工作量只包含了一部分SQL语句，那么就认为是部分工作量。完全与部分工作量之间的差别是，对于完全工作量，SQL Access Advisor如果发现有些现有的物化视图与索引没有被有效的使用，可能会建议删除特定的现有物化视图与索引。

通常来说，SQL Access Adviosr使用工作量作为所有分析活云贵的基础。虽然工作量可能包含各种各样的SQL语句，它将根据特定的统计信息，业务重要性或统计信息与业务重要性的组合来仔细排列条目。这种排列是关键，因为它能让SQL Access Advisor使用更少的业务影响来处理最重要的SQL语句

对于数据集合可以认为是一个有效的工作量，SQL Access Advisor可能需要存在特定的属性。如果丢失某些条目虽然可以执行分析，那么建议的质量将会大幅度的降低。例如，SQL Access Advisor请求一个包含SQL查询与谁执行这个查询的工作量。所有其它属性都是可选项，然而，如果工作量还包含了I/O与CPU信息，那么SQL Access Advisor可能会更好的对语句评估当前的效率。工作量作为一个单独的对象被存储，它是由dbms_advisor.create_sqlwkld过程所创建，并且可以很容易的在多个指导任务之间共享内存。因为工作量是独立的，它必须使用dbms_advisor.add_sqlwkld_ref过程来链接到一个指导任务。一旦这种链接被建立，直到所有指导任务删除了对工作量的依赖性之前，这种链接是不能被删除的。当一个父指导任务被删除或者当工作量引用由用户执行 dbms_advisor.delete_sqlwkld_ref过程手动从指导任务中删除后，工作量引用也将被册子。

也可以在不使用工作量的情况下使用SQL Access Advisor，然而，为了最佳的结果，工作量必须以用户提供的表形式被提供，SQL Tuning Set或从SQL Cache中导入。如果没有提供工作量，SQL Access Advisor可以生成并使用基于你用户方案所定义规模的假想工作量。

一旦工作量被加载到档案库或在生成建议时加载，可以对工作量应用过滤来限制分析什么。这提供了一种基于不同工作量生成不同建议的能力。

建议处理与工作量的定制是由SQL Access Advisor参数来控制的。参数在任务或工作量对象的生命周期内仍然是生效的。当使用dbms_advisor.set_task_parameter过程设置参数后，直到重新设置之前参数值是不会发生改变的。

步骤3 生成建议
一旦任务创建并且工作量被链接到任务，且还设置了合适的参数，可以使用 dbms_advisor_execute_task过程来生成建议。这些建议会存储在SQL Access Advisor档案库中。

建议处理机制会生成一些建议并且每个建议将由一个或多个操作组成。例如，创建物化视图，然后为了收集统计信息而对其进行分析。

任务建议可能是一个复杂解决方案的简单建议，它要求实现一组数据库对象，比如索引，物化视图与物化视图日志。当一个指导任务被执行时，它将仔细分析收集的数据与用户调整的任务参数。SQL Access Advisor将试图基于内置的机制来格式化解决方案。解决方案然后会被精致的以结构化建议的形式被存储可以由用户查看与实现。

步骤4 查看与实现建议
查看建议有两种方式:使用目录视图或使用dbms_advisor.get_task_script过程来生成脚本。不是所有的建议你都需要接受，并且可以在建议脚本中标记你要接受的建议。最后的步骤就是实现建议然后验证查询性能是否有所提高。

SQL Access Advisor档案库
SQL Access Advisor所需要与所生成的所有信息都存储在SQL Access Advisor档案库中，它是数据库数据字典的一部分。使用档案库有以下优点:
.为SQL Access Advisor收集完整的工作量
.支持历史数据
.由服务器进行管理

使用SQL Access Advisor需要的系统权限
需要有advisor权限来管理或使用SQL Access Advisor。当处理一个工作量时，SQL Access Advisor 为了试图验证每个语句需要识别引用的表与列。通过处理每个语句就像原始用户执行语句一样来完成验证。如果用户对于特定的表没有select权限，SQL Access Advisor将跳过语句所引用的表。这可能造成许多语句从分析操作变成了执行操作。如果SQL Access Advisor在一个工作量中执行所有语句，工作量将会失效并且SQL Access Advisor将会返回如下信息:
QSM-00774, there are no SQL statements to process for task TASK_NAME

为了避免丢失关键的工作量查询，当前数据库用户必须有对被分析的物化视图所引用的目标表有 select权限。对于这些表，select权限不能通过角色被获得。

设置任务与模板
1.创建任务
任务是你所定义需要分析什么以及分析结果将存储在什么地方。用户可以创建任意数量的任务，每个有特定的设置。都是基于相同指导任务模型并且共享相同档案库的。使用dbms_advisor.create_task 过程创建任务的语法如下:

DBMS_ADVISOR.CREATE_TASK (
advisor_name IN VARCHAR2,
task_id OUT NUMBER,
task_name IN OUT VARCHAR2,
task_desc IN VARCHAR2 := NULL,
template IN VARCHAR2 := NULL,
is_template IN VARCHAR2 := 'FALSE',
how_created IN VARCHAR2 := NULL);



DBMS_ADVISOR.CREATE_TASK (
advisor_name IN VARCHAR2,
task_name IN VARCHAR2,
task_desc IN VARCHAR2 := NULL,
template IN VARCHAR2 := NULL,
is_template IN VARCHAR2 := 'FALSE',
how_created IN VARCHAR2 := NULL);

下面是创建任务的一示例:

SQL> variable task_id number;
SQL> variable task_name varchar2(255);
SQL> execute :task_name:='JYTASK';

PL/SQL procedure successfully completed
task_name
---------
JYTASK
SQL> execute dbms_advisor.create_task('SQL Access Advisor',:task_id,:task_name);

PL/SQL procedure successfully completed
task_id
---------
45354
task_name
---------
JYTASK

使用模板
当对一个任务或工作量识别一个空闲配置后，这个配置可以被作为模板进行保存。将来的任务可以基于这个模板进行创建。
通过设置模板，当执行调整分析时可以节省时间。还能对业务操作制定一种合适的调整分析。

为了使用模板创建任务，当创建新任务时需要指定要使用的模板。这时，SQL Access Advisor将从模板中复制数据与参数到新创建的任务中。当创建任务时或使用dbms_advisor.update_task_attribute 过程来设置模板属性时可以将现有的任务设置为模板。

为了使用任务作为模板，可以告诉SQL Access Advisor当创建新任务时指定要使用的任务。在这时， SQL Access Advisor将复制任务模板的数据与参数设置到新创建的任务。可以通过设置模板属性将现有的任务设置为模板。

工作量对象也可以用来作为模板来创建新的工作量对象。

创建模板
1.创建一个名叫my_template的模板

SQL> variable template_id number;
SQL> variable template_name varchar2(255);
SQL> execute :template_name := 'my_template';

PL/SQL procedure successfully completed
template_name
---------
my_template
SQL> execute dbms_advisor.create_task('SQL Access Advisor',:template_id,:template_name,  is_template => 'true');

PL/SQL procedure successfully completed
template_id
---------
45357
template_name
---------
my_template

2.设置模板参数

SQL> -- set naming conventions for recommended indexes/mvs
SQL> execute dbms_advisor.set_task_parameter(:template_name, 'INDEX_NAME_TEMPLATE',  'SH_IDX$$_');

PL/SQL procedure successfully completed
template_name
---------
my_template
SQL> execute dbms_advisor.set_task_parameter(:template_name, 'MVIEW_NAME_TEMPLATE',  'SH_MV$$_');

PL/SQL procedure successfully completed
template_name
---------
my_template
SQL> -- set default tablespace for recommended indexes/mvs
SQL> execute dbms_advisor.set_task_parameter(:template_name, 'DEF_INDEX_TABLESPACE',  'USERS');

PL/SQL procedure successfully completed
template_name
---------
my_template
SQL> execute dbms_advisor.set_task_parameter(:template_name, 'DEF_MVIEW_TABLESPACE',  'USERS');

PL/SQL procedure successfully completed
template_name
---------
my_template

3.使用模板来创建任务

SQL> variable task_id number;
SQL> variable task_name varchar2(255);
SQL> execute :task_name := 'mytask';

PL/SQL procedure successfully completed
task_name
---------
mytask
SQL> execute dbms_advisor.create_task('SQL Access Advisor', :task_id,:task_name,  template=>'my_template');

PL/SQL procedure successfully completed
task_id
---------
45376
task_name
---------
mytask

下面的例子使用预先定义的模板SQLACCESS_WAREHOUSE来创建任务

execute dbms_advisor.create_task('SQL Access Advisor', :task_id, :task_name,  template=>'SQLACCESS_WAREHOUSE');

管理工作量
管理工作量主要从以下方面进行:
.工作量对象
.使用工作量
.链接任务与工作量
.定义工作量内容
.向工作量增加SQL语句
.从工作量中删除SQL语句
.改变工作量中的SQL语句
.维护工作量
.删除工作量

工作量对象
因为工作量作为单独的工作量对象被存储，它可以很容易的在多个指导任务之间被共享。一旦一个工作量对象被一个指导任务所引用，那么直到所有指导任务删除它们所依赖的数据之前，工作量是不能被删除或修改的。当父指导任务被删除或者当工作量引用被手动从指导任务中被删除时，工作量引用将会被删除。

当工作量可以使用时，SQL Access Advisor会执行的最好。SQL Access Workload档案库能够存储多个工作量，因此真实世界数据仓库或事务处理环境中的不同用户可以在很长一段时间内查看并且可以踊跃数据库的重启。

在工作量的真实SQL语句被定义之前，工作量必须使用dbms_advisor.create_sqlwkld过程来进行创建
然后，使用合适的import_sqlwkld过程来加载工作量。一个特定的工作量可以通过调用 dbms_advisor.delete_sqlwkld过程来进行删除。为了删除当前用户的所有工作量，可以在调用 dbms_advisor.delete_sqlwkld过程时传递一个常量advisor_all或%。

使用工作量
dbms_advisor.create_sqlwkld过程用来创建工作量并且它必须在执行任何其它工作量操作之前存在，比如导入或更新SQL语句。通过工作量名来识别工作量，因此应该定义一个唯一名称来标识操作。

语法如下:

dbms_advisor.create_sqlwkld (
workload_name in out varchar2,
description in varchar2 := null,
template in varchar2 := null,
is_template in varchar2 := 'false');

创建工作量

SQL> variable workload_name varchar2(255);
SQL> execute :workload_name := 'myworkload';

PL/SQL procedure successfully completed
workload_name
---------
myworkload
SQL> execute dbms_advisor.create_sqlwkld(:workload_name,'this is my first workload');

PL/SQL procedure successfully completed
workload_name
---------
myworkload

使用模板来创建工作量
1.创建变量

SQL> variable template_id number;
SQL> variable template_name varchar2(255);

2.创建名叫my_wk_template的模板

SQL> execute :template_name := 'my_wk_template';

PL/SQL procedure successfully completed
template_name
---------
my_wk_template
SQL> execute dbms_advisor.create_sqlwkld(:template_name, is_template=>'true');

PL/SQL procedure successfully completed
template_name
---------
my_wk_template

3.设置模板参数。下面设置过滤只有insur_changde方案中的表被优化:

SQL> -- set USERNAME_LIST filter to insur_changde
SQL> execute dbms_advisor.set_sqlwkld_parameter(:template_name, 'USERNAME_LIST',  'insur_changde');

PL/SQL procedure successfully completed
template_name
---------
my_wk_template

4.使用模板来创建工作量

SQL> variable workload_name varchar2(255);
SQL> execute :workload_name := 'myworkload';

PL/SQL procedure successfully completed
workload_name
---------
myworkload
SQL> execute dbms_advisor.create_sqlwkld (:workload_name, 'this is my first workload',  'my_wk_template');

PL/SQL procedure successfully completed
workload_name
---------
myworkload

将任务与工作量进行链接
在建议进程开始之前，任务必须被链接到一个工作量。可以通过调用 dbms_advisor.add_sqklwkld_ref过程，通过使用它们的名字来将任务与工作量进行链接。这个过程会在指导任务与工作量之间创建一个链接。一旦在指导任务与工作量之间创建了链接，工作量将会受保护从而避免被删除。语法如下:

dbms_advisor.add_sqlwkld_ref (task_name in varchar2,workload_name in varchar2);

下面将任务mytask与工作量myworkload进行链接.

SQL> execute dbms_advisor.add_sqlwkld_ref('mytask', 'myworkload');

PL/SQL procedure successfully completed

定义工作量的内容
一旦工作量被创建，它必须要加载一些信息。理想情况下，一个工作量将由SQL语句（除非它是一个假想的工作量）组成，它们是在数据库中正在执行的SQL语句。SQL Access Advisor可以从以下来源获得工作量:
.SQL调优集
.加载用户定义的工作量
.加载SQL缓存工作量
.使用假想工作量
.使用汇总的9i工作量

SQL调优集
SQL调优集是工作量档案中的一种工作量。可以使用SQL调整集作为SQL Access Advisor的工作量通过 dbms_advisor.import_workload_sts过程进行导入。下面是使用语法:

dbms_advisor.import_sqlwkld_sts (workload_name in varchar2,
sts_owner in varchar2,
sts_name in varchar2,
import_mode in varchar2 := 'new',
priority in number := 2,
saved_rows out number,
failed_rows out number);


dbms_advisor.import_sqlwkld_sts (workload_name in varchar2,
sts_name in varchar2,
import_mode in varchar2 := 'new',
priority in number := 2,
saved_rows out number,
failed_rows out number);

在工作量被收集并且过滤语句后，SQL Access Advisor使用工作量中的DML语句来计算使用统计数据，下面的例子使用SQL调优集创建一个名叫my_sts_workload的工作量

SQL> exec dbms_sqltune.create_sqlset(sqlset_name => 'MY_STS_WORKLOAD',description =>  'SQL Access Advisor Test',sqlset_owner => 'INSUR_CHANGDE');

PL/SQL procedure successfully completed

SQL>
SQL> declare
  2    cur dbms_sqltune.sqlset_cursor;
  3  begin
  4    open cur for
  5      select value(p)
  6        from table(dbms_sqltune.select_cursor_cache ('parsing_schema_name=''INSUR_CHANGDE'' and force_matching_signature<>0 and  buffer_gets>1000 and executions>100 and command_type<>2',
  7                                                    null,
  8                                                    null,
  9                                                    null,
 10                                                    null,
 11                                                    1,
 12                                                    null,
 13                                                    'all')) p;
 14    dbms_sqltune.load_sqlset(sqlset_name     => 'MY_STS_WORKLOAD',
 15                             populate_cursor => cur);
 16  end;
 17  /

PL/SQL procedure successfully completed



SQL> variable sqlsetname varchar2(30);
SQL> variable workload_name varchar2(30);
SQL> variable saved_stmts number;
SQL> variable failed_stmts number;
SQL> execute :sqlsetname := 'my_sts_workload';

PL/SQL procedure successfully completed
sqlsetname
---------
my_sts_workload
SQL> execute :workload_name := 'my_workload';

PL/SQL procedure successfully completed
workload_name
---------
my_workload
SQL> execute dbms_advisor.create_sqlwkld (:workload_name);

PL/SQL procedure successfully completed
workload_name
---------
my_workload

SQL> execute dbms_advisor.import_sqlwkld_sts (:workload_name ,:sqlsetname, 'NEW',  1,:saved_stmts, :failed_stmts);

PL/SQL procedure successfully completed
workload_name
---------
my_workload
sqlsetname
---------
MY_STS_WORKLOAD
saved_stmts
---------
1219
failed_stmts
---------
13

加载用户定义工作量
为了加载用户定义工伤脑筋量，使用dbms_advisor.import_sqlwkld_user过程。这个过程从用户结构表或视图中收集应用程序工作量并将其保存在指导档案库中。owner_name与table_name两个参数识别表是从那种类型的工作量中获得的。对于工作量存储在那个方案，表名，或有多少个用户定义的表存在都没有限制。唯一的要求就是用户表的结构必须与USER_WORKLOAD相关，并且用户要对工作量表或视图有select访问权限。语法如下:

dbms_advisor.import_sqlwkld_user (
workload_name in varchar2,
import_mode in varchar2 := 'NEW',
owner_name in varchar2,
table_name in varchar2,
saved_rows out number,
failed_rows out number);

下面的例子加载之前创建的工作量MYWORKLOAD，使用用户表SH.USER_WORKLOAD。假设表 USER_WORKLOAD已经加载了SQL语句，并且它的结构与USER_WORKLOAD Table Format相符

variable saved_stmts number;
variable failed_stmts number;
execute dbms_advisor.import_sqlwkld_user(
'MYWORKLOAD', 'NEW', 'SH', 'USER_WORKLOAD', :saved_stmts, :failed_stmts);

加载SQL Cache工作量
可以使用dbms_advisor.import_sqlwkld_sqlcache过程来获得SQL Cache工作量。在调用这个过程的时候，SQL Cache中的当前内容将会被分析并且加载到工作量中。 dbms_advisor.import_sqlwkld_sqlcache过程从SQL Cache中加载SQL工作量，语法如下:

dbms_advisor.import_sqlwkld_sqlcache (
workload_name in varchar2,
import_mode in varchar2,
priority in number := 2,
saved_rows out number,
failed_rows out number);

下面的例子从SQL Cache中加载之前创建的工作量MYWORKLOAD。加载工作量语句的优先级为2:

variable saved_stmts number;
variable failed_stmts number;
execute dbms_advisor.import_sqlwkld_sqlcache('MYWORKLOAD', 'APPEND', 2, :saved_stmts,  :failed_stmts);

SQL Access Advisor可以从SQL Cache中检索工作量信息。如果收集的数据是从实例参数 cursor_sharing设置为similar或force的服务器中所检索到的，那么使用文本值的查询将会被转换为包含系统生成变量的语句。如果使用SQL Access Advisor来建议物化视图，那么服务器应该将参数 cursor_sharing设置为exact，因此有where子句的物化视图会被建议。

使用假想工作量
在许多情况下，应用程序工作量是不存在的。在这种情况下，SQL Access Advisor可以检查当前逻辑方案设计与基于表之间所定义的关系的格式化建议。这种类型的工作量也称为假想工作量。SQL Access Advisor可以产生一组初始化建议，并且将会成为优化应用程序的坚实基础。

使用假想工作量的优点:
.只需要方案与表关系
.对于假定场景建模是有效的

使用假想工作量的缺点:
.只有定义了维度才能工作
.在建议访问结构方面没有提供关于影响DML操作的任何信息
.不一定是完整的

为了成功导入一个假想工作量，目标方案必须包含维度信息。可以使用 dbms_advisor.import_sqlwkld_schema过程，语法如下:

dbms_advisor.import_sqlwkld_schema (
workload_name in varchar2,
import_mode in varchar2 := 'NEW',
priority in number := 2,
saved_rows out number,
failed_rows out number);

下面的例子创建一个名叫SCHEMA_WKLD的假想工作量，将valid_table_list设置为sh,并调用 dbms_advisor.import_sqlwkld_schema过程来生成假想工作量。

variable workload_name varchar2(255);
variable saved_stmts number;
variable failed_stmts number;
execute :workload_name := 'SCHEMA_WKLD';
execute dbms_advisor.create_sqlwkld(:workload_name);
execute dbms_advisor.set_sqlwkld_parameter(:workload_name,VALID_TABLE_LIST, 'SH');
execute dbms_advisor.import_sqlwkld_schema(:workload_name, 'NEW', 2, :saved_stmts,  :failed_stmts);

当使用dbms_advisor.import_sqlwkld_schema过程时，valid_table_list参数不能包含通配符比如 SCO%或SCOTT.EMP%。只支持唯一的通配符格式SCOTT.%，它将指定用户方案中的所有表。

使用9i汇总指导工作量
使用9i汇总工作量可以创建工作量。这些工作量可以被SQL Access Advisor使用，通过使用 dbms_advisor.import_sqlwld_sumadv过程来进行导入。为了使用这个过程，必须知道Oracle 9i的工作量ID。

这个过程从汇总指导工作量中收集SQL工作量。这个过程的目标是为了帮助Oracle 9i汇总指导用户迁移到SQL Access Advisor。语法如下:

DBMS_ADVISOR.IMPORT_SQLWKLD_SUMADV (
workload_name IN VARCHAR2,
import_mode IN VARCHAR2 := 'NEW',
priority IN NUMBER := 2,
sumadv_id IN NUMBER,
saved_rows OUT NUMBER,
failed_rows OUT NUMBER);

下面的例子从Oracle 9i汇总指导工作量来创建一个SQL工作量。oracle 9i的工作量workload_id为 777
1.创建一些变量

variable workload_name varchar2(255);
variable saved_stmts number;
variable failed_stmts number;

2.创建工作量WKLD_9I

execute :workload_name := 'WKLD_9I';
execute dbms_advisor.create_sqlwkld(:workload_name);

3.从Oracle 9i汇总指导工作量导入SQL工作量

execute dbms_advisor.import_sqlwkld_sumadv (:workload_name, 'NEW', 2, 777, :saved_stmts,  :failed_stmts);

SQL Access Advisor工作量参数
一个SQL工作量可以通过dbms_advisor.set_sqlwkld_parameter过程设置一个或多个参数来在加载时进行过滤操作。

下面的例子设置了SQL工作量参数。将SQL_LIMIT设置为3,ORDER_LIST设置为OPTIMIZER_COST。当导入工作量时，这意味着，语句将通过OPTIMIZER_COST来进行排序并且前三个语句将会被保留。

-- Order statements by OPTIMIZER_COST
execute dbms_advisor.set_sqlwkld_parameter ('MYWORKLOAD', 'ORDER_LIST',  'OPTIMIZER_COST');
-- Max number of statements 3
execute dbms_advisor.set_sqlwkld_parameter('MYWORKLOAD', 'SQL_LIMIT', 3);

向工作量增加SQL语句
一种替导入工作量的方法是手动指定SQL语句并且使用dbms_advisor.add_sqlwkld_statement过程将它们加入工作量。这个过程允许向指定的工作量增加SQL语句。语法如下:

dbms_advisor.add_sqlwkld_statement (
workload_name in varchar2,
module in varchar2,
action in varchar2,
cpu_time in number := 0,
elapsed_time in number := 0,
disk_reads in number := 0,
buffer_gets in number := 0,
rows_processed in number := 0,
optimizer_cost in number := 0,
executions in number := 1,
priority in number := 2,
last_execution_date in date := 'SYSDATE',
stat_period in number := 0,
username in varchar2,
sql_text in clob);

下面的例子向MYWORKLOAD工作量增加一个单独的SQL语句:

variable sql_text varchar2(400);
execute :sql_text := 'select avg(amount_sold) from sales';
execute dbms_advisor.add_sqlwkld_statement ('MYWORKLOAD', 'MONTHLY', 'ROLLUP',  priority=>1, executions=>10,username => 'SH', sql_text => :sql_text);

从工作量中删除SQL语句
使用dbms_advisor.delete_sqlwkld_statement过程可以从指定的工作量中删除SQL语句。在删除SQL 语句时需要指定sql_id。

dbms_advisor.delete_sqlwkld_statement (workload_name in varchar2,sql_id in number);

下面的例子将从MYWORKLOAD工作量中从邮sql_id为10的SQL语句:

execute dbms_advisor.delete_sqlwkld_statement('MYWORKLOAD', 10);

如果工作量当前被一个活动任务所引用，那么工作量是不能被修改或删除的。如果工作量不是处于初始状态，那么就可以认为工作量处于活动状态。dbms_advisor.reset_task过程可以将工作量设置为初始状态。

改变工作量中的SQL语句
可以通过dbms_advisor.update_sqlwkld_statement过程来修改工作量中的SQL语句。这个过程将会更新指定工作量中的现有SQL语句。通过指定sql_id来更新SQL语句，语法如下:

dbms_advisor.update_sqlwkld_statement (
workload_name in varchar2,
sql_id in number,
application in varchar2 := null,
action in varchar2 := null,
priority in number := null,
username in varchar2 := null);


dbms_advisor.update_sqlwkld_statement (
workload_name in varchar2,
search in varchar2,
updated out number,
application in varchar2 := null,
action in varchar2 := null,
priority in number := null,
username in varchar2 := null);

下面的例子将sql_id为10的SQL语句的优先级修改为3

维护工作量
有以下几种操作可以用来维护工作量:
.设置工作量属性
.重设工作量
.删除工作量与任务之间的链接

设置工作量属性
dbms_advisor.update_sqlwkld_attributes过程可以用来修改工作量对象或模板的各种属性。有些这样的属性是用来描述的，比如描述它是否是一个模板或是只读。语法如下:

dbms_advisor.update_sqlwkld_attributes (
workload_name in varchar2,
new_name in varchar2 := null,
description in varchar2 := null,
read_only in varchar2 := null,
is_template in varchar2 := null,
how_created in varchar2 := null);

下面的例子将MYWORKLOAD工作量修改为只读:

execute dbms_advisor.update_sqlwkld_attributes('myworkload', read_only=> 'true');

重设工作量
dbms_advisor.reset_sqlwkld过程可以将一个工作量设置为初始状态。当工作量数据保持原样时这将会删除所有的日记和日志信息，并重新计算易波动的统计信息。这个过程应该在任何工作量被调整后，比如增加或删除SQL语句后执行这个过程。下面的例子将重新设置MYWORKLOAD工作量.

execute dbms_advisor.reset_sqlwkld('myworkload');

删除工作量与任务之间的链接
在任务或工作量被删除之前，如果它被分别链接到一个工作量或任务，那么在任务与工作量之间的链接必须使用delete_sqlwkld_ref过程来进行删除。下面的例子将会把任务MYTASK与SQL工作量 MYWORKLOAD之间的链接删除。

execute dbms_advisor.delete_sqlwkld_ref('mytask', 'myworkload');

删除工作量
当工作量不再需要时，可以使用dbms_advisor.delete_sqlwkld过程来将其删除。可以删除所有工作量或者一个特定的集合，但如果工作量仍然被链接到一个任务，那么这个工作量将不会被删除。

下面的例子用来删除一个特定的工作量。

dbms_advisor.delete_sqlwkld (workload_name in varchar2);
execute dbms_advisor.delete_sqlwkld('myworkload');

处理建议
处理建议包含以下方面的内容:
.建议选项
.评估模式
.生成建议
.查看建议
.SQL工作量日记
.停止建议处理
.标记建议
.修改建议
.生成SQL脚本
.何时将不再需要建议

建议选项
在建议生成之前，任务的参数首先必须使用dbms_advisor.set_task_parameter过程来进行定义。如果没有定义参数，那么将会使用缺省值。使用dbms_advisor.set_task_parameter过程设置参数的语法如下:

dbms_advisor.set_task_parameter (
task_name in varchar2,
parameter in varchar2,
value in [varchar2 | number]);

在下面的例子中，将任务MYTASK的存储大小修改为100MB。这将指示对于建议将有额外的100MB空间。如果设置为0,说明没有额外的空间被分配。如果设置为一个负值，则说明指导必须试图削减当前空间利用的大小，其大小是参数所指定。

execute dbms_advisor.set_task_parameter('mytask','storage_change', 100000000);

在下面的例子中，将使用valid_table_list参数来过滤掉所有不包括sh.sales和sh.customers表的查询。

execute dbms_advisor.set_task_parameter('MYTASK', 'VALID_TABLE_LIST', 'SH.SALES,  SH.CUSTOMERS');

评估模式
当执行一个任务时，SQL Access Advisor有两种操作模式:问题解决与评估。缺省情况下，SQL Access Advisor将试图通过寻找索引结构，物化视图与物化视图日志的改进来解决访问方法的问题。当只进行评估操作时，SQL Access Advisor将只会给出提供什么访问结构让工作量使用的见意。例如，一个问题可能可以通过创建一个新索引，增加一个新列到物化视图日志，等等的方法来解决。而当进行评估操作时，只会生成比如保留索引，保留物化视图等建议。当进行评估操作时不会考虑对访问方法的调整。它是一种严格的方法用来查看现有访问方法结构和它们如何被提供给工作量所使用。

生成建议
通过执行dbms_advisor_execute_task过程并指定任务名来生成建议。在这个过程执行完成后，可以检查dba_advisor_log表来检查真实的执行状态与生成的建议数量与操作数。可以使用任务名来查询
{dba,user}_advisor_recommendations视图来查看建议，查询{dba,user}_advisor_actions视图来查看这些建议的操作。

dbms_advisor.execute_task
dbms_advisor.execute_task过程用来对特定任务执行SQL Access Advisor分析或评估。执行任务是一个同步操作，所以直到操作完成之前不会将控制返回给用户。任务在执行时或执行完成后，可以检查dba_advisor_log表来查看真实的执行状态。

执行dbms_advisor.execute_task过程来生成建议，建议是由一个或多个操作组成，比如创建物化视图日志或物化视图。语法如下:

dbms_advisor.execute_task (task_name in varchar2);

下面的例子执行任务MYTASK:

execute dbms_advisor.execute_task('MYTASK');

查看建议
由SQL Access Advisor生成的每个建议可以使用目录视图来进行查看，比如{dba,user} _advisor_recommendations视图。然而，最简单的方法是使用dbms_advisor.get_task_script过程或使用EM中的SQL Access Advisor，它是一个图形工具来显示建议并且提供了一个超链接来快速查看那个语句将会受益于这个建议。由SQL Access Advisor生成的每个建议被链接到受益于它的SQL语句。

下面显了由SQL Access Advisor由生成的建议(rec_id)，以及它们的排名与总收益。排名是一种测量标准判断建议对于查询的重要性。收益是所有查询使用建议后它的执行成本（优化器成本）的提高总量。

variable workload_name varchar2(255);
variable task_name varchar2(255);
execute :task_name := 'MYTASK';
execute :workload_name := 'MYWORKLOAD';
select rec_id, rank, benefit
from user_advisor_recommendations where task_name = :task_name;
    REC_ID       RANK    BENEFIT
---------- ---------- ----------
         1          2       2754
         2          3       1222
         3          1       5499
         4          4        594

为了识别那个查询受益于那个建议，可以使用dba_*与user_advisor_sqla_wk_stmts。precost与 postcost是分别对不使用与使用建议的发生改变的访问结构来评估优化器成本的项目(在explain plan中所示)。为了查看每个查询，执行以下语句:

select sql_id, rec_id, precost, postcost,
(precost-postcost)*100/precost as percent_benefit
from user_advisor_sqla_wk_stmts
where task_name = :task_name and workload_name = :workload_name;
SQL_ID         REC_ID    PRECOST   POSTCOST PERCENT_BENEFIT
---------- ---------- ---------- ---------- ---------------
121                 1       3003        249      91.7082917
122                 2       1404        182       87.037037
123                 3       5503          4      99.9273124
124                 4        730        136       81.369863

每一个建议都是由一个或多个操作组成，这些操作必须都执行才能受益于建议。SQL Access Advisor 提供了以下类型的操作:

.create|drop|retain materialized view
.create|alter|retain materialized view log
.create|drop|retain index
.gather stats

create操作关联新的访问结构，retain建议指示现有的访问结构必须保留，drop建议只有在 workload_scope参数设置为full时才会生成。gather stats操作将生成调用dbms_stats过程的语句用来对新生成的访问结构收集统计信息。需要注意的是可能多个建议引用了相同操作，然后在生成的建议脚本中，对于每个操作只会看到一次。

在下面的例子中，可以看到对于这组建议有许多不同的操作。

select 'action count', count(distinct action_id) cnt
from user_advisor_actions where task_name = :task_name;

'ACTIONCOUNT        CNT
------------ ----------
Action Count         20
-- see the actions for each recommendations
select rec_id, action_id, substr(command,1,30) as command
from user_advisor_actions where task_name = :task_name
order by rec_id, action_id;

REC_ID      ACTION_ID COMMAND
---------- ---------- ------------------------------
         1          5 CREATE MATERIALIZED VIEW LOG
         1          6 ALTER MATERIALIZED VIEW LOG
         1          7 CREATE MATERIALIZED VIEW LOG
         1          8 ALTER MATERIALIZED VIEW LOG
         1          9 CREATE MATERIALIZED VIEW LOG
         1         10 ALTER MATERIALIZED VIEW LOG
         1         11 CREATE MATERIALIZED VIEW
         1         12 GATHER TABLE STATISTICS
         1         19 CREATE INDEX
         1         20 GATHER INDEX STATISTICS
         2          5 CREATE MATERIALIZED VIEW LOG
         2          6 ALTER MATERIALIZED VIEW LOG
         2          9 CREATE MATERIALIZED VIEW LOG
         ...

每个操作有多个属性，它是关于访问结构的属性。每个访问结构的名称与表空间被分别存储在 dba_advisor_actions视图中的attr1与attr2列中。每个新访问结构所占用的空间大小存储在 dba_advisor_actions视图中的num_attr1列中。每个操作的所有其它属性是不同的。

下面的PL/SQL过程可以用来打印建议的一些属性.

create or replace procedure show_recm (in_task_name in varchar2) is
cursor curs is
 select distinct action_id, command, attr1, attr2, attr3, attr4
from user_advisor_actions
where task_name = in_task_name
order by action_id;

v_action number;
v_command varchar2(32);
v_attr1 varchar2(4000);
v_attr2 varchar2(4000);
v_attr3 varchar2(4000);
v_attr4 varchar2(4000);
v_attr5 varchar2(4000);
begin
 open curs;
 dbms_output.put_line('=========================================');
 dbms_output.put_line('Task_name = ' || in_task_name);
 loop
    fetch curs into
      v_action, v_command, v_attr1, v_attr2, v_attr3, v_attr4 ;
  exit when curs%notfound;
  dbms_output.put_line('Action ID: ' || v_action);
  dbms_output.put_line('Command : ' || v_command);
  dbms_output.put_line('Attr1 (name) : ' || SUBSTR(v_attr1,1,30));
  dbms_output.put_line('Attr2 (tablespace): ' || SUBSTR(v_attr2,1,30));
  dbms_output.put_line('Attr3 : ' || SUBSTR(v_attr3,1,30));
  dbms_output.put_line('Attr4 : ' || v_attr4);
  dbms_output.put_line('Attr5 : ' || v_attr5);
  dbms_output.put_line('----------------------------------------');
  end loop;
  close curs;
  dbms_output.put_line('=========end recommendations============');
end show_recm;
/
-- see what the actions are using sample procedure
set serveroutput on size 99999
execute show_recm(:task_name);
A fragment of a sample output from this procedure is as follows:
Task_name = MYTASK
Action ID: 1
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "SH"."CUSTOMERS"
Attr2 (tablespace):
Attr3 : ROWID, SEQUENCE
Attr4 : INCLUDING NEW VALUES
Attr5 :
----------------------------------------
..
----------------------------------------
Action ID: 15
Command : CREATE MATERIALIZED VIEW
Attr1 (name) : "SH"."SH_MV$$_0004"
Attr2 (tablespace): "SH_MVIEWS"
Attr3 : REFRESH FAST WITH ROWID
Attr4 : ENABLE QUERY REWRITE
Attr5 :
----------------------------------------
..
----------------------------------------
Action ID: 19
Command : CREATE INDEX
Attr1 (name) : "SH"."SH_IDX$$_0013"
Attr2 (tablespace): "SH_INDEXES"
Attr3 : "SH"."SH_MV$$_0002"
Attr4 : BITMAP
Attr5 :

SQL工作量日记
在执行分析处理(execute_task)时，SQL Access Advisor保存了关于分析日记的有用信息。可以使用
user_advisor_journal视图来查看日记。输出的信息量依赖于任务参数journaling的设置。

在导入工作量时，各种信息被导入到SQL工作量日记中。它们可以使用user_advisor_sqlw_journal视图来进行查看。例如，如果一个特定的SQL语句引用了无效的表，表丢失统计信息或有权限错误，这些信息会被记录在日记中。输出的信息量可以通过journaling。

为了关闭日记，执行以下语句:

execute dbms_advisor.set_task_parameter('MYTASK', 'JOURNALING', 0);

为了查看信息，执行以下语句:

execute dbms_advisor.set_task_parameter('MYTASK', 'JOURNALING', 4);

为了查看致命信息，执行以下语句:

execute dbms_advisor.set_sqlwkld_parameter('MYWORKLOAD', 'JOURNALING', 1);

停止建议处理
如果SQL Access Advisor执行dbms_advisor.execute_task过程花了太长时间来生成建议，可以执行 dbms_advisor.cancel_task过程并传入任务名来进行停止处理。如果执行dbms_advisor.cancel_task 过程，将不会生成建议。另外可以执行dbms_advisor.interrupt_task过程来终止建议处理。

dbms_advisor.interrupt_task过程会造成一个Advisor操作被终止就像正常结束一样。因此用户可以看到到终止时间点已经格式化的任何建议。

一个终止的任务不能被重启。语法如下:

dbms_advisor.interrupt_task(task_name in varchar2);:

终止任务MY_TASK

execute dbms_advisor.interrupt_task ('my_task');

dbms_advisor.cancel_task过程会造成一个当前执行操作被终止。一个Advisor操作可能花费了几秒来响应这个调用。因为所有Advisor任务过程是同步进行的，为了取消一个操作，必须使用一个单独的数据库会话。

取消命令有效的将任务还原到它启动取消操作开始的条件状态。因此，取消的任务或数据对象不需要重启。

dbms_advisor.cancel_task (task_name in varchar2);

取消任务MYTASK:

execute dbms_advisor.cancel_task('MYTASK');

标记建议
缺省情况下，所有SQL Access Advisor建议已经准备好被实现了，然而，用户可以通过执行 dbms_advisor.mark_recommendation过程来选择跳过或执行所选择的建议。 dbms_advisor.mark_recommendation过程允许用户使用reject或ignore设置来注释一个建议，当生成实现过程时它将造成dbms_advisor.get_task_script过程来跳过它。语法如下:

dbms_advisor.mark_recommendation (
task_name in varchar2
id in number,
action in varchar2);

下面的例子将ID为2的建议标记为reject。这个建议与任何依赖的建议将不会出现在脚本中。

execute dbms_advisor.mark_recommendation('MYTASK', 2, 'REJECT');

修改建议
可以使用dbms_advisor.update_rec_attributes过程，SQL Access Advisor名字与指派给新对象的关系，比如在分析操作时的索引与物化视图。然而，它不是必须要选择合适的名字，因此可以手动设置所有者，名字与新对象的表空间名。对于建议引用的现有数据库对象，所有者与名字不会改变。语法如下:

dbms_advisor.update_rec_attributes (
task_name in varchar2
rec_id in number,
action_id in number,
attribute_name in varchar2,
value in varchar2);

其中,attribute_name参数可以有以下参数值:
.owner:指定建议对象的所有者
.name:指定建议对象名字
.tablespace:指定建议对象表空间

下面的用来修改SH_MVIEWS操作ID为1,建议ID为1的tablespace属性

execute dbms_advisor.update_rec_attributes('MYTASK', 1, 1,'TABLESPACE', 'SH_MVIEWS');

生成SQL脚本
一种查看建议的替代方法就是查询元数据，它将使用dbms_advisor.task_script过程来为SQL语句创建建议脚本。最终脚本是一个可以执行的SQL文件，它可能包含drop,create和alter语句。对于新对象，物化视图名字，物化视图日志名与使用用户定义模板自动生成的索引。你也能在试图执行它之前生成SQL脚本。

这里有四个任务参数控制着命名规则(mview_name_template与index_name_template),这些对象的所有者(def_index_owner与def_mview_owner)与表空间(def_mview_tablespace与 def_index_tablespace)。

下面的例子显示了如何生成包含建议的CLOB:

execute dbms_advisor.create_file(dbms_advisor.get_task_script ('MYTASK'),'ADVISOR_RESULTS', 'advscript.sql');

为了保存脚本文件，必须提供目录路径，因此dbms_advisor.create_file过程需要知道脚本存储位置。另外，必须要对这个目录持有读写权限。下面的例子显示了如何保存一个CLOB类型的指导脚本文件


SQL> create directory advisor_results as '/bak';

Directory created
SQL> grant read,write on directory advisor_results to public;

Grant succeeded

SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('JYTASK'),'ADVISOR_RESULTS', 'jy_advscript.sql');

PL/SQL procedure successfully completed

下面是由这个过程生成的脚本内容的一部分。这个脚本包含了对建议访问结构收集统计信息的调用并且在最后将建议标记为implemented。

Rem Access Advisor V10.1.0.0.0 - Production
Rem
Rem Username: SH
Rem Task: MYTASK
Rem Execution date: 15/04/2005 11:35
Rem
set feedback 1
set linesize 80
set trimspool on
set tab off
set pagesize 60
whenever sqlerror CONTINUE
create materialized view log on "sh"."products"
with rowid, sequence("prod_id","prod_subcategory")
including new values;
alter materialized view log force on "sh"."products"
add rowid, sequence("prod_id","prod_subcategory")
including new values;
..
create materialized view "sh"."mv$$_00510002"
refresh fast with rowid
enable query rewrite
as select sh.customers.cust_state_province c1, count(*) m1 from
sh.customers where (sh.customers.cust_state_province = 'ca') group
by sh.customers.cust_state_province;
begin
dbms_stats.gather_table_stats('"sh"', '"mv$$_00510002"', null,
dbms_stats.auto_sample_size);
end;
/

如何不再需要建议
dbms_advisor.reset_task过程可以将一个任务重新设置为初始启动状态。这将会删除所有建议和任务的中间数据。任务的实际状态被设置为initial。语法如下:

dbms_advisor.reset_task (task_name in varchar2);

下面的例子将任务MYTASK进行重设置:

execute dbms_advisor.reset_task('mytask');

执行快速优化
如果只想要优化单个SQL语句，dbms_advisor.quick_tune过程将接受任务名与一个SQL语句作为参数来执行。它将创建一个任务与工作量，并且执行任务。使用dbms_advisor.quick_tune过程所得到的结果没有差异。它们实际上与使用dbms_advisor.execute_task过程得到的结果是一样的，但是对于只优化一个SQL语句来说，快速优化这种方法更容易。其语法如下:

dbms_advisor.quick_tune (
advisor_name in varchar2,
task_name in varchar2,
attr1 in clob,
attr2 in varchar2 := null,
attr3 in number := null,
task_or_template in varchar2 := null);

下面是快速优化一个SQL语句的例子:

SQL> variable task_name varchar2(255);
SQL> variable sql_stmt varchar2(4000);
SQL> execute :sql_stmt := 'select count(1) cs  from bs_hospital a, bs_biztype g,  mt_biz_fin b, bs_center h, bs_insured j  where a.hospital_id = b.hospital_id    and  b.center_id = g.center_id    and b.biz_type = g.biz_type    and b.center_id =  h.center_id(+)    and b.valid_flag = ''1''    and b.indi_id = j.indi_id    and  (j.urban_type = ''all'' or ''all'' = ''all'')    and (b.district_code = b.center_id and  b.center_id in (430721) or    b.district_code <> b.center_id and b.center_id in (430721)  or b.district_code <> b.center_id and b.district_code in (430721)) and g.biz_stat =  ''2'' and b.biz_type <> ''52'' and b.biz_type <> ''17'' and b.pers_type_detail in (2)  and nvl(b.finish_flag, ''0'') = ''1'' and b.end_date between  to_date(''2014-01-01  00:00:00'', ''yyyy-mm-dd hh24:mi:ss'') and  to_date(''2014-12-31 23:59:59'', ''yyyy-mm- dd hh24:mi:ss'')';

PL/SQL procedure successfully completed
sql_stmt
---------
select count(1) cs  from bs_hospital a, bs_biztype g, mt_biz_fin b, bs_center h,  bs_insured j  where a.hospital_id = b.hospital_id    and b.center_id = g.center_id     and b.biz_type = g.biz_type    and b.center_id = h.center_id(+)    and b.valid_flag =  '1'    and b.indi_id = j.indi_id    and (j.urban_type = 'all' or 'all' = 'all')    and  (b.district_code = b.center_id and b.center_id in (430721) or    b.district_code <>  b.center_id and b.center_id in (430721) or b.district_code <> b.center_id and  b.district_code in (430721)) and g.biz_stat = '2' and b.biz_type <> '52' and b.biz_type  <> '17' and b.pers_type_detail in (2) and nvl(b.finish_flag, '0') = '1' and b.end_date  between  to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and  to_date('2014-12- 31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
SQL> execute :task_name := 'my_quicktune_task';

PL/SQL procedure successfully completed
task_name
---------
my_quicktune_task
SQL> execute dbms_advisor.quick_tune(dbms_advisor.sqlaccess_advisor,:task_name,  :sql_stmt);

PL/SQL procedure successfully completed
task_name
---------
my_quicktune_task
sql_stmt
---------
select count(1) cs  from bs_hospital a, bs_biztype g, mt_biz_fin b, bs_center h,  bs_insured j  where a.hospital_id = b.hospital_id    and b.center_id = g.center_id     and b.biz_type = g.biz_type    and b.center_id = h.center_id(+)    and b.valid_flag =  '1'    and b.indi_id = j.indi_id    and (j.urban_type = 'all' or 'all' = 'all')    and  (b.district_code = b.center_id and b.center_id in (430721) or    b.district_code <>  b.center_id and b.center_id in (430721) or b.district_code <> b.center_id and  b.district_code in (430721)) and g.biz_stat = '2' and b.biz_type <> '52' and b.biz_type  <> '17' and b.pers_type_detail in (2) and nvl(b.finish_flag, '0') = '1' and b.end_date  between  to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and  to_date('2014-12- 31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')


SQL> create or replace procedure show_recm (in_task_name in varchar2) is
  2  cursor curs is
  3   select distinct action_id, command, attr1, attr2, attr3, attr4
  4  from user_advisor_actions
  5  where task_name = in_task_name
  6  order by action_id;
  7
  8  v_action number;
  9  v_command varchar2(32);
 10  v_attr1 varchar2(4000);
 11  v_attr2 varchar2(4000);
 12  v_attr3 varchar2(4000);
 13  v_attr4 varchar2(4000);
 14  v_attr5 varchar2(4000);
 15  begin
 16   open curs;
 17   dbms_output.put_line('=========================================');
 18   dbms_output.put_line('Task_name = ' || in_task_name);
 19   loop
 20      fetch curs into
 21        v_action, v_command, v_attr1, v_attr2, v_attr3, v_attr4 ;
 22    exit when curs%notfound;
 23    dbms_output.put_line('Action ID: ' || v_action);
 24    dbms_output.put_line('Command : ' || v_command);
 25    dbms_output.put_line('Attr1 (name) : ' || SUBSTR(v_attr1,1,30));
 26    dbms_output.put_line('Attr2 (tablespace): ' || SUBSTR(v_attr2,1,30));
 27    dbms_output.put_line('Attr3 : ' || SUBSTR(v_attr3,1,30));
 28    dbms_output.put_line('Attr4 : ' || v_attr4);
 29    dbms_output.put_line('Attr5 : ' || v_attr5);
 30    dbms_output.put_line('----------------------------------------');
 31    end loop;
 32    close curs;
 33    dbms_output.put_line('=========end recommendations============');
 34  end show_recm;
 35  /

Procedure created

SQL> set serveroutput on size 99999
SQL> execute show_recm('my_quicktune_task');

=========================================
Task_name = my_quicktune_task
Action ID: 1
Command : CREATE MATERIALIZED VIEW
Attr1 (name) : "INSUR_CHANGDE"."MV$$_B1D10000
Attr2 (tablespace):
Attr3 : REFRESH FORCE WITH ROWID
Attr4 : ENABLE QUERY REWRITE
Attr5 :
----------------------------------------
Action ID: 2
Command : GATHER TABLE STATISTICS
Attr1 (name) : "INSUR_CHANGDE"."MV$$_B1D10000
Attr2 (tablespace):
Attr3 : -1
Attr4 :
Attr5 :
----------------------------------------
=========end recommendations============

PL/SQL procedure successfully completed

管理任务
每次建议被生成，任务被创建，并且除非对这些任务执行一些维护操作，它们将会随着时间而增长并且将会占用存储空间。有些任务可以想要保留并且防止意外删除。因此对于任务有多个管理操作可以执行。
.更新任务属性
.删除任务
.设置days_to_expire参数

更新任务属性
使用dbms_advisor.update_task_attributes过程，可以执行以下操作:
.改变任务名称
.指定任务描述
.设置任务为只读状态，因上不能被修改
.将任务设置为模板让其它任务可以依赖它进行定义
.改变任务或任务模板的各种属性

其语法如下:

dbms_advisor.update_task_attributes (
task_name in varchar2
new_name in varchar2 := null,
description in varchar2 := null,
read_only in varchar2 := null,
is_template in varchar2 := null,
how_created in varchar2 := null);

下面的例子将任务名称MYTASK更新为TUNING1:

execute dbms_advisor.update_task_attributes('MYTASK', 'TUNING1');

下面的例子将任务TUNING1标记为只读:

execute dbms_advisor.update_task_attributes('TUNING1', read_only => 'TRUE');

下面的例子将把任务MYTASK标记为模板

execute dbms_advisor.update_task_attributes('TUNING1', is_template=>'TRUE');

删除任务
dbms_advisor.delete_task过程将从档案库中删除指导任务。其语法如下:

dbms_advisor.delete_task(task_name in varchar2);

下面的例子将任务MYTASK删除:

execute dbms_advisor.delete_task('MYTASK');

设置DAYS_TO_EXPIRE参数
当一个任务或工作量被创建后，参数days_to_expire被设置为30天。这个参数指示直到创建时间达到指定天数时任务或对象将会由系统自动删除。如果你想要无限期的保存一个任务或工作量，参数 days_to_expire应该被设置为advisor_unlimited。

使用SQL Access Advisor进行SQL优化的例子
使用SQL调优集来加载工作量执行SQL Access Advisor
1.创建任务

SQL> variable task_id number;
SQL> variable task_name varchar2(255);
SQL> execute :task_name:='JYTASK';

PL/SQL procedure successfully completed
task_name
---------
JYTASK
SQL> execute dbms_advisor.create_task('SQL Access Advisor',:task_id,:task_name);

PL/SQL procedure successfully completed
task_id
---------
45354
task_name
---------
JYTASK

2.创建工作量

SQL> variable workload_name varchar2(255);
SQL> execute :workload_name := 'myworkload';

PL/SQL procedure successfully completed
workload_name
---------
myworkload
SQL> execute dbms_advisor.create_sqlwkld(:workload_name,'this is my first workload');

PL/SQL procedure successfully completed
workload_name
---------
myworkload

3.将任务与工作量进行链接

SQL> execute dbms_advisor.add_sqlwkld_ref('JYTASK', 'myworkload');

PL/SQL procedure successfully completed

4.从SQL调优集加载工作量

SQL> exec dbms_sqltune.create_sqlset(sqlset_name => 'MY_STS_WORKLOAD',description =>  'SQL Access Advisor Test',sqlset_owner => 'INSUR_CHANGDE');

PL/SQL procedure successfully completed

SQL>
SQL> declare
  2    cur dbms_sqltune.sqlset_cursor;
  3  begin
  4    open cur for
  5      select value(p)
  6        from table(dbms_sqltune.select_cursor_cache ('parsing_schema_name=''INSUR_CHANGDE'' and force_matching_signature<>0 and  buffer_gets>1000 and executions>100 and command_type<>2',
  7                                                    null,
  8                                                    null,
  9                                                    null,
 10                                                    null,
 11                                                    1,
 12                                                    null,
 13                                                    'all')) p;
 14    dbms_sqltune.load_sqlset(sqlset_name     => 'MY_STS_WORKLOAD',
 15                             populate_cursor => cur);
 16  end;
 17  /

PL/SQL procedure successfully completed



SQL> variable sqlsetname varchar2(30);
SQL> variable workload_name varchar2(30);
SQL> variable saved_stmts number;
SQL> variable failed_stmts number;
SQL> execute :sqlsetname := 'MY_STS_WORKLOAD';

PL/SQL procedure successfully completed
sqlsetname
---------
MY_STS_WORKLOAD
SQL> execute :workload_name := 'myworkload';

PL/SQL procedure successfully completed
workload_name
---------
myworkload

SQL> execute dbms_advisor.import_sqlwkld_sts (:workload_name ,:sqlsetname,  'NEW',1,:saved_stmts, :failed_stmts);

PL/SQL procedure successfully completed
workload_name
---------
myworkload
sqlsetname
---------
MY_STS_WORKLOAD
saved_stmts
---------
1219
failed_stmts
---------
13

5.执行任务

SQL> exec dbms_advisor.execute_task('JYTASK');

PL/SQL procedure successfully completed

6.查看建议

SQL> select rec_id, rank, benefit from user_advisor_recommendations where task_name  ='JYTASK';

    REC_ID       RANK    BENEFIT
---------- ---------- ----------
         1          1          0
         2          2     511856
         3          3          0
         4          4          0
         5          5          0
         6          6          0
         7          7          0
         8          8      17712
         9          9          0
        10         10     171589
        11         11     352280
        12         12          0
        13         13   35201233
        14         14     249249
        15         15          0
        16         16          0
        17         17          0
        18         18          0
        19         19      67650
        20         20      24901
        21         21          0
        22         22      45756
        23         23          0
        24         24          0
        25         25          0
        26         26      44170




SQL> select sql_id, rec_id, precost, postcost,
  2  decode((precost-postcost),0,0,round((precost-postcost)*100/precost,2)) as  percent_benefit
  3  from user_advisor_sqla_wk_stmts
  4  where task_name ='JYTASK' and workload_name ='myworkload';

    SQL_ID     REC_ID    PRECOST   POSTCOST PERCENT_BENEFIT
---------- ---------- ---------- ---------- ---------------
      2450          5       4440       4440               0
      2451         13      19012      13580           28.57
      2452          1       2908       2908               0
      2453          3       4907       4907               0
      2454          9       2665       2665               0
      2455          4       7180       7180               0
      2456         12       4230       4230               0
      2457          9       1521       1521               0
      2458         85      14322        434           96.97
      2459         27       9809       9809               0
      2460          2      31086       8478           72.73
      2461          5       4050       4050               0
      2462          6      15432      15432               0
      2463          1        935        935               0
      2464        307      14490        420            97.1
      2465         20       2900       2175              25
      2466          5       1401       1401               0
      2467          7       1980       1980               0
      2468        128     224180      67254              70
      2469         23       8098       8098               0

下面的PL/SQL过程可以用来打印建议的一些属性.

SQL> create or replace procedure show_recm (in_task_name in varchar2) is
  2  cursor curs is
  3   select distinct action_id, command, attr1, attr2, attr3, attr4
  4  from user_advisor_actions
  5  where task_name = in_task_name
  6  order by action_id;
  7
  8  v_action number;
  9  v_command varchar2(32);
 10  v_attr1 varchar2(4000);
 11  v_attr2 varchar2(4000);
 12  v_attr3 varchar2(4000);
 13  v_attr4 varchar2(4000);
 14  v_attr5 varchar2(4000);
 15  begin
 16   open curs;
 17   dbms_output.put_line('=========================================');
 18   dbms_output.put_line('Task_name = ' || in_task_name);
 19   loop
 20      fetch curs into
 21        v_action, v_command, v_attr1, v_attr2, v_attr3, v_attr4 ;
 22    exit when curs%notfound;
 23    dbms_output.put_line('Action ID: ' || v_action);
 24    dbms_output.put_line('Command : ' || v_command);
 25    dbms_output.put_line('Attr1 (name) : ' || SUBSTR(v_attr1,1,30));
 26    dbms_output.put_line('Attr2 (tablespace): ' || SUBSTR(v_attr2,1,30));
 27    dbms_output.put_line('Attr3 : ' || SUBSTR(v_attr3,1,30));
 28    dbms_output.put_line('Attr4 : ' || v_attr4);
 29    dbms_output.put_line('Attr5 : ' || v_attr5);
 30    dbms_output.put_line('----------------------------------------');
 31    end loop;
 32    close curs;
 33    dbms_output.put_line('=========end recommendations============');
 34  end show_recm;
 35  /

Procedure created


SQL> set serveroutput on size 9999999
SQL> execute show_recm('JYTASK');

=========================================
Task_name = JYTASK
Action ID: 1
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_SPECIAL_DI
Attr2 (tablespace):
Attr3 : ROWID
Attr4 :
Attr5 :
----------------------------------------
Action ID: 3
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."FC_BIZ_POLICY
Attr2 (tablespace):
Attr3 : ROWID, SEQUENCE
Attr4 :  INCLUDING NEW VALUES
Attr5 :
----------------------------------------
Action ID: 5
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_TREAT_TYPE
Attr2 (tablespace):
Attr3 : ROWID, PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 7
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_PERSON_TYP
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 9
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."PFS_INSUR_DET
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 11
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_SPECIAL_DI
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 13
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CORP_STAC"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 15
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CULTURE_ST
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 17
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CALLING"
Attr2 (tablespace):
Attr3 : ROWID
Attr4 :
Attr5 :
----------------------------------------
Action ID: 19
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_OCCUPATION
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 21
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_BIZ_LICE"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 23
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CORP_TYPE"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 25
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_SALARYSYS"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 27
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_JOIN_STA"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 29
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CORP_REVEN
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 31
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_MEDI_ITEM_
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 33
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_HIRED_TYPE
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 35
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CORP_CHARG
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 37
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_ECON_TYPE"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 39
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."CG_INDI_TYPE"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 41
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CORP_FINAL
Attr2 (tablespace):
Attr3 : ROWID
Attr4 :
Attr5 :
----------------------------------------
Action ID: 43
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_LOWINSR"
Attr2 (tablespace):
Attr3 : ROWID
Attr4 :
Attr5 :
----------------------------------------
Action ID: 45
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_SUB_CONN"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 47
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_OFFICIAL_I
Attr2 (tablespace):
Attr3 : ROWID
Attr4 :
Attr5 :
----------------------------------------
Action ID: 49
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_OFFICIAL"
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :
Attr5 :
----------------------------------------
Action ID: 51
Command : CREATE MATERIALIZED VIEW LOG
Attr1 (name) : "INSUR_CHANGDE"."BS_CHARGE_COD
Attr2 (tablespace):
Attr3 : PRIMARY KEY
Attr4 :ant succeeded


SQL> create directory advisor_results as '/bak';

Directory created
SQL> grant read,write on directory advisor_results to public;

Grant succeeded

SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('JYTASK'),'ADVISOR_RESULTS', 'jy_advscript.sql');

PL/SQL procedure successfully completed

[IBMP740-1:root:/bak]#more jy_advscript.sql
Rem  SQL Access Advisor: Version 10.2.0.4.0 - Production
Rem
Rem  Username:        INSUR_CHANGDE
Rem  Task:            JYTASK
Rem  Execution date:  06/09/2016 09:48
Rem

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_SPECIAL_DISEASE_RANGE"
    WITH ROWID ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."FC_BIZ_POLICY"
    WITH ROWID, SEQUENCE ("POLICY_CODE","POLICY_VALUE","CENTER_OR_HOSP","VALID_FLAG","CENTER_ID")
    INCLUDING NEW VALUES;

...省略...
CREATE MATERIALIZED VIEW "INSUR_CHANGDE"."MV$$_B12A0069"
    REFRESH FORCE WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT A.SERIAL_MATCH, A.HOSPITAL_ID, A.MATCH_TYPE, A.ITEM_CODE, A.ITEM_NAME,
       A.HOSP_CODE, A.HOSP_NAME, A.MODEL AS MODEL_NAME, A.HOSP_MODEL, TO_CHAR (A.EDIT_DATE,
       'yyyy-mm-dd') AS EDIT_DATE, A.EDIT_STAFF, A.EDIT_MAN, A.VALID_FLAG, NVL (B.SP_FLAG,
       '0') AS SP_FLAG, TO_CHAR(A.EFFECT_DATE, 'yyyy-mm-dd') AS EFFECT_DATE,
       TO_CHAR(A.EXPIRE_DATE, 'yyyy-mm-dd') AS EXPIRE_DATE, A.AUDIT_FLAG, TO_CHAR (A.AUDIT_DATE,
       'yyyy-mm-dd') AS AUDIT_DATE, A.AUDIT_STAFF, A.AUDIT_MAN, B.CLINIC_FLAG,
       NVL(B.DEFRAY_TYPE_CJ,B.DEFRAY_TYPE) AS DEFRAY_TYPE_CJ, B.STAPLE_FLAG,
       B.SELF_SCALE, NVL(A.HOSP_PRICE, 0) AS PRICE, B.STAT_TYPE AS STAT_TYPE,
       DECODE(SUBSTR('110',1,1),'5', DECODE(B.BO_FLAG,1,DECODE(SUBSTR (B.DEFRAY_TYPE,1,3),
       'Bed', B.DEFRAY_TYPE, 'A000_00'),'A000_100'),'4', DECODE(B.WL_FLAG,1,DECODE (B.DEFRAY_TYPE,
       'A001','A001','A000_00'),'A000_100'), B.DEFRAY_TYPE) AS DEFRAY_TYPE, DECODE(NVL (C.TREATMENT_TYPE,
       '000'), '161', '161', '162', '162', '000') AS TREATMENT_TYPE FROM  BS_CATALOG_MATCH
       A, BS_ITEM B, BS_SPECIAL_ITEM_RANGE C ,BS_CENTER D WHERE A.CENTER_ID =
       '430701' AND A.HOSPITAL_ID = '4307000001' AND A.HOSP_CODE = '111965**'
       AND A.MATCH_TYPE = '0' AND A.VALID_FLAG = '1' AND A.EFFECT_DATE < = TO_DATE(
       '2016-09-02','yyyy-mm-dd') AND (A.EXPIRE_DATE >= TO_DATE('2016-09-02'
       ,'yyyy-mm-dd') OR A.EXPIRE_DATE IS NULL) AND A.CENTER_ID = D.CENTER_ID
       AND NVL(D.CATALOG_CENTER,D.CENTER_ID) = B.CENTER_ID AND TO_CHAR(A.ITEM_CODE)
       = (B.ITEM_CODE) AND B.VALID_FLAG = '1' AND B.EFFECT_DATE < = TO_DATE('2016-09-02'
       ,'yyyy-mm-dd') AND (B.EXPIRE_DATE >= TO_DATE('2016-09-02','yyyy-mm-dd'
       ) OR B.EXPIRE_DATE IS NULL) AND A.CENTER_ID = C.CENTER_ID(+) AND A.MATCH_TYPE
       = C.MEDI_ITEM_TYPE(+) AND A.ITEM_CODE = C.MEDI_ITEM_CODE(+) AND '1' =
       C.VALID_FLAG(+) AND TO_DATE('2016-09-02','yyyy-mm-dd') >= C.EFFECT_DATE(+)
       AND TO_DATE('2016-09-02','yyyy-mm-dd') < = NVL(C.EXPIRE_DATE(+), TO_DATE(
       '2099-12-31', 'yyyy-mm-dd')) AND '131' <> C.TREATMENT_TYPE(+);

begin
  dbms_stats.gather_table_stats('"INSUR_CHANGDE"','"MV$ $_B12A0069"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "INSUR_CHANGDE"."MV$$_B12A006A"
    REFRESH FORCE WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT A.SERIAL_MATCH, A.HOSPITAL_ID, A.MATCH_TYPE, A.ITEM_CODE, A.ITEM_NAME,
       A.HOSP_CODE, A.HOSP_NAME, A.MODEL AS MODEL_NAME, A.HOSP_MODEL, TO_CHAR (A.EDIT_DATE,
       'yyyy-mm-dd') AS EDIT_DATE, A.EDIT_STAFF, A.EDIT_MAN, A.VALID_FLAG, NVL (B.SP_FLAG,
       '0') AS SP_FLAG, TO_CHAR(A.EFFECT_DATE, 'yyyy-mm-dd') AS EFFECT_DATE,
       TO_CHAR(A.EXPIRE_DATE, 'yyyy-mm-dd') AS EXPIRE_DATE, A.AUDIT_FLAG, TO_CHAR (A.AUDIT_DATE,
       'yyyy-mm-dd') AS AUDIT_DATE, A.AUDIT_STAFF, A.AUDIT_MAN, B.CLINIC_FLAG,
       NVL(B.DEFRAY_TYPE_CJ,B.DEFRAY_TYPE) AS DEFRAY_TYPE_CJ, B.STAPLE_FLAG,
       B.SELF_SCALE, NVL(A.HOSP_PRICE, 0) AS PRICE, B.STAT_TYPE AS STAT_TYPE,
       DECODE(SUBSTR('120',1,1),'5', DECODE(B.BO_FLAG,1,DECODE(SUBSTR (B.DEFRAY_TYPE,1,3),
       'Bed', B.DEFRAY_TYPE, 'A000_00'),'A000_100'),'4', DECODE(B.WL_FLAG,1,DECODE (B.DEFRAY_TYPE,
       'A001','A001','A000_00'),'A000_100'), B.DEFRAY_TYPE) AS DEFRAY_TYPE, DECODE(NVL (C.TREATMENT_TYPE,
       '000'), '161', '161', '162', '162', '000') AS TREATMENT_TYPE FROM  BS_CATALOG_MATCH
       A, BS_ITEM B, BS_SPECIAL_ITEM_RANGE C ,BS_CENTER D WHERE A.CENTER_ID =
       '430722' AND A.HOSPITAL_ID = '4307220004' AND A.HOSP_CODE = '00358' AND
       A.MATCH_TYPE = '0' AND A.VALID_FLAG = '1' AND A.EFFECT_DATE < = TO_DATE(
       '2016-09-01','yyyy-mm-dd') AND (A.EXPIRE_DATE >= TO_DATE('2016-09-01'
       ,'yyyy-mm-dd') OR A.EXPIRE_DATE IS NULL) AND A.CENTER_ID = D.CENTER_ID
       AND NVL(D.CATALOG_CENTER,D.CENTER_ID) = B.CENTER_ID AND TO_CHAR(A.ITEM_CODE)
       = (B.ITEM_CODE) AND B.VALID_FLAG = '1' AND B.EFFECT_DATE < = TO_DATE('2016-09-01'
       ,'yyyy-mm-dd') AND (B.EXPIRE_DATE >= TO_DATE('2016-09-01','yyyy-mm-dd'
       ) OR B.EXPIRE_DATE IS NULL) AND A.CENTER_ID = C.CENTER_ID(+) AND A.MATCH_TYPE
       = C.MEDI_ITEM_TYPE(+) AND A.ITEM_CODE = C.MEDI_ITEM_CODE(+) AND '1' =
       C.VALID_FLAG(+) AND TO_DATE('2016-09-01','yyyy-mm-dd') >= C.EFFECT_DATE(+)
       AND TO_DATE('2016-09-01','yyyy-mm-dd') < = NVL(C.EXPIRE_DATE(+), TO_DATE(
       '2099-12-31', 'yyyy-mm-dd')) AND '131' <> C.TREATMENT_TYPE(+);

begin
  dbms_stats.gather_table_stats('"INSUR_CHANGDE"','"MV$ $_B12A006A"',NULL,dbms_stats.auto_sample_size);
end;
/

...省略...

使用任务模板来加载工作量执行SQL Access Advisor
1.创建任务模板my_template

SQL> variable template_id number;
SQL> variable template_name varchar2(255);
SQL> execute :template_name := 'my_template';

PL/SQL procedure successfully completed
template_name
---------
my_template
SQL> execute dbms_advisor.create_task('SQL Access Advisor',:template_id,:template_name,  is_template => 'true');

PL/SQL procedure successfully completed
template_id
---------
45357
template_name
---------
my_template

2.设置模板参数

SQL> execute dbms_advisor.set_task_parameter(:template_name, 'INDEX_NAME_TEMPLATE',  'SH_IDX$$_');

PL/SQL procedure successfully completed
template_name
---------
my_template
SQL> execute dbms_advisor.set_task_parameter(:template_name, 'MVIEW_NAME_TEMPLATE',  'SH_MV$$_');

PL/SQL procedure successfully completed
template_name
---------
my_template
SQL> -- set default tablespace for recommended indexes/mvs
SQL> execute dbms_advisor.set_task_parameter(:template_name, 'DEF_INDEX_TABLESPACE',  'USERS');

PL/SQL procedure successfully completed
template_name
---------
my_template
SQL> execute dbms_advisor.set_task_parameter(:template_name, 'DEF_MVIEW_TABLESPACE',  'USERS');

PL/SQL procedure successfully completed
template_name
---------
my_template

3.使用模板来创建任务mytask

SQL> variable task_id number;
SQL> variable task_name varchar2(255);
SQL> execute :task_name := 'mytask';

PL/SQL procedure successfully completed
task_name
---------
mytask
SQL> execute dbms_advisor.create_task('SQL Access Advisor', :task_id,:task_name,  template=>'my_template');

PL/SQL procedure successfully completed
task_id
---------
45376
task_name
---------
mytask

4.创建工作量my_template_workload

SQL> variable workload_name varchar2(255);
SQL> execute :workload_name := 'my_template_workload';

PL/SQL procedure successfully completed
workload_name
---------
my_template_workload
SQL> execute dbms_advisor.create_sqlwkld(:workload_name,'this is my first workload');

PL/SQL procedure successfully completed
workload_name
---------
my_template_workload

5.从SQL调优集加载工作量

SQL> exec dbms_sqltune.create_sqlset(sqlset_name =>  'MY__TEMPLATE_STS_WORKLOAD',description => 'SQL Access Advisor Test',sqlset_owner =>  'INSUR_CHANGDE');

PL/SQL procedure successfully completed
SQL> declare
  2    cur dbms_sqltune.sqlset_cursor;
  3  begin
  4    open cur for
  5      select value(p)
  6        from table(dbms_sqltune.select_cursor_cache ('parsing_schema_name=''INSUR_CHANGDE''
  7
  8  and force_matching_signature<>0 and buffer_gets>1000 and executions>100 and
  9
 10  command_type<>2',
 11                                                    null,
 12                                                    null,
 13                                                    null,
 14                                                    null,
 15                                                    1,
 16                                                    null,
 17                                                    'all')) p;
 18    dbms_sqltune.load_sqlset(sqlset_name     => 'MY__TEMPLATE_STS_WORKLOAD',
 19                             populate_cursor => cur);
 20  end;
 21  /

PL/SQL procedure successfully completed

SQL> variable sqlsetname varchar2(30);
SQL> variable workload_name varchar2(30);
SQL> variable saved_stmts number;
SQL> variable failed_stmts number;
SQL> execute :sqlsetname := 'MY__TEMPLATE_STS_WORKLOAD';

PL/SQL procedure successfully completed
sqlsetname
---------
MY__TEMPLATE_STS_WORKLOAD
SQL> execute :workload_name := 'my_template_workload';

PL/SQL procedure successfully completed
workload_name
---------
my_template_workload
SQL> execute dbms_advisor.import_sqlwkld_sts (:workload_name ,:sqlsetname,  'NEW',1,:saved_stmts, :failed_stmts);

PL/SQL procedure successfully completed
workload_name
---------
my_template_workload
sqlsetname
---------
MY__TEMPLATE_STS_WORKLOAD
saved_stmts
---------
650
failed_stmts
---------
38

6.创建任务与工作量之间的链接

SQL> execute dbms_advisor.add_sqlwkld_ref('mytask','my_template_workload');

PL/SQL procedure successfully completed

7.执行任务

SQL> execute dbms_advisor.execute_task('mytask');

PL/SQL procedure successfully completed

8.生成建议脚本

SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('mytask'),'ADVISOR_RESULTS', 'jy_mytask_advscript.sql');

PL/SQL procedure successfully completed


[IBMP740-1:root:/bak]# more jy_mytask_advscript.sql
Rem  SQL Access Advisor: Version 10.2.0.4.0 - Production
Rem
Rem  Username:        INSUR_CHANGDE
Rem  Task:            mytask
Rem  Execution date:  06/09/2016 10:42
Rem

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_PERSON_TYPE"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_HIRED_TYPE"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_HOSP_TYPE"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_OFFICIAL_IDEN"
    WITH ROWID ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_TOWNS"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_OFFICIAL"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_VILLAGE"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_SPECIAL"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_BANK_INFO"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_FOLK"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_TAX_INFO"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_STREET"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_FUND_TYPE"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_SEX"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_OCCUPATION"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_COMMUNITY"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_POSITION"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_KINDRED"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."CM_OUTHOS_CIRCS"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_DISTRICT"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."CG_INDI_REASON"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."SYS_QUESTIONNAIRE"
    WITH ROWID ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_GROUP"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_HOSP_BIZ_PERS"
    WITH ROWID ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_HOSPITAL_COLLATE"
    WITH ROWID ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_HOSPITAL"
    WITH ROWID ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_BIZTYPE"
    WITH ROWID, PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."CG_INDI_ALT_DATA"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."MS_RECEIVE_SQL"
    WITH ROWID ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."MQ_SQL"
    WITH ROWID ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."LV_PRD"
    WITH ROWID, SEQUENCE("CALC_PRD","CURR_YEAR","CENTER_ID")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_TREAT_TYPE"
    WITH ROWID, PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_SPECIAL_DISEASE_RANGE"
    WITH ROWID ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."BS_DISEASE"
    WITH ROWID, PRIMARY KEY ;

CREATE MATERIALIZED VIEW LOG ON
    "INSUR_CHANGDE"."APPLICATION_VERSION"
    WITH PRIMARY KEY ;

CREATE MATERIALIZED VIEW "INSUR_CHANGDE"."SH_MV$$_0008"
    TABLESPACE "USERS"
    REFRESH FORCE WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT SUM(CITY_CODE) CITY_CODE FROM ( SELECT '1' CITY_CODE FROM BS_OUT O WHERE
       O.LEAVE_TYPE <> '2' AND O.LEAVE_STATUS = '1' AND O.INDI_ID = '208227'
       AND TO_CHAR(O.LEAVE_DATE, 'yyyy-mm-dd') < = '2016-09-06' UNION SELECT
       '1' CITY_CODE FROM BS_OUT O, (SELECT BOC.LEAVE_NO, BOC.INPUT_DATE FROM
       BS_OUT_CHANGE BOC, BS_OUT O WHERE BOC.LEAVE_NO = O.LEAVE_NO AND BOC.VALUE_AFTER
       = '异地正式取消' AND O.INDI_ID = '208227') C WHERE O.LEAVE_NO = C.LEAVE_NO(+)
       AND O.LEAVE_TYPE <> '2' AND O.LEAVE_STATUS = '4' AND O.INDI_ID = '208227'
       AND TO_CHAR(O.LEAVE_DATE, 'yyyy-mm-dd') < = '2016-09-06' AND TO_CHAR(C.INPUT_DATE,
       'yyyy-mm-dd') > '2016-09-06' UNION SELECT NVL(BI.CITY_CODE, 0) CITY_CODE
       FROM BS_INSURED BI WHERE BI.INDI_ID = '208227' AND NOT EXISTS (SELECT
       1 FROM BS_OUT BO WHERE BO.INDI_ID = BI.INDI_ID AND BO.LEAVE_TYPE <> 2
       AND BO.LEAVE_STATUS = 1) );

begin
  dbms_stats.gather_table_stats('"INSUR_CHANGDE"','"SH_MV$ $_0008"',NULL,dbms_stats.auto_sample_size);
end;
/

使用SQL Caceh加载工作量来调用SQL Access Advisor
1.创建名为my_cache_workload的工作量

SQL> execute :workload_name := 'my_cache_workload';

PL/SQL procedure successfully completed
workload_name
---------
my_cache_workload
SQL> execute dbms_advisor.create_sqlwkld(:workload_name);

PL/SQL procedure successfully completed
workload_name
---------
my_cache_workload

2.设置过滤
只加载只包含INSUR_CHANGDE用户所拥有表的SQL语句

SQL> execute dbms_advisor.set_sqlwkld_parameter('my_cache_workload', 'USERNAME_LIST',  'INSUR_CHANGDE');

PL/SQL procedure successfully completed

3.从SQL Cache中加载工作量

SQL> variable task_id number;
SQL> variable task_name varchar2(255);
SQL> variable workload_name varchar2(255);
SQL> variable saved_stmts number;
SQL> variable failed_stmts number;
SQL> execute :workload_name := 'my_cache_workload';

PL/SQL procedure successfully completed
workload_name
---------
my_cache_workload

SQL> EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE (:workload_name, 'APPEND', 2,  :saved_stmts, :failed_stmts);

PL/SQL procedure successfully completed
workload_name
---------
my_cache_workload
saved_stmts
---------
120672
failed_stmts
---------
1515

SQL> SELECT num_select_stmt, create_date
  2  FROM user_advisor_sqlw_sum
  3  WHERE workload_name = :workload_name;

NUM_SELECT_STMT CREATE_DATE
--------------- -----------
          84547 2016/9/6 11
workload_name
---------
my_cache_workload

SQL> SELECT sql_id, username, optimizer_cost, SUBSTR(sql_text, 1, 30)
  2  FROM user_advisor_sqlw_stmts
  3  WHERE workload_name ='my_cache_workload'
  4  ORDER BY sql_id;

    SQL_ID USERNAME                       OPTIMIZER_COST SUBSTR(SQL_TEXT,1,30)
---------- ------------------------------ --------------  --------------------------------------------------------------------------------
      4319 INSUR_CHANGDE                               0 insert into bs_indi_freeze_his
      4320 INSUR_CHANGDE                               0 update bs_pres_insur set
      4321 INSUR_CHANGDE                               0 select a.serial_match,
      4322 INSUR_CHANGDE                               0 update mt_serial set cur_num_n
      4323 INSUR_CHANGDE                               0 select nvl(catalog_center,cent
      4324 INSUR_CHANGDE                               0 select scene_value from mt_biz
      4325 INSUR_CHANGDE                               0 update mt_serial set cur_num_n
      4326 INSUR_CHANGDE                               0 insert into bs_mdi_indi_acc( I
      4327 INSUR_CHANGDE                               0 select biz_type,center_id from
      4328 INSUR_CHANGDE                               0 select bi.indi_id,bi.pers_type
      4329 INSUR_CHANGDE                               0 select nvl(cp.indi_join_flag,0
      4330 INSUR_CHANGDE                               0 update mt_serial set cur_num_n
      4331 INSUR_CHANGDE                               0 update mt_serial set cur_num_n
      4332 INSUR_CHANGDE                               0 update mt_serial set cur_num_n
      4333 INSUR_CHANGDE                               0 select a.dataobj_name    from
      4334 INSUR_CHANGDE                               0 update mt_serial set cur_num_n
      4335 INSUR_CHANGDE                               0 select password from bs_cards
      4336 INSUR_CHANGDE                               0 select t.old_value,t.new_value
      4337 INSUR_CHANGDE                               0 update mt_serial set cur_num_n
      4338 INSUR_CHANGDE                               0 update mt_serial set cur_num_n

4.创建名为my_cache_task的任务

SQL> execute :task_name := 'my_cache_task';

PL/SQL procedure successfully completed
task_name
---------
my_cache_task
SQL> execute dbms_advisor.create_task('SQL Access Advisor', :task_id, :task_name);

PL/SQL procedure successfully completed
task_id
---------
45556
task_name
---------
my_cache_task

5.在任务与工作量之间创建链接

SQL> execute dbms_advisor.add_sqlwkld_ref('my_cache_task','my_cache_workload');

PL/SQL procedure successfully completed

6.执行任务

SQL> execute dbms_advisor.execute_task('my_cache_task');

PL/SQL procedure successfully completed

7.生成脚本

SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('my_cache_task'),'ADVISOR_RESULTS', 'my_cache_workload_script.sql');

PL/SQL procedure successfully completed