【题目示意】
此题考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_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
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.
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
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.
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.
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_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.
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.
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.
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
相关参考
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_spm.htm#CACHJJJA
http://docs.oracle.com/cd/E11882_01/server.112/e41573/optplanmgmt.htm#PFGRF007
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_spm.htm#CACHJJJA
http://docs.oracle.com/cd/E11882_01/server.112/e41573/optplanmgmt.htm#PFGRF007
Speak Your Mind