1 procedure DBMS_FEATURE_RESOURCE_MANAGER
2 (feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 feature_usage varchar2(1000);
7 non_maint_sql varchar2(1000);
8 non_maint_usage number;
9 non_maint_cpu number;
10 non_maint_other number;
11 begin
12
13 -- Initialize all variables
14
15 feature_boolean := 0;
16 aux_count := 0;
17 feature_info := to_clob('Resource Manager usage not detected');
18
19 feature_usage := NULL;
20 non_maint_sql := NULL;
21 non_maint_cpu := 0;
22 non_maint_other := 0;
23
24 -- 'feature_boolean' is set to 1 if Resource Manager was enabled, not
25 -- including for maintenance windows.
26
27 non_maint_sql :=
28 'select decode(count(*), 0, 0, 1) from v$rsrc_plan_history where ' ||
29 'name != ''ORA$INTERNAL_CDB_PLAN'' and ' ||
30 'name != ''INTERNAL_PLAN'' and name is not null and ' ||
31 '(name != ''DEFAULT_MAINTENANCE_PLAN'' or ' ||
32 ' (window_name is null or ' ||
33 ' (window_name != ''MONDAY_WINDOW'' and ' ||
34 ' window_name != ''TUESDAY_WINDOW'' and ' ||
35 ' window_name != ''WEDNESDAY_WINDOW'' and ' ||
36 ' window_name != ''THURSDAY_WINDOW'' and ' ||
37 ' window_name != ''FRIDAY_WINDOW'' and ' ||
38 ' window_name != ''SATURDAY_WINDOW'' and ' ||
39 ' window_name != ''SUNDAY_WINDOW''))) ';
40
41 execute immediate
42 non_maint_sql
43 into feature_boolean;
44
45 -- 'aux_count' is not being used
46
47 -- 'feature_info' is constructed of the following name-value pairs:
48 -- Non-Maintenance CPU Management:
49 -- This field is set to 1 if Resource Manager was enabled explicitly
50 -- and the Resource Plan was managing CPU.
51 -- Non-Maintenance Other Management:
52 -- This field is set to 1 if Resource Manager was enabled explicitly
53 -- and the Resource Plan was NOT managing CPU, i.e. the Resource Plan
54 -- was managing idle time, switch time, DOP, etc.
55
56 if feature_boolean > 0
57 then
58 execute immediate
59 non_maint_sql || ' and cpu_managed = ''ON'' '
60 into non_maint_cpu;
61
62 execute immediate
63 non_maint_sql || ' and cpu_managed = ''OFF'' '
64 into non_maint_other;
65
66 feature_usage :=
67 'Non-Maintenance CPU Management: ' || non_maint_cpu ||
68 ', Non-Maintenance Other Management: ' || non_maint_other;
69
70 feature_info := to_clob(feature_usage);
71 end if;
72
73 end dbms_feature_resource_manager;