1 PROCEDURE dbms_feature_emx
2 ( feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 -- total em express usage count since last feature usage collection
7 l_count_total_delta number := 0;
8
9 -- total em express usage count since the first time it's used
10 l_count_total number := 0;
11
12 -- feature_info clob
13 l_detailed_usage_clob CLOB := NULL;
14
15 -- feature_info xml
16 l_detailed_usage_xml xmltype := NULL;
17
18 -- new xml to add to feature_info xml for one report
19 l_report_usage_xml xmltype := NULL;
20
21 -- xpath key to find out if report already has entry in feature_info xml
22 l_report_usage_key varchar2(32767);
23
24 -- report name
25 l_report_name varchar2(32767) := NULL;
26
27 -- statistics of existing entry in feature_info xml for report
28 l_old_report_count number;
29 l_old_report_avg_time number;
30 l_old_report_avg_cputime number;
31
32 -- new statistics to put into feature_info xml for report
33 l_new_report_count number:= NULL;
34 l_new_report_total_time number:= NULL;
35 l_new_report_avg_time number:= NULL;
36 l_new_report_total_cputime number:= NULL;
37 l_new_report_avg_cputime number:= NULL;
38
39 -- last db feature usage sample collection time
40 l_last_collection_time date;
41
42 -- Query to select the delta since last feature usage collection
43 -- from internal fixed table X$KEXSVFU.
44 -- Note if count for the report is 0, but last request timestamp is greater
45 -- than or equal to the latest sample date of db feature usage framework,
46 -- then this report must have been used at least once, therefore decoding
47 -- count from 0 to 1.
48 -- If in CDB, returned result is for this container only
49 cursor emx_fu_cursor(p_last_collection_time date) is
50 select report,
51 sum(total_count) as total_count,
52 sum(total_elapsed_time) as total_elapsed_time,
53 sum(total_cpu_time) as total_cpu_time
54 from table(gv$(cursor(
55 select report_kexsvfu as report,
56 decode(count_kexsvfu, 0, 1, count_kexsvfu) as total_count,
57 elapsed_time_kexsvfu as total_elapsed_time,
58 cpu_time_kexsvfu as total_cpu_time
59 from X$KEXSVFU
60 where last_req_time_kexsvfu >= p_last_collection_time
61 and con_id = sys_context('userenv', 'con_id'))))
62 group by report;
63
64 begin
65
66 -- initialize output parameters
67 feature_boolean := 0;
68 aux_count := 0;
69 feature_info := NULL;
70
71 -- get total em express usage count from aux_count column and
72 -- detailed em express usage info from feature_info column before
73 -- last usage collection
74 begin
75 select nvl(aux_count, 0), feature_info
76 into l_count_total, l_detailed_usage_clob
77 from dba_feature_usage_statistics
78 where name = 'EM Express';
79 exception
80 when NO_DATA_FOUND then
81 l_count_total := 0;
82 l_detailed_usage_clob := NULL;
83 end;
84
85 -- if no feature_info xml exists, construct a brand new one
86 if (l_detailed_usage_clob is NULL) then
87 l_detailed_usage_xml :=
88 xmltype('<emx_usage time_unit="us"></emx_usage>');
89 -- otherwise update the existing one
90 else
91 l_detailed_usage_xml := xmltype(l_detailed_usage_clob);
92 end if;
93
94 -- get last db feature usage collection time
95 select nvl(max(last_sample_date), sysdate-7)
96 into l_last_collection_time
97 from dba_feature_usage_statistics;
98
99 -- get report usage info since last feature usage collection
100 for rc in emx_fu_cursor(l_last_collection_time)
101 loop
102 l_report_name := rc.report;
103 l_new_report_count := rc.total_count;
104 l_new_report_total_time := rc.total_elapsed_time;
105 l_new_report_total_cputime := rc.total_cpu_time;
106
107 -- update total count for all EM Express reports since last usage
108 -- collection, this will indicate if EM Express has been used since last
109 -- collection, and be added to aux_count column
110 l_count_total_delta := l_count_total_delta + l_new_report_count;
111
112 --
113 -- update the feature_info detail xml
114 --
115
116 -- build the xpath key to find out if the report already exists
117 -- in the xml. The key looks like:
118 -- '//report_usage[@report="<report_id>"]'
119 l_report_usage_key := '//report_usage[' ||
120 '@report="' || l_report_name || '"' ||
121 ']';
122
123 -- find out if an xml element for this report already exists in the xml
124 if (l_detailed_usage_xml.existsNode(l_report_usage_key) > 0) then
125
126 -- get the old count for this report
127 -- if any of the attributes is not found for this report,
128 -- reset it to 0
129 select NVL(EXTRACTVALUE(l_detailed_usage_xml,
130 l_report_usage_key || '//@count'), 0),
131 NVL(EXTRACTVALUE(l_detailed_usage_xml,
132 l_report_usage_key || '//@avg_elapsed_time'), 0),
133 NVL(EXTRACTVALUE(l_detailed_usage_xml,
134 l_report_usage_key || '//@avg_cpu_time'), 0)
135 into l_old_report_count,
136 l_old_report_avg_time,
137 l_old_report_avg_cputime
138 from dual;
139
140 -- update the statistics, increment count and total time with stats
141 -- since the last usage collection
142 l_new_report_count
143 := l_old_report_count + l_new_report_count;
144
145 -- recalculate average time
146 l_new_report_avg_time
147 := round((l_old_report_avg_time * l_old_report_count
148 + l_new_report_total_time) / l_new_report_count, 1);
149
150 l_new_report_avg_cputime
151 := round((l_old_report_avg_cputime * l_old_report_count
152 + l_new_report_total_cputime) / l_new_report_count, 1);
153
154 -- update the xml using the new stats
155 select updateXML(l_detailed_usage_xml,
156 l_report_usage_key || '//@count',
157 l_new_report_count,
158 l_report_usage_key || '//@avg_elapsed_time',
159 l_new_report_avg_time,
160 l_report_usage_key || '//@avg_cpu_time',
161 l_new_report_avg_cputime)
162 into l_detailed_usage_xml
163 from dual;
164
165 -- if no xml element is found for this report, construct a new one
166 else
167 -- calculate average time
168 l_new_report_avg_time
169 := round(l_new_report_total_time / l_new_report_count, 1);
170 l_new_report_avg_cputime
171 := round(l_new_report_total_cputime / l_new_report_count, 1);
172
173 -- construct new xml element for this report usage
174 select xmlelement("report_usage",
175 xmlattributes(
176 l_report_name as "report",
177 l_new_report_count as "count",
178 l_new_report_avg_time as "avg_elapsed_time",
179 l_new_report_avg_cputime as "avg_cpu_time"))
180 into l_report_usage_xml
181 from dual;
182
183 -- append this report usage to the main emx feature usage xml
184 l_detailed_usage_xml :=
185 l_detailed_usage_xml.appendChildxml('/*', l_report_usage_xml);
186
187 end if;
188
189 end loop;
190
191 -- update feature_boolean to indicate if em express has been used or not
192 -- by setting it to the total count since last usage collection
193 feature_boolean := l_count_total_delta;
194
195 -- update total count in aux_count column
196 aux_count := l_count_total + l_count_total_delta;
197
198 -- update feature_info for the new collection
199 feature_info := l_detailed_usage_xml.getClobVal();
200
201 end dbms_feature_emx;
[Home] [Help]