【Q&A with explanations】OCP 1z0-053 QUESTION3:DBMS_SPM

2013 年 9 月 10 日 by Leave a Comment



    • OCP_1z0-053_QUESTION4
    【题目示意】
        此题考SQL Plan Management相关的内容

    【解析】
         DBMS_SPM包允许用户使用SQL计划管理功能来管理SQL执行计划，SQL计划管理功能可以通过长时间的记录和分析SQL语句执行计划来有效的防止由于突然间更改一个SQL语句执行计划所导致数据库性能的衰退，而且还可以通过已知的一组高效的执行计划生成一些执行计划基线。这些SQL计划基线能够随后用于保证适当的性能，即使是在系统发生改变的时候，通常在如下的情况使用SQL计划功能来管理SQL执行计划：
         数据库升级安装改变优化器时，通常会有少部分的执行计划改变，大部分可能是有变化的或者是有提高的。无论怎样，还是存在一部分的计划改变所导致性能的衰退问题。此时利用SQL计划基线能显著地减少由于数据库更新带来的性能问题。
        正在运行的系统和不断变化的数据会带来一些性能问题。利用SQL计划基线能可以减少性能回退同时可以维持系统稳定
        有时部署新的系统模块相当于引用新的SQL语句到系统中，应用程序需要有适当的SQL执行计划，而这些新的执行计划需要通过一些标准的测试获得，使用SQL计划基线能在随时间的变化产生更好的性能
        DBMS_SPM包属于SYS用户，其他用户需要被分配到ADMINISTER SQL MANAGEMENT OBJECT权限才可以执行这个包

        ● CONFIGURE 程序
           这个程序用于设置SQL管理程序的配置选项，使用parameter/value 的格式。这个函数可能被调用多次，每次可以设置不同的值。
           语法

    DBMS_SPM.CONFIGURE (
       parameter_name    IN VARCHAR2,
       parameter_value   IN NUMBER);

    参数设置

    Parameter Description
    parameter_name Name of parameter to set (see table below)
    parameter_value Value of parameter to use (see table below)

     

    Name和Values的设置

    Name Description Possible Values Default Value
    space_budget_percent Maximum percent of SYSAUX space that can be used for SQL management base 1,2, …, 50 10
    plan_retention_weeks Number of weeks to retain unused plans before they are purged 5,6, …, 523 53

    【实验】
    查看SQL management base (SMB)当前配置

    SYS@ENMOEDU> SELECT * FROM DBA_SQL_MANAGEMENT_CONFIG;

    PARAMETER_NAME            PARAMETER_VALUE LAST_MODIFIED       MODIFIED_BY
    ------------------------- --------------- ------------------ ---------------------------
    SPACE_BUDGET_PERCENT                   10
    PLAN_RETENTION_WEEKS                   53

    修改space_budget_percent配置

    SYS@ENMOEDU> BEGIN
      2    DBMS_SPM.CONFIGURE('space_budget_percent',1);
      3  END;
      4  /

    PL/SQL PROCEDURE successfully completed.

    检查修改结果

    SYS@ENMOEDU> SELECT * FROM DBA_SQL_MANAGEMENT_CONFIG;

    PARAMETER_NAME          PARAMETER_VALUE LAST_MODIFIED                      MODIFIED_BY
    ----------------------- --------------- ------------------------------ ---------------
    SPACE_BUDGET_PERCENT                  1  10-SEP-13 10.37.01.000000 AM             SYS
    PLAN_RETENTION_WEEKS                 53

     
    创建SQL_TUNING_SET

    SYS@ENMOEDU> BEGIN
      2    DBMS_SQLTUNE.CREATE_SQLSET(
      3      sqlset_name => 'my_sql_tuning_set',
      4      description  => 'use SPM workload');
      5  END;
      6  /

    PL/SQL PROCEDURE successfully completed.

    通过AWR快照装载SQL_TUNING_SET

    SYS@ENMOEDU>DECLARE
      2   baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
      3  BEGIN
      4   OPEN baseline_cursor FOR
      5      SELECT VALUE(p)
      6   FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY (begin_snap=>114,end_snap=>115)) p;
      7 
      8      DBMS_SQLTUNE.LOAD_SQLSET(
      9               sqlset_name     => 'my_sql_tuning_set',
     10               populate_cursor => baseline_cursor);
     11  END;
     12  /

    PL/SQL PROCEDURE successfully completed.

    查看SQL_TUNING_SET内容

    SYS@ENMOEDU> SELECT sql_id,sql_text FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('my_sql_tuning_set'));
     
    SQL_ID        SQL_TEXT
    ------------- --------------------------------------------------------------------------
    089dbukv1aanh SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL
    08bqjmf8490s2 SELECT PARAMETER_VALUE FROM MGMT_PARAMETERS WHERE PARAMETER_NAME = :B1
    08vznc16ycuag SELECT SYS_GUID() FROM SYS.DUAL

    装载SQL Tuning Sets中的语句的执行计划

    SYS@ENMOEDU> DECLARE
      2    my_plans PLS_INTEGER;
      3  BEGIN
      4    my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'my_sql_tuning_set');
      5  END;
      6  /

    PL/SQL PROCEDURE successfully completed.

    查看SQL Plan 基线

    SYS@ENMOEDU> SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED FROM   DBA_SQL_PLAN_BASELINES ORDER BY 1;

    SQL_HANDLE                  PLAN_NAME                     ENA ACC FIX
    -------------------------- ------------------------------ --- --- ---
    SQL_0423f9c274f50401        SQL_PLAN_088zts9uga1011ce50939 YES YES NO
    SQL_057a0e6722dc9052        SQL_PLAN_0ayhfcwjdt42kbc5c3b2f YES YES NO
    SQL_087cde3f0085a7ed        SQL_PLAN_0hz6y7w08b9zd950e283d YES YES NO
    ………………………………………………………………………………………………………………………………
    84 ROWS selected.

    显示执行某个PLAN_NAME的执行计划

    SYS@ENMOEDU> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(plan_name => 'SQL_PLAN_5k80rgjtc9gpa044e61bd'));

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL handle: SQL_5920177c72c4beaa
    SQL text: SELECT /*+ ORDERED PUSH_SUBQ */ DISTINCT D.PROFILE_NAME, D.DEVICE_NAME,
              D.TYPE, D.STATUS, D.PROGRAM, CFG.OWNER, CFG.RULE_NAME, DECODE(D.TYPE,
              :B29 ,D.PROFILE_NAME, :B28 ,D.PROFILE_NAME, CFG.OWNER) CHECKUSER FROM
              (SELECT RULE_NAME, OWNER FROM MGMT_NOTIFY_RULE_CONFIGS C WHERE ((:B26
              LIKE C.TARGET_TYPE ESCAPE :B21 AND :B25 LIKE C.TARGET_NAME ESCAPE :B21
              ) OR (C.TARGET_GUID IS NOT NULL AND EXISTS (SELECT 1 FROM
              MGMT_FLAT_TARGET_ASSOC A, MGMT_TARGETS T WHERE A.SOURCE_TARGET_GUID =
              C.TARGET_GUID AND A.IS_MEMBERSHIP = 1 AND A.ASSOC_TARGET_GUID =
              T.TARGET_GUID AND T.TARGET_TYPE = C.TARGET_TYPE AND T.TARGET_NAME =
              :B25 ))) AND :B24 LIKE C.METRIC_NAME ESCAPE :B21 AND :B23 LIKE
              C.METRIC_COLUMN ESCAPE :B21 AND ((:B22 LIKE C.KEY_VALUE ESCAPE :B21 )
              OR (C.KEY_VALUE <> ' ' AND EXISTS (SELECT 1 FROM
              MGMT_METRICS_COMPOSITE_KEYS K WHERE K.COMPOSITE_KEY = :B22 AND
              NVL(K.KEY_PART1_VALUE, ' ') LIKE C.KEY_PART_1 ESCAPE :B21 AND
              NVL(K.KEY_PART2_VALUE, ' ') LIKE C.KEY_PART_2 ESCAPE :B21 AND
              NVL(K.KEY_PART3_VALUE, ' ') LIKE C.KEY_PART_3 ESCAPE :B21 AND
              NVL(K.KEY_PART4_VALUE, ' ') LIKE C.KEY_PART_4 ESCAPE :B21 AND
              NVL(K.KEY_PART5_VALUE, ' ') LIKE C.KEY_PART_5 ESCAPE :B21 AND
              K.TARGET_GUID = :B27 ))) AND ((:B4 = -1 AND ( (:B2 = :B20 AND
              WANT_TARGET_UNREACHABLE_START = 1) OR (:B2 = :B19 AND
              WANT_TARGET_UNREACHABLE_END = 1) OR (:B2 = :B18 AND
              WANT_TARGET_BLACKOUT_START = 1) OR (:B2 = :B17 AND
              WANT_TARGET_BLACKOUT_END = 1) OR (:B2 = :B16 AND
              ((WANT_TARGET_METRIC_ERR_START = 1 AND :B12 = 1) OR
              (WANT_TARGET_METRIC_ERR_START = 2 AND :B12 = 0) OR
              (WANT_TARGET_METRIC_ERR_START = 3))) OR (:B2 = :B15 AND
              ((WANT_TARGET_METRIC_ERR_END = 1 AND :B12 = 1) OR
              (WANT_TARGET_METRIC_ERR_END = 2 AND :B12 = 0) OR
              (WANT_TARGET_METRIC_ERR_END = 3))) OR (:B2 = :B1 AND WANT_CLEARS = 1
              AND :B14 <> 1) OR (:B2 = :B1 AND WANT_TARGET_UP = 1 AND :B14 = 1) OR
              (:B2 = :B6 AND WANT_WARNINGS = 1) OR (:B2 = :B5 AND
              WANT_CRITICAL_ALERTS = 1 AND :B14 <> 1) OR (:B2 = :B5 AND
              WANT_TARGET_DOWN = 1 AND :B14 = 1 AND :B13 = 0) OR (:B2 = :B5 AND
              WANT_TARGET_DOWN = 1 AND :B14 = 1 AND :B13 = 1 AND :B12 = 1 AND
              IGNORE_RCA = 1 AND :B11 NOT IN (:B10 , :B9 )) OR (:B2 = :B5 AND
              WANT_TARGET_DOWN = 1 AND :B14 = 1 AND :B13 = 1 AND :B12 = 1 AND
              IGNORE_RCA = 0 AND :B11 IN (:B10 , :B9 )) OR ((:B2 = :B8 OR :B2 = :B6
              OR :B2 = :B5 ) AND WANT_POLICY_VIOLATIONS = 1 ) OR (:B2 = :B1 AND
              WANT_POLICY_CLEARS = 1 ) ) ) OR (:B4 <> -1 AND ( (:B4 = :B7 AND :B2 =
              :B6 AND WANT_WARNING_JOB_SUCCEEDED = 1) OR (:B4 = :B7 AND :B2 = :B5 AND
              WANT_CRITICAL_JOB_SUCCEEDED = 1) OR (:B4 = :B7 AND :B2 <> :B1 AND
              WANT_POLICY_JOB_SUCCEEDED = 1) OR (:B4 = :B3 AND :B2 = :B6 AND
              WANT_WARNING_JOB_PROBLEMS = 1) OR (:B4 = :B3 AND :B2 = :B5 AND
              WANT_CRITICAL_JOB_PROBLEMS = 1) OR (:B4 = :B3 AND :B2 <> :B1 AND
              WANT_POLICY_JOB_PROBLEMS = 1) ) ) )) CFG, MGMT_NOTIFY_NOTIFYEES N,
              MGMT_NOTIFY_DEVICES D WHERE N.RULE_NAME = CFG.RULE_NAME AND N.OWNER =
              CFG.OWNER AND ((D.DEVICE_NAME = N.DEVICE_NAME AND D.PROFILE_NAME =
              N.PROFILE_NAME) OR (N.DEVICE_NAME = ' ' AND D.PROFILE_NAME =
              N.PROFILE_NAME AND D.TYPE IN (:B29 , :B28 ) ) ) ORDER BY CHECKUSER
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    PLAN name: SQL_PLAN_5k80rgjtc9gpa044e61bd         PLAN id: 72245693
    Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
    --------------------------------------------------------------------------------
    PLAN hash VALUE: 1224314653
    ---------------------------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name                         | ROWS  | Bytes | COST (%CPU)| TIME     |
    ---------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |                              |     1 |   992 |     9  (34)| 00:00:01 |
    |   1 |  SORT UNIQUE                   |                              |     1 |   992 |     8  (25)| 00:00:01 |
    |*  2 |   FILTER                       |                              |       |       |            |          |
    |   3 |    NESTED LOOPS                |                              |     1 |   992 |     7  (15)| 00:00:01 |
    |*  4 |     HASH JOIN                  |                              |     1 |   510 |     4  (25)| 00:00:01 |
    |*  5 |      TABLE ACCESS FULL         | MGMT_NOTIFY_RULE_CONFIGS     |     1 |   148 |     3   (0)| 00:00:01 |
    |   6 |      INDEX FULL SCAN           | MGMT_NOTIFY_NOTIFYEES_PK     |     1 |   362 |     0   (0)| 00:00:01 |
    |*  7 |     TABLE ACCESS FULL          | MGMT_NOTIFY_DEVICES          |     1 |   482 |     3   (0)| 00:00:01 |
    |   8 |    NESTED LOOPS                |                              |     1 |    80 |     1   (0)| 00:00:01 |
    |   9 |     TABLE ACCESS BY INDEX ROWID| MGMT_TARGETS                 |     1 |    47 |     1   (0)| 00:00:01 |
    |* 10 |      INDEX UNIQUE SCAN         | MGMT_TARGETS_PK              |     1 |       |     0   (0)| 00:00:01 |
    |* 11 |     TABLE ACCESS BY INDEX ROWID| MGMT_FLAT_TARGET_ASSOC       |     1 |    33 |     0   (0)| 00:00:01 |
    |* 12 |      INDEX RANGE SCAN          | MGMT_FLAT_TARGET_ASSOC_IDX01 |     1 |       |     0   (0)| 00:00:01 |
    |* 13 |    TABLE ACCESS FULL           | MGMT_METRICS_COMPOSITE_KEYS  |     1 |    82 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       2 - filter((:B26 LIKE "C"."TARGET_TYPE" ESCAPE :B21 AND :B25 LIKE "C"."TARGET_NAME" ESCAPE :B21 OR
                  "C"."TARGET_GUID" IS NOT NULL AND  EXISTS (SELECT /*+ LEADING ("T" "A") INDEX_RS_ASC ("T"
                  "MGMT_TARGETS_PK") USE_NL ("A") INDEX_RS_ASC ("A" "MGMT_FLAT_TARGET_ASSOC_IDX01") */     0 FROM
                  "MGMT_TARGETS" "T","MGMT_FLAT_TARGET_ASSOC" "A" WHERE "A"."IS_MEMBERSHIP"=1 AND
                  "A"."SOURCE_TARGET_GUID"=:B1 AND "A"."ASSOC_TARGET_GUID"="T"."TARGET_GUID" AND "T"."TARGET_NAME"=:B25
                  AND "T"."TARGET_TYPE"=:B2)) AND (:B22 LIKE "C"."KEY_VALUE" ESCAPE :B21 OR "C"."KEY_VALUE"<>' ' AND
                  EXISTS (SELECT /*+ FULL ("K") */ 0 FROM "MGMT_METRICS_COMPOSITE_KEYS" "K" WHERE
                  RAWTOHEX("K"."COMPOSITE_KEY")=:B22 AND RAWTOHEX("K"."TARGET_GUID")=:B27 AND
                  NVL("K"."KEY_PART1_VALUE",' ') LIKE :B3 ESCAPE :B21 AND NVL("K"."KEY_PART2_VALUE",' ') LIKE :B4 ESCAPE
                  :B21 AND NVL("K"."KEY_PART3_VALUE",' ') LIKE :B5 ESCAPE :B21 AND NVL("K"."KEY_PART4_VALUE",' ') LIKE
                  :B6 ESCAPE :B21 AND NVL("K"."KEY_PART5_VALUE",' ') LIKE :B7 ESCAPE :B21)))
       4 - ACCESS("N"."RULE_NAME"="RULE_NAME" AND "N"."OWNER"="OWNER")
       5 - filter(:B24 LIKE "C"."METRIC_NAME" ESCAPE :B21 AND :B23 LIKE "C"."METRIC_COLUMN" ESCAPE :B21
                  AND (TO_NUMBER(:B4)=(-1) AND (:B2=:B20 AND "WANT_TARGET_UNREACHABLE_START"=1 OR :B2=:B19 AND
                  "WANT_TARGET_UNREACHABLE_END"=1 OR "WANT_POLICY_VIOLATIONS"=1 AND (:B2=:B8 OR :B2=:B6 OR :B2=:B5) OR
                  :B2=:B5 AND "WANT_CRITICAL_ALERTS"=1 AND TO_NUMBER(:B14)<>1 OR :B2=:B18 AND
                  "WANT_TARGET_BLACKOUT_START"=1 OR :B2=:B17 AND "WANT_TARGET_BLACKOUT_END"=1 OR :B2=:B6 AND
                  "WANT_WARNINGS"=1 OR :B2=:B1 AND "WANT_POLICY_CLEARS"=1 OR :B2=:B16 AND
                  ("WANT_TARGET_METRIC_ERR_START"=3 OR "WANT_TARGET_METRIC_ERR_START"=1 AND TO_NUMBER(:B12)=1 OR
                  "WANT_TARGET_METRIC_ERR_START"=2 AND TO_NUMBER(:B12)=0) OR :B2=:B15 AND
                  ("WANT_TARGET_METRIC_ERR_END"=3 OR "WANT_TARGET_METRIC_ERR_END"=1 AND TO_NUMBER(:B12)=1 OR
                  "WANT_TARGET_METRIC_ERR_END"=2 AND TO_NUMBER(:B12)=0) OR :B2=:B1 AND "WANT_CLEARS"=1 AND
                  TO_NUMBER(:B14)<>1 OR :B2=:B1 AND "WANT_TARGET_UP"=1 AND TO_NUMBER(:B14)=1 OR :B2=:B5 AND
                  "WANT_TARGET_DOWN"=1 AND TO_NUMBER(:B14)=1 AND TO_NUMBER(:B13)=0 OR :B2=:B5 AND "WANT_TARGET_DOWN"=1
                  AND (:B11=:B10 OR :B11=:B9) AND TO_NUMBER(:B14)=1 AND TO_NUMBER(:B13)=1 AND TO_NUMBER(:B12)=1 AND
                  "IGNORE_RCA"=0 OR :B2=:B5 AND "IGNORE_RCA"=1 AND :B11<>:B10 AND :B11<>:B9 AND "WANT_TARGET_DOWN"=1 AND
                  TO_NUMBER(:B14)=1 AND TO_NUMBER(:B13)=1 AND TO_NUMBER(:B12)=1) OR TO_NUMBER(:B4)<>(-1) AND (:B4=:B7
                  AND "WANT_POLICY_JOB_SUCCEEDED"=1 AND :B2<>:B1 OR :B4=:B3 AND "WANT_POLICY_JOB_PROBLEMS"=1 AND
                  :B2<>:B1 OR :B4=:B7 AND :B2=:B6 AND "WANT_WARNING_JOB_SUCCEEDED"=1 OR :B4=:B7 AND :B2=:B5 AND
                  "WANT_CRITICAL_JOB_SUCCEEDED"=1 OR :B4=:B3 AND :B2=:B6 AND "WANT_WARNING_JOB_PROBLEMS"=1 OR :B4=:B3
                  AND :B2=:B5 AND "WANT_CRITICAL_JOB_PROBLEMS"=1)))
       7 - filter("D"."DEVICE_NAME"="N"."DEVICE_NAME" AND "D"."PROFILE_NAME"="N"."PROFILE_NAME" OR
                  "N"."DEVICE_NAME"=' ' AND "D"."PROFILE_NAME"="N"."PROFILE_NAME" AND ("D"."TYPE"=TO_NUMBER(:B29) OR
                  "D"."TYPE"=TO_NUMBER(:B28)))
      10 - ACCESS("T"."TARGET_TYPE"=:B1 AND "T"."TARGET_NAME"=:B25)
      11 - filter("A"."ASSOC_TARGET_GUID"="T"."TARGET_GUID")
      12 - ACCESS("A"."SOURCE_TARGET_GUID"=:B1 AND "A"."IS_MEMBERSHIP"=1)
      13 - filter(RAWTOHEX("K"."COMPOSITE_KEY")=:B22 AND RAWTOHEX("K"."TARGET_GUID")=:B27 AND
                  NVL("K"."KEY_PART1_VALUE",' ') LIKE :B1 ESCAPE :B21 AND NVL("K"."KEY_PART2_VALUE",' ') LIKE :B2 ESCAPE
                  :B21 AND NVL("K"."KEY_PART3_VALUE",' ') LIKE :B3 ESCAPE :B21 AND NVL("K"."KEY_PART4_VALUE",' ') LIKE
                  :B4 ESCAPE :B21 AND NVL("K"."KEY_PART5_VALUE",' ') LIKE :B5 ESCAPE :B21)

    124 ROWS selected.

    【小结】
    通过Performance Tuning Guideà15 Using SQL Plan Managementà15.6.1中参数space_budget_percent的理解，可以知道这个参数是设置最大的在SYSAUX空间上的百分比，并且如果超过空间阈值则每周会告警,所以选择D
    OCP_1z0-053_QUESTION4

    Filed Under: 1z0-053, OCP, Sandataedu

    Speak Your Mind

    *

    京ICP备14059771号-2