1 PACKAGE dbms_optim_bundle AUTHID CURRENT_USER AS
2
3 ---------------------------------------------------------------------------
4 --
5 -- PACKAGE NOTES
6 -- This package is created to manage (enable/disable) optimizer fixes
7 -- provided as part of PSU/bundle. By optimizer fixes, we mean, any fix
8 -- provided as part of bundle which has a fix_control and can possibly
9 -- cause a plan change.
10 --
11 -- SECURITY
12 -- This package is only accessible to user SYS by default. You can control
13 -- access to these routines by only granting execute to privileged users.
14 -- Access to routines in this package should be exposed at pl/sql level
15 -- very carefully.
16
17
18 ----------------------------
19 -- PROCEDURES AND FUNCTIONS
20 ----------------------------
21 --
22 -- USAGE:
23 -- User can invoke below procedure to enable/disable optimizer related fixes
24 -- enable_optim_fixes(action IN VARCHAR2,
25 -- scope IN VARCHAR2,
26 -- current_setting_precedence IN VARCHAR2)
27 -- Input arguments:
28 -- action ( IN )
29 -- to enable/disable optimizer fixes
30 -- Acceptable values are 'ON'/'OFF'
31 -- Default is 'OFF'
32 -- scope ( IN )
33 -- scope of enabling/disabling the fixes
34 -- Acceptable values are MEMORY/SPFILE/BOTH/INITORA
35 -- MEMORY/SPFILE/BOTH: These three input values when used will
36 -- enable/disable fixes in given scope
37 -- INITORA: This input value will just display the content to be
38 -- added to init.ora to manage the fixes.
39 -- 'current_setting_precedence' has no signifincance when scope=INITORA
40 -- Default is 'MEMORY'
41 -- current_setting_precedence (IN)
42 -- precedence of current setting over bundle's setting
43 -- Acceptable values are YES/NO
44 -- YES: current env settings take precedence in case of conflict
45 -- NO: bundle settings take precedence in case of conflict
46 -- It does not hold any significance when SCOPE=INITORA
47 -- Default is 'YES'
48 -- Input values are case in-sensitive.
49 --
50 -- USAGE EXAMPLE:
51 -- 1. exec dbms_optim_bundle.enable_optim_fixes('ON','MEMORY', 'NO');
52 -- This will switch ON all optimizer fixes in memory which are present
53 -- in bundle without considering their current env setting as
54 -- current_setting_precedence is set to 'NO'
55 --
56 -- 2. exec dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES');
57 -- In case of non-conflicting values, this will switch ON all bundle related
58 -- optimizer fixes in MEMORY and SPFILE both.
59 -- In case of conflict between bundle and current env setting, current
60 -- env will be given precedence.
61 -- It will also retain any additional setting already present in spfile.
62 --
63 -- 3. exec dbms_optim_bundle.enable_optim_fixes;
64 -- This will take default input values and will work accordingly.
65 --
66 -- 4. exec dbms_optim_bundle.enable_optim_fixes('ON', 'INITORA');
67 -- This will display the command to be added to init.ora file.
68 -- It will not execute anything. It will just display what could
69 -- be added to init.ora file to make it work.
70 --
71 -- ERRORS:
72 -- Application errors used are:
73 -- -20001 user-supplied value error
74 -- -20002 internal/other errors
75
76 PROCEDURE enable_optim_fixes(action IN VARCHAR2 default 'OFF',
77 scope IN VARCHAR2 default 'MEMORY',
78 current_setting_precedence IN VARCHAR2 default 'YES');
79
80 -- USAGE:
81 -- User can invoke below procedure to display opimizer bug#s applied
82 -- as part of given PSU/bundle
83 --
84 -- getBugsforBundle(bundle IN NUMBER);
85 --
86 -- Input arguments:
87 -- bundle ( IN ): bundle Id
88 -- Possible Input Values:
89 -- 1...N: Display optimizer Bug#s for all bundles upto the specified Bundle
90 -- NULL: Display optimizer Bug#s for default bundle i.e. latest bundle
91 --
92 -- USAGE EXAMPLE:
93 -- 1. exec dbms_optim_bundle.getBugsforBundle(9);
94 -- This will display bug#s for all bundles upto bundle 9
95 --
96 -- 2. exec dbms_optim_bundle.getBugsforBundle;
97 -- This will display bug#s for latest applied bundle
98 --
99 -- ERRORS:
100 -- Application errors used are:
101 -- -20001 user-supplied value error
102 -- -20002 internal/other errors
103
104 PROCEDURE getBugsforBundle(bundleId IN NUMBER default NULL);
105 -- USAGE:
106 -- User can invoke below procedure to display bundle-ids and bundle-names
107 -- which have fixes with _fix_controls i.e. fixes which may cause plan change.
108 --
109 -- listBundlesWithFCFixes
110 --
111 -- Input arguments: None
112 --
113 -- USAGE EXAMPLE:
114 -- 1. exec dbms_optim_bundle.listBundlesWithFCFixes;
115 -- This will display all bundle-ids and names which have
116 -- fixes with _fix_controls.
117 --
118 -- ERRORS:
119 -- Application errors used are:
120 -- -20001 user-supplied value error
121 -- -20002 internal/other errors
122 PROCEDURE listBundlesWithFCFixes;
123
124 END dbms_optim_bundle;
[Home] [Help]