Oracle SQL Access Advisor 说明

欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入 --Task recommendation 是一个范围，从简单的建议到复杂的解决方案。当advisortask 执行时，SQL Access Advisor 会仔细分析收集数据和用户定义的参数。 1.3.4 Viewand implement the recommendation

欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入

    --Task recommendation 是一个范围，从简单的建议到复杂的解决方案。当advisortask 执行时，SQL Access Advisor 会仔细分析收集数据和用户定义的参数。

    1.3.4   Viewand implement the recommendations

    You can view therecommendations from SQL Access Advisor in either of the following ways:

    --可以使用如下2种方法来查看recommendation的内容：

    （1）Using thecatalog views

    （2）Generating ascript using the DBMS_ADVISOR.GET_TASK_SCRIPT procedure

    In EnterpriseManager, you may display the recommendations after SQL Access Advisor processhas completed. See "ViewingRecommendations" for a description of using the catalog views toview the recommendations. See "GeneratingSQL Scripts" to see how to create a script.

    --在OEM中，在SQL Access Advisor 进程处理完毕后会自动显示recommendation。

    You need notaccept all recommendations. You can mark the ones to be included in therecommendation script. However, when base table partitioning is recommended,some recommendations depend on others. For example, you cannot implement alocal index if you do not also implement the partitioning recommendation on theindex base table.

    The final stepis then implementing the recommendations and verifying that query performancehas improved.

    1.3.5 SQLAccess Advisor Repository

    All theinformation needed and generated by SQL Access Advisor resides in the Advisorrepository, which is a part of the database dictionary. The benefits of usingthe repository are that it:

    --Advisor 生成的所有信息都存放在Advisor repository中，其是数据字典的一部分，使用repository有如下好处：

    （1）    Collects a complete workloadfor SQL Access Advisor.

    （2）    Supports historical data.

    （3）    Is managed by the server.

    1.3.6 使用SQLAccess Advisor需要的权限

    You must have the ADVISOR privilege tomanage or use SQL Access Advisor. When processing a workload, SQL AccessAdvisor attempts to validate each statement to identify table and columnreferences. The database achieves validation by processing each statement as ifit were being executed by the statement's original user.

    --必须需要有ADVISOR权限

    If the user doesnot have SELECT privileges to a particular table, then SQL AccessAdvisor bypasses the statement referencing the table. This behavior can causemany statements to be excluded from analysis. If SQL Access Advisor excludesall statements in a workload, then the workload is invalid. SQL Access Advisorreturns the following message:

    QSM-00774, thereare no SQL statements to process for task TASK_NAME

    --必须需要有指定表的select 的权限，否则会报QSM-774错误。

    To avoid missingcritical workload queries, the current database user must have SELECT privilegeson the tables targeted for materialized view analysis. For these tables, these SELECT privilegescannot be obtained through a role.

    Additionally,you must have the ADMINISTER SQL TUNING SET privilege to create andmanage workloads in SQL tuning set objects. To run the Advisor on SQL tuningsets owned by other users, you must have the ADMINISTER ANY SQL TUNING SET privilege.

    --还需要 ADMINISTER SQL TUNING SET的权限来创建和管理workload。

    二。手工生成SQLAccess Advisor 示例

    From:

    http://www.oracle-base.com/articles/10g/SQLAccessAdvisor10g.php

    2.1 DBMS_ADVISOR

    The DBMS_ADVISOR packagecan be used to create and execute any advisor tasks, including SQL AccessAdvisor tasks. The following example shows how it is used to create, executeand display a typical SQL Access Advisor script for the current workload.

    --DBMS_ADVISOR 包可以用来创建和执行advisor 任务。

    DECLARE

    l_taskname     VARCHAR2（30）  := 'test_sql_access_task';

    l_task_desc    VARCHAR2（128）  := 'Test SQL Access Task';

    l_wkld_name    VARCHAR2（30）   := 'test_work_load';

    l_saved_rows   NUMBER         := 0;

    l_failed_rows  NUMBER         := 0;

    l_num_found    NUMBER;

    BEGIN

    -- Create an SQLAccess Advisor task.

    DBMS_ADVISOR.create_task （

    advisor_name => DBMS_ADVISOR.sqlaccess_advisor,

    task_name    => l_taskname,

    task_desc    => l_task_desc）；

    -- Reset the task.

    DBMS_ADVISOR.reset_task（task_name => l_taskname）；

    -- Create a workload.

    SELECT COUNT（*）

    INTO   l_num_found

    FROM   user_advisor_sqlw_sum

    WHERE  workload_name =l_wkld_name;

    IFl_num_found = 0 THEN

    DBMS_ADVISOR.create_sqlwkld（workload_name => l_wkld_name）；

    ENDIF;

    -- Link the workload to the task.

    SELECT count（*）

    INTO   l_num_found

    FROM   user_advisor_sqla_wk_map

    WHERE  task_name     = l_taskname

    AND    workload_name =l_wkld_name;

    IFl_num_found = 0 THEN

    DBMS_ADVISOR.add_sqlwkld_ref（

    task_name     => l_taskname,

    workload_name => l_wkld_name）；

    ENDIF;

    -- Set workload parameters.

    DBMS_ADVISOR.set_sqlwkld_parameter（l_wkld_name, 'ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED）；

    DBMS_ADVISOR.set_sqlwkld_parameter（l_wkld_name,'MODULE_LIST', DBMS_ADVISOR.ADVISOR_UNUSED）；

    DBMS_ADVISOR.set_sqlwkld_parameter（l_wkld_name, 'SQL_LIMIT',DBMS_ADVISOR.ADVISOR_UNLIMITED）；

    DBMS_ADVISOR.set_sqlwkld_parameter（l_wkld_name, 'ORDER_LIST', 'PRIORITY,OPTIMIZER_COST'）；

    DBMS_ADVISOR.set_sqlwkld_parameter（l_wkld_name, 'USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED）；

    DBMS_ADVISOR.set_sqlwkld_parameter（l_wkld_name, 'VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED）；

    DBMS_ADVISOR.import_sqlwkld_sqlcache（l_wkld_name, 'REPLACE', 2,l_saved_rows, l_failed_rows）；

    -- Set task parameters.

    DBMS_ADVISOR.set_task_parameter（l_taskname, '_MARK_IMPLEMENTATION','FALSE'）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, 'EXECUTION_TYPE','INDEX_ONLY'）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, 'MODE', 'COMPREHENSIVE'）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, 'STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, 'DML_VOLATILITY', 'TRUE'）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, 'ORDER_LIST','PRIORITY,OPTIMIZER_COST'）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, 'WORKLOAD_SCOPE','PARTIAL'）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, 'DEF_INDEX_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, 'DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, 'DEF_MVIEW_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED）；

    DBMS_ADVISOR.set_task_parameter（l_taskname, 'DEF_MVIEW_OWNER', DBMS_ADVISOR.ADVISOR_UNUSED）；

    -- Execute the task.

    DBMS_ADVISOR.execute_task（task_name => l_taskname）；

    END;

    /

    -- Display the resultingscript.

    SET LONG 100000

    SET PAGESIZE 50000

    SELECT DBMS_ADVISOR.get_task_script（'test_sql_access_task'） AS script FROM  dual;

    SET PAGESIZE 24

    The value for the SET LONG commandshould be adjusted to allow the whole script to be displayed.

    在我测试环境上的输入结果如下：

    PL/SQL procedure successfully completed.

    SCRIPT

    --------------------------------------------------------------------------------

    Rem  SQL AccessAdvisor: Version 10.2.0.4.0 - Production

    Rem

    Rem  Username:        SYS

    Rem  Task:            test_sql_access_task

    Rem  Executiondate:  31/01/2012 21:50

    Rem

    CREATE BITMAP INDEX "QSOA"."DATA_OA_MESSAGE_IDX$$_167F0001"

    ON"QSOA"."DATA_OA_MESSAGE"

    （"MESS_TYPE"）

    COMPUTESTATISTICS;

    CREATE INDEX"ZHAOKA"."CFG_GAME_AREA_S_IDX$$_167F0004"

    ON "ZHAOKA"."CFG_GAME_AREA_SERVER"

    （"AREA_ID","AREA_NAME","SERVER_ID","SERVER_NAME"）

    COMPUTESTATISTICS;

    …

    2.2 Quick Tune

    If you just wantto tune an individual statement you can use the QUICK_TUNE procedureas follows.

    --如果仅仅是调整一个独立的语句，可以使用QUICK_TUNE过程：

    BEGIN

    DBMS_ADVISOR.quick_tune（

    advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,

    task_name    =>'emp_quick_tune',

    attr1        => 'SELECT e.*FROM emp e WHERE UPPER（e.ename） = ''SMITH'''）；

    END;

    /

    Any recommendations can then be displayed using the previous query with the correcttask name specified.

    查询输出结果和之前的一样，使用：

    Select DBMS_ADVISOR.get_task_script（‘emp_quick_tune’） fromdual;

    2.3 Related Views

    The followingviews can be used to display the SQL Access Advisor output without usingEnterprise Manager or the get_task_script function:

    --可以使用以下视图来查看advisor的输出：

    （1）    DBA_ADVISOR_TASKS：Basic information about existingtasks.

    （2）    DBA_ADVISOR_LOG ：Status information about existingtasks.

    （3）    DBA_ADVISOR_FINDINGS ： Findings identified for anexisting task.

    （4）    DBA_ADVISOR_RECOMMENDATIONS ： Recommendations for the problemsidentified by an existing task.

  [1] [2]