[Home] [Help]
Skip to content
PROCEDURE: SYS.DBMS_FEATURE_REGISTER_ALLHWM
Source
1 procedure DBMS_FEATURE_REGISTER_ALLHWM
2 as
3 begin
4
5 /**************************
6 * User Tables
7 **************************/
8
9 declare
10 HWM_USER_TABLES_STR CONSTANT VARCHAR2(1000) :=
11 'select count(*) from sys.tab$ t, sys.obj$ o ' ||
12 'where t.obj# = o.obj# ' ||
13 'and bitand(t.property, 1) = 0 ' ||
14 'and bitand(o.flags, 128) = 0 ' ||
15 'and o.owner# not in (select u.user# from user$ u ' ||
16 'where u.name in (''SYS'', ''SYSTEM''))';
17
18 begin
19 dbms_feature_usage.register_high_water_mark
20 ('USER_TABLES',
21 dbms_feature_usage.DBU_HWM_BY_SQL,
22 HWM_USER_TABLES_STR,
23 'Number of User Tables');
24 end;
25
26 /**************************
27 * Segment Size
28 **************************/
29
30 declare
31 HWM_SEG_SIZE_STR CONSTANT VARCHAR2(1000) :=
32 'select max(bytes) from dba_segments';
33
34 begin
35 dbms_feature_usage.register_high_water_mark
36 ('SEGMENT_SIZE',
37 dbms_feature_usage.DBU_HWM_BY_SQL,
38 HWM_SEG_SIZE_STR,
39 'Size of Largest Segment (Bytes)');
40 end;
41
42 /**************************
43 * Partition Tables
44 **************************/
45
46 declare
47 HWM_PART_TABLES_STR CONSTANT VARCHAR2(1000) :=
48 'select nvl(max(p.partcnt), 0) from sys.partobj$ p, sys.obj$ o ' ||
49 'where p.obj# = o.obj# ' ||
50 'and o.type# = 2 ' ||
51 'and o.owner# not in (select u.user# from user$ u ' ||
52 'where u.name in (''SYS'', ''SYSTEM'', ''SH''))';
53
54 begin
55 dbms_feature_usage.register_high_water_mark
56 ('PART_TABLES',
57 dbms_feature_usage.DBU_HWM_BY_SQL,
58 HWM_PART_TABLES_STR,
59 'Maximum Number of Partitions belonging to an User Table');
60 end;
61
62 /**************************
63 * Partition Indexes
64 **************************/
65
66 declare
67 HWM_PART_INDEXES_STR CONSTANT VARCHAR2(1000) :=
68 'select nvl(max(p.partcnt), 0) from sys.partobj$ p, sys.obj$ o ' ||
69 'where p.obj# = o.obj# ' ||
70 'and o.type# = 1 ' ||
71 'and o.owner# not in (select u.user# from user$ u ' ||
72 'where u.name in (''SYS'', ''SYSTEM'', ''SH''))';
73
74 begin
75 dbms_feature_usage.register_high_water_mark
76 ('PART_INDEXES',
77 dbms_feature_usage.DBU_HWM_BY_SQL,
78 HWM_PART_INDEXES_STR,
79 'Maximum Number of Partitions belonging to an User Index');
80 end;
81
82 /**************************
83 * User Indexes
84 **************************/
85
86 declare
87 HWM_USER_INDEX_STR CONSTANT VARCHAR2(1000) :=
88 'select count(*) from sys.ind$ i, sys.obj$ o ' ||
89 'where i.obj# = o.obj# ' ||
90 'and bitand(i.flags, 4096) = 0 ' ||
91 'and o.owner# not in (select u.user# from user$ u ' ||
92 'where u.name in (''SYS'', ''SYSTEM''))';
93
94 begin
95 dbms_feature_usage.register_high_water_mark
96 ('USER_INDEXES',
97 dbms_feature_usage.DBU_HWM_BY_SQL,
98 HWM_USER_INDEX_STR,
99 'Number of User Indexes');
100 end;
101
102 /**************************
103 * Sessions
104 **************************/
105
106 declare
107 HWM_SESSIONS_STR CONSTANT VARCHAR2(1000) :=
108 'select sessions_highwater from V$LICENSE';
109
110 begin
111 dbms_feature_usage.register_high_water_mark
112 ('SESSIONS',
113 dbms_feature_usage.DBU_HWM_BY_SQL,
114 HWM_SESSIONS_STR,
115 'Maximum Number of Concurrent Sessions seen in the database');
116 end;
117
118 /**************************
119 * DB Size
120 **************************/
121
122 declare
123 HWM_DB_SIZE_STR CONSTANT VARCHAR2(1000) :=
124 'select sum(bytes) from dba_data_files';
125
126 begin
127 dbms_feature_usage.register_high_water_mark
128 ('DB_SIZE',
129 dbms_feature_usage.DBU_HWM_BY_SQL,
130 HWM_DB_SIZE_STR,
131 'Maximum Size of the Database (Bytes)');
132 end;
133
134 /**************************
135 * Datafiles
136 **************************/
137
138 declare
139 HWM_DATAFILES_STR CONSTANT VARCHAR2(1000) :=
140 'select count(*) from dba_data_files';
141
142 begin
143 dbms_feature_usage.register_high_water_mark
144 ('DATAFILES',
145 dbms_feature_usage.DBU_HWM_BY_SQL,
146 HWM_DATAFILES_STR,
147 'Maximum Number of Datafiles');
148 end;
149
150 /**************************
151 * Tablespaces
152 **************************/
153
154 declare
155 HWM_TABLESPACES_STR CONSTANT VARCHAR2(1000) :=
156 'select count(*) from sys.ts$ ts ' ||
157 'where ts.online$ != 3 ' ||
158 'and bitand(ts.flags, 2048) != 2048';
159
160 begin
161 dbms_feature_usage.register_high_water_mark
162 ('TABLESPACES',
163 dbms_feature_usage.DBU_HWM_BY_SQL,
164 HWM_TABLESPACES_STR,
165 'Maximum Number of Tablespaces');
166 end;
167
168 /**************************
169 * CPU count
170 **************************/
171
172 declare
173 HWM_CPU_COUNT_STR CONSTANT VARCHAR2(1000) :=
174 'select sum(cpu_count_highwater) from gv$license';
175
176 begin
177 dbms_feature_usage.register_high_water_mark
178 ('CPU_COUNT',
179 dbms_feature_usage.DBU_HWM_BY_SQL,
180 HWM_CPU_COUNT_STR,
181 'Maximum Number of CPUs');
182 end;
183
184 /**************************
185 * Query Length
186 **************************/
187
188 declare
189 HWM_QUERY_LENGTH_STR CONSTANT VARCHAR2(1000) :=
190 'select max(maxquerylen) from v$undostat';
191
192 begin
193 dbms_feature_usage.register_high_water_mark
194 ('QUERY_LENGTH',
195 dbms_feature_usage.DBU_HWM_BY_SQL,
196 HWM_QUERY_LENGTH_STR,
197 'Maximum Query Length');
198 end;
199
200 /******************************
201 * National Character Set Usage
202 *******************************/
203
204 declare
205 HWM_NCHAR_COLUMNS_STR CONSTANT VARCHAR2(1000) :=
206 'select count(*) from col$ c, obj$ o ' ||
207 ' where c.charsetform = 2 and c.obj# = o.obj# ' ||
208 ' and o.owner# not in ' ||
209 ' (select distinct u.user_id from all_users u, ' ||
210 ' sys.ku_noexp_view k where (k.OBJ_TYPE=''USER'' and ' ||
211 ' k.name=u.username) or (u.username=''SYSTEM'')) ' ;
212
213 begin
214 dbms_feature_usage.register_high_water_mark
215 ('SQL_NCHAR_COLUMNS',
216 dbms_feature_usage.DBU_HWM_BY_SQL,
217 HWM_NCHAR_COLUMNS_STR,
218 'Maximum Number of SQL NCHAR Columns');
219 end;
220
221 /********************************
222 * Instances
223 *********************************/
224 declare
225 HWM_INSTANCES_STR CONSTANT VARCHAR2(1000) :=
226 'SELECT count(*) FROM gv$instance';
227 begin
228 dbms_feature_usage.register_high_water_mark
229 ('INSTANCES',
230 dbms_feature_usage.DBU_HWM_BY_SQL,
231 HWM_INSTANCES_STR,
232 'Oracle Database instances');
233 end;
234
235 /****************************
236 * Materialized Views (User)
237 ****************************/
238
239 declare
240 HWM_USER_MV_STR CONSTANT VARCHAR2(1000) :=
241 'select count(*) from dba_mviews ' ||
242 'where owner not in (''SYS'',''SYSTEM'', ''SH'')';
243
244 begin
245 dbms_feature_usage.register_high_water_mark
246 ('USER_MV',
247 dbms_feature_usage.DBU_HWM_BY_SQL,
248 HWM_USER_MV_STR,
249 'Maximum Number of Materialized Views (User)');
250 end;
251
252
253 /*******************
254 * Active Sessions
255 *******************/
256
257 declare
258 HWM_ACTIVE_SESSIONS_STR CONSTANT VARCHAR2(1000) :=
259 'select max(value) from v$sysmetric_history ' ||
260 'where metric_name = ''Average Active Sessions''';
261
262 begin
263 dbms_feature_usage.register_high_water_mark
264 ('ACTIVE_SESSIONS',
265 dbms_feature_usage.DBU_HWM_BY_SQL,
266 HWM_ACTIVE_SESSIONS_STR,
267 'Maximum Number of Active Sessions seen in the system');
268 end;
269
270 /*******************
271 * DBMS_SCHEDULER HWM is number of jobs per day
272 *******************/
273
274 declare
275 HWM_DBMS_SCHEDULER_STR CONSTANT VARCHAR2(1000) :=
276 'select '||
277 'round(max(runs*((max_id-min_id)/2 + 1)/tot),0) '||
278 'from '||
279 '( select '||
280 'trunc(log_date) day, '||
281 'max(log_id) max_id, '||
282 'min(log_id) min_id, '||
283 'count(log_id) tot, '||
284 'count(case when operation = ''RUN'' then 1 end) runs '||
285 'from '||
286 'scheduler$_event_log '||
287 'where '||
288 'log_date > systimestamp - interval ''8'' day '||
289 'group by trunc(log_date) '||
290 ')' ;
291 begin
292 dbms_feature_usage.register_high_water_mark
293 ('HWM_DBMS_SCHEDULER',
294 dbms_feature_usage.DBU_HWM_BY_SQL,
295 HWM_DBMS_SCHEDULER_STR,
296 'Number of job runs per day');
297 end;
298
299 /*******************
300 * Exadata
301 *******************/
302
303 declare
304 HWM_EXADATA_STR CONSTANT VARCHAR2(1000) :=
305 'select replace(substr(statistics_value, 23), ''</nphysicaldisks_stats>'') from gv$cell_state where statistics_type = ''NPHYSDISKS''';
306 begin
307 dbms_feature_usage.register_high_water_mark
308 ('EXADATA_DISKS',
309 dbms_feature_usage.DBU_HWM_BY_SQL,
310 HWM_EXADATA_STR,
311 'Number of physical disks');
312 end;
313
314 /************************
315 * GSM Global services
316 ***********************/
317
318 declare
319 HWM_GSM_STR CONSTANT VARCHAR2(1000) :=
320 'select count(*) from dba_services ' ||
321 'where global_service = ''YES''';
322 begin
323 dbms_feature_usage.register_high_water_mark
324 ('GLOBAL SERVICES',
325 dbms_feature_usage.DBU_HWM_BY_SQL,
326 HWM_GSM_STR,
327 'Number of global services');
328 end;
329
330 /**************************
331 * Test HWM
332 **************************/
333
334 declare
335 HWM_TEST_PROC CONSTANT VARCHAR2(1000) :=
336 'DBMS_FEATURE_TEST_PROC_3';
337
338 begin
339 dbms_feature_usage.register_high_water_mark
340 ('_HWM_TEST_1',
341 dbms_feature_usage.DBU_HWM_BY_PROCEDURE +
342 dbms_feature_usage.DBU_HWM_TEST,
343 HWM_TEST_PROC,
344 'Test HWM 1');
345 end;
346
347 dbms_feature_usage.register_high_water_mark
348 ('_HWM_TEST_2',
349 dbms_feature_usage.DBU_HWM_NULL +
350 dbms_feature_usage.DBU_HWM_TEST,
351 'Junk',
352 'Test HWM 2');
353
354 dbms_feature_usage.register_high_water_mark
355 ('_HWM_TEST_3',
356 dbms_feature_usage.DBU_HWM_BY_SQL +
357 dbms_feature_usage.DBU_HWM_TEST,
358 'select 10 from dual',
359 'Test HWM 3');
360
361 dbms_feature_usage.register_high_water_mark
362 ('_HWM_TEST_4',
363 dbms_feature_usage.DBU_HWM_BY_SQL +
364 dbms_feature_usage.DBU_HWM_TEST,
365 'select 1240 from foo',
366 'Test HWM 4 - Error case');
367
368 end;