1 PROCEDURE dbms_feature_job_scheduler
2 (is_used OUT number, nr_of_jobs OUT number, summary OUT clob)
3 AS
4 sum1 varchar2(4000);
5 n1 number;
6 n2 number;
7 n3 number;
8 n4 number;
9 n5 number;
10 n6 number;
11 n7 number;
12 n8 number;
13 n9 number;
14
15 BEGIN
16 select count(*) into nr_of_jobs from dba_scheduler_jobs where
17 owner not in ('SYS', 'ORACLE_OCM', 'EXFSYS' )
18 and job_name not like 'AQ$%'
19 and job_name not like 'MV_RF$J_%';
20
21 is_used := nr_of_jobs;
22 -- if job used
23 if is_used = 0 then return; end if;
24
25 select count(*) into n1 from dba_scheduler_jobs;
26 sum1 := sum1
27 || 'JNRA:' || n1
28 || ',JNRU:' || nr_of_jobs;
29
30 select count(*) into n1 from dba_jobs;
31 sum1 := sum1
32 || ',DJOBS:' || n1;
33 -- Direct per job type counts, i.e of the total number of jobs how many are
34 -- program vs executable vs plsql block vs stored procedure vs chain
35
36
37 for it in (
38 select jt t, count(*) n
39 from (select nvl(job_type, 'PROGRAM') jt
40 from dba_scheduler_jobs )
41 group by jt order by 1)
42 loop
43 sum1 := sum1 || ',JTD' || substr(it.t,1,3) || ':' || it.n;
44 end loop;
45
46
47 -- Indirect per job type counts.
48 -- In this case the you have to track down the program type of all
49 -- the jobs whose jobs are of type program.
50 -- So now of the the total number of jobs, how many are
51 -- executable vs plsql block vs stored procedure vs chain
52
53 for it in (
54 select jt t, count(*) n from
55 (select program_type jt
56 from dba_scheduler_jobs j,
57 dba_scheduler_programs p
58 where
59 job_type is null
60 and p.owner = j.program_owner
61 and p.program_name = j.program_name
62 union all
63 select 'NAP'
64 from dba_scheduler_jobs j
65 where
66 j.job_type is null
67 and not exists (select 1 from
68 dba_scheduler_programs p
69 where
70 p.owner = j.program_owner
71 and p.program_name = j.program_name)
72 union all
73 select job_type
74 from dba_scheduler_jobs where job_type is not null)
75 group by jt order by 1)
76 loop
77 sum1 := sum1 || ',JTI' || substr(it.t,1,3) || ':' || it.n;
78 end loop;
79 -- Direct per schedule type counts, i.e. of the total
80 -- number of jobs how many are
81 -- repeat_interval is null, schedule based, event based, file watcher based,
82 -- plsql repeat interval, calendar repeat interval, window based
83
84
85
86 for it in (
87 select schedule_type t,
88 count(*) n
89 from dba_scheduler_jobs
90 group by schedule_type order by 1)
91 loop
92 sum1 := sum1 || ',JDS' || substr(replace(it.t, 'WINDOW_','W'),1,3) || ':' || it.n;
93 end loop;
94
95 -- Indirect per schedule type counts. In this case the schedule based jobs are
96 -- tracked down to their eventual schedule type. So now of the total number of jobs, how many are
97 -- repeat_interval is null, event based, file watcher, plsql repeat interval,
98 -- calendar repeat interval, window (group) based
99
100 for it in (
101 select schedule_type t, count(*) n from
102 (select p.schedule_type
103 from dba_scheduler_jobs j,
104 dba_scheduler_schedules p
105 where
106 j.schedule_type = 'NAMED'
107 and p.owner = j.schedule_owner
108 and p.schedule_name = j.schedule_name
109 union all
110 select schedule_type
111 from dba_scheduler_jobs where schedule_type <> 'NAMED')
112 group by schedule_type order by 1)
113 loop
114 sum1 := sum1 || ',JIS' || substr(replace(it.t, 'WINDOW_','W'),1,3) || ':' || it.n;
115 end loop;
116
117
118 -- Number of jobs that have destination set to a
119 -- single destination vs destination set to a destination group
120
121 for it in (
122 select dest t, count(*) n
123 from (select decode(number_of_destinations,1, 'SD', 'MD') dest
124 from dba_scheduler_jobs where destination is not null)
125 group by dest order by 1)
126 loop
127 sum1 := sum1 || ',JD' || it.t || ':' || it.n;
128 end loop;
129
130 -- Number of external jobs (job type or program type executable) split across local without a credential,
131 -- local with credential, remote single destination, remote destination group
132 for it in (
133 select ext_type t, count(*) n from
134 (select job_name, decode(destination, null,
135 decode(credential_name, null,'JXL','JXLC'),
136 decode(dest_type,null,'JXRID','SINGLE','JXRSD','JXRGD')) ext_type from
137 (select job_name, job_type, credential_name, destination_owner, destination
138 from all_scheduler_jobs where program_name is null
139 union all
140 select job_name, program_type, credential_name, destination_owner, destination
141 from all_scheduler_jobs aj, all_scheduler_programs ap
142 where aj.program_owner = ap.owner and aj.program_name = ap.program_name) aij,
143 (select owner, group_name dest_name, 'GROUP' dest_type from all_scheduler_groups
144 where group_type = 'EXTERNAL_DEST'
145 union all
146 select 'SYS', destination_name, 'SINGLE' from all_scheduler_external_dests) ad
147 where job_type in ('EXECUTABLE','EXTERNAL_SCRIPT') and aij.destination_owner = ad.owner(+) and
148 aij.destination = ad.dest_name(+)) group by ext_type order by 1)
149 loop
150 sum1 := sum1 || ',' || it.t || ':' || it.n;
151 end loop;
152
153
154 -- Number of remote database jobs with single destination versus number of jobs with destination group (i.e. destination is set and job type or program type is plsql block or stored procedure).
155
156 for it in (
157 select dest_type t, count(*) n from
158 (select job_type, destination_owner, destination
159 from all_scheduler_jobs where program_name is null
160 union all
161 select program_type, destination_owner, destination
162 from all_scheduler_jobs aj, all_scheduler_programs ap
163 where aj.program_owner = ap.owner and aj.program_name = ap.program_name) aij,
164 (select owner, group_name dest_name, 'JDBG' dest_type from all_scheduler_groups
165 where group_type = 'DB_DEST'
166 union all
167 select owner, destination_name, 'JDBS' from all_scheduler_db_dests) ad
168 where job_type in ('STORED_PROCEDURE','PLSQL_BLOCK','SQL_SCRIPT', 'BACKUP_SCRIPT') and
169 aij.destination is not null and aij.destination_owner = ad.owner(+) and
170 aij.destination = ad.dest_name(+) group by dest_type order by 1)
171 loop
172 sum1 := sum1 || ',' || it.t || ':' || it.n;
173 end loop;
174
175 -- Number of jobs with arguments. For those jobs with arguments, avg,
176 -- median and max number of job arguments.
177
178 select count(*),
179 avg(number_of_arguments),
180 median(number_of_arguments),
181 max(number_of_arguments) into n1, n2, n3, n4
182 from dba_scheduler_jobs where number_of_arguments > 0;
183
184 sum1 := sum1
185 || ',JAC:' || n1
186 || ',JAA:' || round(n2)
187 || ',JAM:' || n3
188 || ',JAX:' || n4;
189
190 -- Split total number of jobs across job_style, i.e. regular vs lightweight
191
192 for it in (
193 select job_style t, count(*) n from dba_scheduler_jobs
194 group by job_style order by 1)
195 loop
196 sum1 := sum1 || ',JST' || substr(it.t,1,3) || ':' || it.n;
197 end loop;
198
199
200 -- Number of jobs that have restartable set to true
201 -- How many have max_run_duration set
202 -- How many have schedule_limit set
203 -- How many have instance_id set
204 -- How many have allow_runs_in_restricted_mode set
205 -- How many have raise_events set
206 -- How many have parallel_instances set
207 select sum(decode(restartable,null, 0,1)),
208 sum(decode(max_run_duration,null, 0,1)) ,
209 sum(decode(schedule_limit,null, 0,1)) ,
210 sum(decode(instance_id,null, 0,1)) ,
211 sum(decode(allow_runs_in_restricted_mode,'FALSE', 0,1)) ,
212 sum(decode(bitand(flags, 2147483648),2147483648,1,0)),
213 sum(decode(bitand(flags, 68719476736),68719476736,1,0)),
214 sum(decode(enabled,'FALSE',1,0)),
215 sum(decode(raise_events,null, 0,1))
216 into n1, n2, n3, n4, n5,n6, n7, n8, n9
217 from dba_scheduler_jobs;
218 sum1 := sum1
219 || ',JRS:' || n1
220 || ',JMRD:' || n2
221 || ',JSL:' || n3
222 || ',JII:' || n4
223 || ',JAR:' || n5
224 || ',JFLW:' || n7
225 || ',JRE:' || n9
226 || ',JDIS:' || n8
227 || ',JPI:' || n6;
228
229 -- Total number of programs
230 -- Per type program numbers, i.e. the number of executable, plsql_block,
231 -- stored procedure, chain programs
232
233 for it in (
234 select program_type t, count(*) n from dba_scheduler_programs
235 group by program_type order by 1)
236 loop
237 sum1 := sum1 || ',PRT' || substr(it.t,1,3) || ':' || it.n;
238 end loop;
239
240
241 -- Number of programs with arguments
242 -- For programs with arguments, avg, mean and max number of arguments
243 select count(*) , round(avg(number_of_arguments)) ,
244 median(number_of_arguments) ,
245 max(number_of_arguments)
246 into n1, n2, n3, n4
247 from dba_scheduler_programs where number_of_arguments > 0;
248 sum1 := sum1
249 || ',PAC:' || n1
250 || ',PAA:' || n2
251 || ',PAM:' || n3
252 || ',PAX:' || n4;
253
254 -- Total number of schedules
255 -- Split across schedule type. How many in each category:
256 -- run once, plsql repeat interval, calendar repeat interval, event based,
257 -- file watcher, window based
258
259
260 for it in (
261 select schedule_type t, count(*) n from dba_scheduler_schedules group by
262 schedule_type order by 1)
263 loop
264 sum1 := sum1 || ',SST' || substr(it.t,1,3) || ':' || it.n;
265 end loop;
266
267
268 -- Total number of arguments
269 -- How many of them are named arguments
270
271 for it in (
272 select an t, count(*) n
273 from (select decode(argument_name, null, 'PA_', 'PAN') an from
274 dba_scheduler_program_args) group by an order by 1)
275 loop
276 sum1 := sum1 || ',' || it.t || ':' || it.n;
277 end loop;
278
279 -- Split across count of metadata arguments, varchar based args, anydata based arguments
280 for it in (
281 select metadata_attribute t, count(*) n from dba_scheduler_program_args where
282 metadata_attribute is not null group by metadata_attribute order by 1)
283 loop
284 sum1 := sum1 || ',PM' ||
285 substr(replace(replace(it.t,'JOB_','J'),'WINDOW_','W'),1,3)
286 || ':' || it.n;
287 end loop;
288
289 -- Job Classes
290 -- Total number of job classes
291 -- How many have service set
292 -- How many have resource consumer group set
293 -- split across logging levels, i.e. how many no logging, failed runs, runs only, full
294
295 select count(*) , sum(decode(service, null, 0, 1)) ,
296 sum(decode(resource_consumer_group, null, 0, 1)) into n1,n2,n3
297 from dba_scheduler_job_classes;
298 sum1 := sum1
299 || ',JCNT:' || n1
300 || ',JCSV:' || n2
301 || ',JCCG:' || n3 ;
302
303 for it in (
304 select logging_level t, count(*) n from dba_scheduler_job_classes
305 group by logging_level order by 1)
306 loop
307 sum1 := sum1 || ',LL' || substr(it.t,1,3) || ':' || it.n;
308 end loop;
309
310 -- Windows
311 -- Total number of windows
312 -- Number of high priority windows (low = total - high)
313 -- Number of windows without a resource plan
314 -- Number of named schedule based windows (inlined schedule = total - named schedule)
315 for it in (
316 select window_priority t, count(*) n from dba_scheduler_windows
317 group by window_priority order by 1)
318 loop
319 sum1 := sum1 || ',WIP' || substr(it.t,1,2) || ':' || it.n;
320 end loop;
321
322 select count(*) into n1 from dba_scheduler_windows where resource_plan is
323 null;
324 sum1 := sum1
325 || ',WINR:' || n1;
326
327 for it in (
328 select st t, count(*) n from
329 (select schedule_type st
330 from
331 dba_scheduler_windows) group by st order by 1)
332 loop
333 sum1 := sum1 || ',SWT' || substr(it.t,1,2) || ':' || it.n;
334 end loop;
335
336
337 -- Chains
338 -- Total number of chains
339 -- How many have evaluation interval set
340 -- How many were created with a rule set passed in
341 -- Total number of steps
342 -- How many steps have destination set
343 -- Avg, mean and max number of steps per chain
344 -- Total number of rules
345 -- Avg, mean and max number of rules per chain
346 -- ? How many of them use simple syntax
347 -- ? Avg, mean and max number of steps per rule condition
348 -- ? Avg, mean and max number of steps per rule action
349
350 select count(*), sum(decode(evaluation_interval, null, 0, 1)) EV,
351 sum(decode(user_rule_set, 'TRUE', 1, 0)) UR,
352 sum(nvl(number_of_rules,0)) NR, sum(nvl(number_of_steps,0)) NS,
353 round(avg(number_of_steps)) VS , median(number_of_steps) MS,
354 max(number_of_steps) XS into n1, n2,n3,n4,n5,n6,n7,n8
355 from dba_scheduler_chains;
356 sum1 := sum1
357 || ',CCNT:' || n1
358 || ',CEVI:' || n2
359 || ',CURS:' || n3
360 || ',CNRR:' || n4
361 || ',CNRS:' || n5
362 || ',CAVS:' || n6
363 || ',CMDS:' || n7
364 || ',CMXS:' || n8;
365
366
367 select count(*) into n1
368 from dba_scheduler_chain_steps where destination is not null;
369 sum1 := sum1
370 || ',CSRD:' || n1 ;
371
372 -- Direct per step type counts. Of total how many steps point to:
373 -- program vs (sub)chain vs event
374 for it in (
375 select step_type t, count(*) n from dba_scheduler_chain_steps
376 group by step_type order by 1)
377 loop
378 sum1 := sum1 || ',CSP' || substr(it.t,1,3) || ':' || it.n;
379 end loop;
380
381 -- Indirect per step type counts. By following the program type how many are:
382 -- executable vs plsql block vs stored procedure vs (sub)chain vs event
383
384 for it in (
385 select step_type t, count(*) n from
386 (select step_type from dba_scheduler_chain_steps
387 where step_type <> 'PROGRAM'
388 union all
389 select program_type from dba_scheduler_programs p,
390 dba_scheduler_chain_steps s
391 where
392 s.step_type = 'PROGRAM' and
393 s.program_owner =p.owner and
394 s.program_name = p.program_name)
395 group by step_type order by 1)
396 loop
397 sum1 := sum1 || ',CHST' || substr(it.t,1,3) || ':' || it.n;
398 end loop;
399
400 -- Total number of credentials
401 -- How many have database role set
402 -- How many have windows domain set
403
404 select count(*), sum(decode(database_role, null, 0, 1)),
405 sum(decode(windows_domain, null, 0, 1))
406 into n1,n2,n3
407 from dba_scheduler_credentials;
408 sum1 := sum1
409 || ',CRNR:' || n1
410 || ',CRDB:' || n2
411 || ',CSWD:' || n3 ;
412
413 -- Total number of destinations
414 -- How many database destinations (external dests = total - database dests)
415 -- Of the database destinations, how many specified connect info (non null tns_name)
416
417 for it in (
418 select dt t, count(*) n from
419 (select decode(destination_type, 'EXTERNAL', 'DSXT', 'DSDB') dt
420 from dba_scheduler_dests )
421 group by dt order by 1)
422 loop
423 sum1 := sum1 || ',' || it.t || ':' || it.n;
424 end loop;
425
426 select count(*) into n1 from dba_scheduler_db_dests
427 where connect_info is null;
428 sum1 := sum1
429 || ',DSDN:' || n1 ;
430 -- File Watcher
431 -- Total number of file watchers
432 -- How many remote file watchers (destination is non null)
433 -- How many have minimum file size > 0
434 -- How many have steady_state_duration set to a non-null value
435 select count(*),
436 sum(decode(steady_state_duration, null, 0,1)),
437 sum(decode(destination, null, 0,1)),
438 sum(decode(nvl(min_file_size,0), 0, 0, 1))
439 into n1,n2,n3,n4
440 from dba_scheduler_file_watchers;
441 sum1 := sum1
442 || ',FWNR:' || n1
443 || ',FWSS:' || n2
444 || ',FWDS:' || n3
445 || ',FWMF' || n4 ;
446
447
448 -- Groups
449 -- Total number of groups
450 -- Per group type count, i.e. how many are db_dest vs external_dest vs window
451 -- Avg, mean and max number of members per group
452
453 for it in (
454 select group_type t, count(*) n , round(avg(number_of_members)) a ,
455 max(number_of_members) b,
456 median(number_of_members) c
457 from dba_scheduler_groups group by group_type order by 1)
458 loop
459 sum1 := sum1 || ',G' || substr(it.t,1,3) || 'N:' || it.n
460 || ',G' || substr(it.t,1,3) || 'A:' || it.a
461 || ',G' || substr(it.t,1,3) || 'X:' || it.b
462 || ',G' ||substr( it.t,1,3) || 'M:' || it.c;
463 end loop;
464
465
466 -- Calendar Syntax
467 -- Total number of schedules
468 -- Total number of non-null repeat_intervals schedules
469 -- Of the calendar syntax ones how many:
470 -- use include, exclude, or intersect
471 -- have a user defined frequency
472 -- use offset
473
474 select count(*) into n1 from dba_scheduler_schedules;
475 sum1 := sum1
476 || ',SCHNRA:' || n1;
477
478 select count(*) into n1 from dba_scheduler_schedules
479 where repeat_interval is not null;
480 sum1 := sum1
481 || ',SCHNNR:' || n1;
482
483 for it in (
484 select typ t, count(*) n from
485 (select decode(instr(i,'FREQ=YEARLY'),1, 'Y',
486 decode(instr(i, 'FREQ=MONTHLY'),1,'M',
487 decode(instr(i,'FREQ=WEEKLY'),1, 'W',
488 decode(instr(i,'FREQ=DAILY'),1, 'D',
489 decode(instr(i,'FREQ=HOURLY'),1, 'H',
490 decode(instr(i,'FREQ=MINUTELY'),1, 'MI',
491 decode(instr(i,'FREQ=SECONDLY'),1, 'S',
492 decode(instr(i,'FREQ='),1, 'U','X')))))))) typ
493 from (select replace(upper(iv), ' ', '') i from (
494 select repeat_interval iv
495 from dba_scheduler_jobs
496 where schedule_type = 'CALENDAR'
497 union all select repeat_interval from dba_scheduler_schedules where
498 schedule_type = 'CALENDAR')))
499 group by typ order by 1)
500 loop
501 sum1 := sum1 || ',CAF' || it.t || ':' || it.n;
502 end loop;
503
504
505 select sum(decode(instr(i, 'OFFSET'), 0, 0, 1)) "Offset",
506 sum(decode(instr(i, 'SPAN'), 0, 0, 1)) "Span",
507 sum(decode(instr(i, 'BYSETPOS'), 0, 0, 1)) "Bysetp",
508 sum(decode(instr(i, 'INCLUDE'), 0, 0, 1)) "Inc",
509 sum(decode(instr(i, 'EXCLUDE'), 0, 0, 1)) "EXC",
510 sum(decode(instr(i, 'INTERSECT'), 0, 0, 1)) "ISEC"
511 into n1,n2,n3,n4,n5,n6
512 from (select replace(upper(iv), ' ', '') i from (
513 select repeat_interval iv
514 from dba_scheduler_jobs
515 where schedule_type = 'CALENDAR'
516 union all select repeat_interval from dba_scheduler_schedules where
517 schedule_type = 'CALENDAR'));
518 sum1 := sum1
519 || ',CAOF:' || n1
520 || ',CASC:' || n2
521 || ',CABS:' || n3
522 || ',CAIC:' || n4
523 || ',CAEX:' || n5
524 || ',CAIS:' || n6;
525
526
527 select count (distinct owner||job_name) into n1
528 from dba_scheduler_notifications;
529 sum1 := sum1
530 || ',SNNR:' || n1;
531
532 for it in (
533 select event t, count(*) n
534 from dba_scheduler_notifications
535 group by event order by 1)
536 loop
537 sum1 := sum1 || ',JN'
538 || substr(replace(it.t, 'JOB_','J'),1,5) || ':' || it.n;
539 end loop;
540 summary := to_clob(sum1);
541 END;
[Home] [Help]