1 PACKAGE dbms_resource_manager AUTHID CURRENT_USER AS
2
3 --
4 -- create a new resource plan
5 --
6 -- Input arguments:
7 -- plan - name of resource plan
8 -- comment - user's comment
9 -- cpu_mth - allocation method for CPU resources
10 -- (deprecated)
11 -- active_sess_pool_mth - allocation method for max. active sessions
12 -- parallel_degree_limit_mth - allocation method for degree of parallelism
13 -- queueing_mth - type of queueing policy to use
14 -- mgmt_mth - allocation method for CPU and I/O resources
15 -- sub_plan - whether plan is sub_plan
16 -- max_iops - maximum I/O requests per second
17 -- max_mbps - maximum megabytes of I/O per second
18 --
19
20 PROCEDURE create_plan(plan IN VARCHAR2,
21 comment IN VARCHAR2 DEFAULT NULL,
22 cpu_mth IN VARCHAR2 DEFAULT NULL,
23 active_sess_pool_mth IN VARCHAR2
24 DEFAULT 'ACTIVE_SESS_POOL_ABSOLUTE',
25 parallel_degree_limit_mth IN VARCHAR2 DEFAULT
26 'PARALLEL_DEGREE_LIMIT_ABSOLUTE',
27 queueing_mth IN VARCHAR2 DEFAULT 'FIFO_TIMEOUT',
28 mgmt_mth IN VARCHAR2 DEFAULT 'EMPHASIS',
29 sub_plan IN BOOLEAN DEFAULT FALSE,
30 max_iops IN NUMBER DEFAULT NULL,
31 max_mbps IN NUMBER DEFAULT NULL
32 );
33
34 --
35 -- update an existing resource plan. NULL arguments leave the resource plan
36 -- unchanged in the dictionary.
37 --
38 -- Input arguments:
39 -- plan - name of resource plan
40 -- new_comment - new user's comment
41 -- new_cpu_mth - name of new allocation method for CPU
42 -- resources (deprecated)
43 -- new_active_sess_pool_mth - name of new method for max. active
44 -- sessions
45 -- new_parallel_degree_limit_mth - name of new method for degree of
46 -- parallelism
47 -- new_queueing_mth - new type of queueing policy to use
48 -- new_mgmt_mth - name of new allocation method for CPU
49 -- and I/O resources
50 -- new_sub_plan - whether plan is sub_plan
51 -- new_max_iops - new maximum I/O requests per second
52 -- new_max_mbps - new maximum megabytes of I/O per second
53 --
54 PROCEDURE update_plan(plan IN VARCHAR2,
55 new_comment IN VARCHAR2 DEFAULT NULL,
56 new_cpu_mth IN VARCHAR2 DEFAULT NULL,
57 new_active_sess_pool_mth IN VARCHAR2
58 DEFAULT NULL,
59 new_parallel_degree_limit_mth IN VARCHAR2
60 DEFAULT NULL,
61 new_queueing_mth IN VARCHAR2 DEFAULT NULL,
62 new_mgmt_mth IN VARCHAR2 DEFAULT NULL,
63 new_sub_plan IN BOOLEAN DEFAULT FALSE,
64 new_max_iops IN NUMBER DEFAULT NULL,
65 new_max_mbps IN NUMBER DEFAULT NULL
66 );
67
68 --
69 -- delete an existing resource plan
70 --
71 -- Input arguments:
72 -- plan - name of resource plan to delete
73 --
74 PROCEDURE delete_plan(plan IN VARCHAR2);
75
76
77 --
78 -- delete an existing resource plan cascade
79 --
80 -- Input arguments:
81 -- plan - name of plan
82 --
83 PROCEDURE delete_plan_cascade(plan IN VARCHAR2);
84
85
86 --
87 -- create a new resource consumer group
88 --
89 -- Input arguments:
90 -- consumer_group - name of consumer group
91 -- comment - user's comment
92 -- cpu_mth - name of CPU resource allocation method (deprecated)
93 -- mgmt_mth - name of CPU and I/O resource allocation method
94 -- category - consumer group category
95 --
96 PROCEDURE create_consumer_group(consumer_group IN VARCHAR2,
97 comment IN VARCHAR2 DEFAULT NULL,
98 cpu_mth IN VARCHAR2 DEFAULT NULL,
99 mgmt_mth IN VARCHAR2 DEFAULT 'ROUND-ROBIN',
100 category IN VARCHAR2 DEFAULT 'OTHER');
101
102 --
103 -- update an existing resource consumer group
104 --
105 -- Input arguments:
106 -- consumer_group - name of consumer group
107 -- new_comment - new user's comment
108 -- new_cpu_mth - name of new method for CPU resource allocation
109 -- (deprecated)
110 -- new_mgmt_mth - name of new method for CPU and I/O resource allocation
111 -- new_category - new consumer group category
112 --
113 PROCEDURE update_consumer_group(consumer_group IN VARCHAR2,
114 new_comment IN VARCHAR2 DEFAULT NULL,
115 new_cpu_mth IN VARCHAR2 DEFAULT NULL,
116 new_mgmt_mth IN VARCHAR2 DEFAULT NULL,
117 new_category IN VARCHAR2 DEFAULT NULL);
118
119 --
120 -- delete an existing resource consumer group
121 --
122 -- Input arguments:
123 -- consumer_group - name of consumer group to be deleted
124 --
125 PROCEDURE delete_consumer_group(consumer_group IN VARCHAR2);
126
127 --
128 -- create a new resource consumer group category
129 --
130 -- Input arguments:
131 -- category - name of consumer group category
132 -- comment - user's comment
133 --
134 PROCEDURE create_category(category IN VARCHAR2,
135 comment IN VARCHAR2 DEFAULT NULL);
136
137 --
138 -- update an existing resource consumer group category
139 --
140 -- Input arguments:
141 -- category - name of consumer group category
142 -- new_comment - new user's comment
143 --
144 PROCEDURE update_category(category IN VARCHAR2,
145 new_comment IN VARCHAR2 DEFAULT NULL);
146
147 --
148 -- delete an existing resource consumer group category
149 --
150 -- Input arguments:
151 -- category - name of consumer group category to be deleted
152 --
153 PROCEDURE delete_category(category IN VARCHAR2);
154
155 --
156 -- create a new resource plan directive
157 --
158 -- Input arguments:
159 -- plan - name of resource plan
160 -- group_or_subplan - name of consumer group or subplan
161 -- comment - comment for the plan directive
162 -- cpu_p1 - first parameter for the CPU resource
163 -- allocation method (deprecated)
164 -- cpu_p2 - second parameter for the CPU resource
165 -- allocation method (deprecated)
166 -- cpu_p3 - third parameter for the CPU resource
167 -- allocation method (deprecated)
168 -- cpu_p4 - fourth parameter for the CPU resource
169 -- allocation method (deprecated)
170 -- cpu_p5 - fifth parameter for the CPU resource
171 -- allocation method (deprecated)
172 -- cpu_p6 - sixth parameter for the CPU resource
173 -- allocation method (deprecated)
174 -- cpu_p7 - seventh parameter for the CPU resource
175 -- allocation method (deprecated)
176 -- cpu_p8 - eighth parameter for the CPU resource
177 -- allocation method (deprecated)
178 -- active_sess_pool_p1 - first parameter for the max. active sessions
179 -- allocation method
180 -- queueing_p1 - queue timeout in seconds
181 -- parallel_degree_limit_p1 - first parameter for the degree of parallelism
182 -- allocation method
183 -- switch_group - group to switch to, once a switch condition
184 -- is met
185 -- switch_time - max execution time within a group
186 -- switch_estimate - use execution time estimate to assign group?
187 -- max_est_exec_time - max. estimated execution time in seconds
188 -- undo_pool - max. cumulative undo allocated for
189 -- consumer groups
190 -- max_idle_time - max. idle time
191 -- max_idle_blocker_time - max. idle time when blocking other sessions
192 -- switch_time_in_call - max execution time within a top call -
193 -- will switch back to home group after call
194 -- (deprecated)
195 -- mgmt_p1 - first parameter for the resource management
196 -- method (replaces cpu_p1)
197 -- mgmt_p2 - second parameter for the resource management
198 -- method (replaces cpu_p2)
199 -- mgmt_p3 - third parameter for the resource management
200 -- method (replaces cpu_p3)
201 -- mgmt_p4 - fourth parameter for the resource management
202 -- method (replaces cpu_p4)
203 -- mgmt_p5 - fifth parameter for the resource management
204 -- method (replaces cpu_p5)
205 -- mgmt_p6 - sixth parameter for the resource management
206 -- method (replaces cpu_p6)
207 -- mgmt_p7 - seventh parameter for the resource management
208 -- method (replaces cpu_p7)
209 -- mgmt_p8 - eighth parameter for the resource management
210 -- method (replaces cpu_p8)
211 -- switch_io_megabytes - max MBs of IO issued within a group
212 -- switch_io_reqs - max IO requests issued within a group
213 -- switch_for_call - if group switch occurs, switch back to home
214 -- group after call
215 -- max_utilization_limit - maximum resource allocation set as
216 -- percentage cap (deprecated)
217 -- parallel_target_percentage - maximum percentage of parallel target
218 -- before queuing (deprecated)
219 -- parallel_queue_timeout - queue timeout for parallel query queuing
220 -- parallel_server_limit - maximum percentage of parallel target
221 -- before queuing
222 -- (replaces parallel_target_percentage)
223 -- utilization_limit - maximum resource allocation set as
224 -- percentage cap
225 -- (replaces max_utilization_limit)
226 -- switch_io_logical - max logical IOs issued within a group
227 -- switch_elapsed_time - max elapsed time within a group
228 -- shares - number of shares for resource allocation
229 -- parallel_stmt_critical - queuing policy for critical parallel
230 -- statements
231 --
232
233 PROCEDURE create_plan_directive(plan IN VARCHAR2,
234 group_or_subplan IN VARCHAR2,
235 comment IN VARCHAR2 DEFAULT NULL,
236 cpu_p1 IN NUMBER DEFAULT NULL,
237 cpu_p2 IN NUMBER DEFAULT NULL,
238 cpu_p3 IN NUMBER DEFAULT NULL,
239 cpu_p4 IN NUMBER DEFAULT NULL,
240 cpu_p5 IN NUMBER DEFAULT NULL,
241 cpu_p6 IN NUMBER DEFAULT NULL,
242 cpu_p7 IN NUMBER DEFAULT NULL,
243 cpu_p8 IN NUMBER DEFAULT NULL,
244 active_sess_pool_p1 IN NUMBER DEFAULT NULL,
245 queueing_p1 IN NUMBER DEFAULT NULL,
246 parallel_degree_limit_p1 IN NUMBER
247 DEFAULT NULL,
248 switch_group IN VARCHAR2 DEFAULT NULL,
249 switch_time IN NUMBER DEFAULT NULL,
250 switch_estimate IN BOOLEAN DEFAULT FALSE,
251 max_est_exec_time IN NUMBER DEFAULT NULL,
252 undo_pool IN NUMBER DEFAULT NULL,
253 max_idle_time IN NUMBER DEFAULT NULL,
254 max_idle_blocker_time IN NUMBER DEFAULT NULL,
255 switch_time_in_call IN NUMBER DEFAULT NULL,
256 mgmt_p1 IN NUMBER DEFAULT NULL,
257 mgmt_p2 IN NUMBER DEFAULT NULL,
258 mgmt_p3 IN NUMBER DEFAULT NULL,
259 mgmt_p4 IN NUMBER DEFAULT NULL,
260 mgmt_p5 IN NUMBER DEFAULT NULL,
261 mgmt_p6 IN NUMBER DEFAULT NULL,
262 mgmt_p7 IN NUMBER DEFAULT NULL,
263 mgmt_p8 IN NUMBER DEFAULT NULL,
264 switch_io_megabytes IN NUMBER DEFAULT NULL,
265 switch_io_reqs IN NUMBER DEFAULT NULL,
266 switch_for_call IN BOOLEAN DEFAULT NULL,
267 max_utilization_limit IN NUMBER DEFAULT NULL,
268 parallel_target_percentage IN NUMBER
269 DEFAULT NULL,
270 parallel_queue_timeout IN NUMBER DEFAULT NULL,
271 parallel_server_limit IN NUMBER DEFAULT NULL,
272 utilization_limit IN NUMBER DEFAULT NULL,
273 switch_io_logical IN NUMBER DEFAULT NULL,
274 switch_elapsed_time IN NUMBER DEFAULT NULL,
275 shares IN NUMBER DEFAULT NULL,
276 parallel_stmt_critical IN VARCHAR2
277 DEFAULT NULL
278 );
279
280 --
281 -- update a plan directive. A plan directive is specified by the plan
282 -- and group_or_subplan.
283 --
284 -- Input arguments:
285 -- plan - name of resource plan
286 -- group_or_subplan - name of group or subplan
287 -- new_comment - comment for the plan directive
288 -- new_cpu_p1 - first parameter for the CPU management
289 -- method (deprecated)
290 -- new_cpu_p2 - parameter for the CPU allocation
291 -- method (deprecated)
292 -- new_cpu_p3 - parameter for the CPU allocation
293 -- method (deprecated)
294 -- new_cpu_p4 - parameter for the CPU allocation
295 -- method (deprecated)
296 -- new_cpu_p5 - parameter for the CPU allocation
297 -- method (deprecated)
298 -- new_cpu_p6 - parameter for the CPU allocation
299 -- method (deprecated)
300 -- new_cpu_p7 - parameter for the CPU allocation
301 -- method (deprecated)
302 -- new_cpu_p8 - parameter for the CPU allocation
303 -- method (deprecated)
304 -- new_active_sess_pool_p1 - first parameter for the max. active
305 -- sessions allocation method
306 -- new_queueing_p1 - queue timeout in seconds
307 -- new_parallel_degree_limit_p1 - first parameter for the degree of
308 -- parallelism allocation method
309 -- new_switch_group - group to switch to, once a switch
310 -- condition is met
311 -- new_switch_time - max execution time within a group
315 -- consumer groups
312 -- new_switch_estimate - use execution time estimate?
313 -- new_max_est_exec_time - max. estimated execution time in seconds
314 -- new_undo_pool - max. cumulative undo allocated for
316 -- new_max_idle_time - max. idle time
317 -- new_max_idle_blocker_time - max. idle time when blocking other
318 -- sessions
319 -- new_switch_time_in_call - max execution time within a top call -
320 -- will switch back to home group after call
321 -- (deprecated)
322 -- new_mgmt_p1 - first parameter for the resource
323 -- management method (replaces new_cpu_p1)
324 -- new_mgmt_p2 - second parameter for the resource
325 -- management method (replaces new_cpu_p2)
326 -- new_mgmt_p3 - third parameter for the resource
327 -- management method (replaces new_cpu_p3)
328 -- new_mgmt_p4 - fourth parameter for the resourc
329 -- management method (replaces new_cpu_p4)
330 -- new_mgmt_p5 - fifth parameter for the resourc
331 -- management method (replaces new_cpu_p5)
332 -- new_mgmt_p6 - sixth parameter for the resourc
333 -- management method (replaces new_cpu_p6)
334 -- new_mgmt_p7 - seventh parameter for the resourc
335 -- management method (replaces new_cpu_p7)
336 -- new_mgmt_p8 - eighth parameter for the resourc
337 -- management method (replaces new_cpu_p8)
338 -- new_switch_io_megabytes - max MBs of IO issued within a group
339 -- new_switch_io_reqs - max IO requests issued within a group
340 -- new_switch_for_call - if group switch occurs, switch back to
341 -- home group after call
342 -- new_max_utilization_limit - maximum resource allocation set
343 -- as percentage cap (deprecated)
344 -- new_parallel_target_percentage - maximum percentage of parallel
345 -- server pool before queuing (deprecated)
346 -- new_parallel_queue_timeout - queue timeout for parallel query queuing
347 -- new_parallel_server_limit - maximum percentage of parallel
348 -- server pool before queuing
349 -- (replaces new_parallel_target_percentage)
350 -- new_utilization_limit - maximum resource allocation set
351 -- as percentage cap
352 -- (replaces new_max_utilization_limit)
353 -- new_switch_io_logical - max logical IOs issued within a group
354 -- new_switch_elapsed_time - max elapsed time within a group
355 -- new_shares - number of shares for resource allocation
356 -- new_parallel_stmt_critical - queuing policy for critical parallel
357 -- statements
358 --
359
360 PROCEDURE update_plan_directive(plan IN VARCHAR2,
361 group_or_subplan IN VARCHAR2,
362 new_comment IN VARCHAR2 DEFAULT NULL,
363 new_cpu_p1 IN NUMBER DEFAULT NULL,
364 new_cpu_p2 IN NUMBER DEFAULT NULL,
365 new_cpu_p3 IN NUMBER DEFAULT NULL,
366 new_cpu_p4 IN NUMBER DEFAULT NULL,
367 new_cpu_p5 IN NUMBER DEFAULT NULL,
368 new_cpu_p6 IN NUMBER DEFAULT NULL,
369 new_cpu_p7 IN NUMBER DEFAULT NULL,
370 new_cpu_p8 IN NUMBER DEFAULT NULL,
371 new_active_sess_pool_p1 IN NUMBER
372 DEFAULT NULL,
373 new_queueing_p1 IN NUMBER DEFAULT NULL,
374 new_parallel_degree_limit_p1 IN NUMBER
375 DEFAULT NULL,
376 new_switch_group IN VARCHAR2 DEFAULT NULL,
377 new_switch_time IN NUMBER DEFAULT NULL,
378 new_switch_estimate IN BOOLEAN DEFAULT NULL,
379 new_max_est_exec_time IN NUMBER DEFAULT NULL,
380 new_undo_pool IN NUMBER DEFAULT NULL,
381 new_max_idle_time IN NUMBER DEFAULT NULL,
382 new_max_idle_blocker_time IN NUMBER
383 DEFAULT NULL,
384 new_switch_time_in_call IN NUMBER
385 DEFAULT NULL,
386 new_mgmt_p1 IN NUMBER DEFAULT NULL,
387 new_mgmt_p2 IN NUMBER DEFAULT NULL,
388 new_mgmt_p3 IN NUMBER DEFAULT NULL,
389 new_mgmt_p4 IN NUMBER DEFAULT NULL,
390 new_mgmt_p5 IN NUMBER DEFAULT NULL,
391 new_mgmt_p6 IN NUMBER DEFAULT NULL,
392 new_mgmt_p7 IN NUMBER DEFAULT NULL,
393 new_mgmt_p8 IN NUMBER DEFAULT NULL,
394 new_switch_io_megabytes IN NUMBER
395 DEFAULT NULL,
396 new_switch_io_reqs IN NUMBER DEFAULT NULL,
400 new_parallel_target_percentage IN NUMBER
397 new_switch_for_call IN BOOLEAN DEFAULT NULL,
398 new_max_utilization_limit IN NUMBER
399 DEFAULT NULL,
401 DEFAULT NULL,
402 new_parallel_queue_timeout IN NUMBER
403 DEFAULT NULL,
404 new_parallel_server_limit IN NUMBER
405 DEFAULT NULL,
406 new_utilization_limit IN NUMBER DEFAULT NULL,
407 new_switch_io_logical IN NUMBER DEFAULT NULL,
408 new_switch_elapsed_time IN NUMBER
409 DEFAULT NULL,
410 new_shares IN NUMBER DEFAULT NULL,
411 new_parallel_stmt_critical IN VARCHAR2
412 DEFAULT NULL
413 );
414
415 --
416 -- delete a plan directive. A plan directive is uniquely specified by the plan
417 -- and group_or_subplan
418 --
419 -- Input arguments:
420 -- plan - name of resource plan
421 -- group_or_subplan - name of group or subplan
422 --
423 PROCEDURE delete_plan_directive(plan IN VARCHAR2,
424 group_or_subplan IN VARCHAR2);
425
426 --
427 -- Add or modify a consumer group mapping.
428 -- If consumer_group is NULL then the mapping is deleted.
429 --
430 -- Input arguments:
431 -- attribute - mapping attribute to add/modify, can be:
432 -- ORACLE_USER, SERVICE_NAME,
433 -- CLIENT_OS_USER, CLIENT_PROGRAM, CLIENT_MACHINE,
434 -- MODULE_NAME, MODULE_NAME_ACTION,
435 -- SERVICE_MODULE, SERVICE_MODULE_ACTION
436 -- (string constants defined in
437 -- dbms_resource_manager)
438 -- value - attribute value to match
439 -- consumer_group - name of the mapped consumer group
440 --
441 PROCEDURE set_consumer_group_mapping(attribute in VARCHAR2,
442 value in VARCHAR2,
443 consumer_group in VARCHAR2 DEFAULT NULL);
444
445 oracle_user CONSTANT VARCHAR2(30) := 'ORACLE_USER';
446 oracle_function CONSTANT VARCHAR2(30) := 'ORACLE_FUNCTION';
447 service_name CONSTANT VARCHAR2(30) := 'SERVICE_NAME';
448 client_os_user CONSTANT VARCHAR2(30) := 'CLIENT_OS_USER';
449 client_program CONSTANT VARCHAR2(30) := 'CLIENT_PROGRAM';
450 client_machine CONSTANT VARCHAR2(30) := 'CLIENT_MACHINE';
451 client_id CONSTANT VARCHAR2(30) := 'CLIENT_ID';
452 module_name CONSTANT VARCHAR2(30) := 'MODULE_NAME';
453 module_name_action CONSTANT VARCHAR2(30) := 'MODULE_NAME_ACTION';
454 service_module CONSTANT VARCHAR2(30) := 'SERVICE_MODULE';
455 service_module_action CONSTANT VARCHAR2(30) := 'SERVICE_MODULE_ACTION';
456 performance_class CONSTANT VARCHAR2(30) := 'PERFORMANCE_CLASS';
457
458 --
459 -- Set the mapping priorities (1 = highest).
460 -- Each parameter must be a distinct integer from 1 to 8.
461 --
462 -- Input arguments:
463 -- explicit - priority of the explicit group setting
464 -- oracle_user - priority of the "ORACLE user" mapping
465 -- service_name - priority of the "service name" mapping
466 -- client_os_user - priority of the "client OS user" mapping
467 -- client_program - priority of the "client program" mapping
468 -- client_machine - priority of the "client machine" mapping
469 -- module_name - priority of the "module name" mapping
470 -- module_name_action - priority of the
471 -- "module name.module action" mapping
472 -- service_module - priority of the
473 -- "service name.module name" mapping
474 -- service_module_action - priority of the
475 -- "service name.module name.module action" mapping
476 -- client_id - priority of the "client id" mapping
477 --
478 PROCEDURE set_consumer_group_mapping_pri(explicit IN NUMBER,
479 oracle_user IN NUMBER,
480 service_name IN NUMBER,
481 client_os_user IN NUMBER,
482 client_program IN NUMBER,
483 client_machine IN NUMBER,
484 module_name IN NUMBER,
485 module_name_action IN NUMBER,
486 service_module IN NUMBER,
487 service_module_action IN NUMBER,
488 client_id IN NUMBER DEFAULT 11);
489
490 --
491 -- create a pending area. Creates a temporary workspace to make changes.
492 --
493 -- Input arguments: None
494 --
495 PROCEDURE create_pending_area;
496
497
498 --
499 -- clear the pending area. Discards all the changes in the pending area
500 -- and renders it inactive.
501 --
502 -- Input arguments: None
503 --
504 PROCEDURE clear_pending_area;
505
506 --
507 -- validate the pending area. Validates all the changes made in the pending
511 --
508 -- area.
509 --
510 -- Input arguments: None
512 PROCEDURE validate_pending_area;
513
514
515 --
516 -- submit the pending area. Commits all the changes if they are valid and
517 -- renders the pending area inactive. If not, leaves the pending area
518 -- unchanged.
519 --
520 -- Input arguments: None
521 --
522 PROCEDURE submit_pending_area;
523
524 --
525 -- OBSOLETE: set the initial consumer group of a user.
526 --
527 PROCEDURE set_initial_consumer_group(user IN VARCHAR2,
528 consumer_group IN VARCHAR2);
529
530 --
531 -- switch the consumer group for all currently logged on users with the
532 -- given name
533 --
534 -- Input arguments:
535 -- user - name of the user
536 -- consumer_group - name of the consumer group to switch to
537 --
538 PROCEDURE switch_consumer_group_for_user(user IN VARCHAR2,
539 consumer_group IN VARCHAR2);
540
541 --
542 -- switch the consumer group for a session.
543 --
544 -- Input arguments:
545 -- session_id - SID column from the view V$SESSION
546 -- serial - SERIAL# column from the view V$SESSION
547 -- consumer_group - name of the consumer group to switch to
548 --
549 PROCEDURE switch_consumer_group_for_sess(session_id IN NUMBER,
550 session_serial IN NUMBER,
551 consumer_group IN VARCHAR2);
552
553 --
554 -- switch to the specified plan. This makes the specified plan active.
555 --
556 -- Input arguments:
557 -- plan_name - name of plan to switch to
558 -- allow_scheduler_plan_switches - allow scheduler to switch this plan
559 -- at a later time
560 --
561 PROCEDURE switch_plan(plan_name IN VARCHAR2,
562 sid IN VARCHAR2 DEFAULT '*',
563 allow_scheduler_plan_switches IN BOOLEAN DEFAULT TRUE);
564
565 --
566 -- create a simple plan that implicitly has SYS_GROUP at 100% at level 1
567 -- and OTHER_GROUPS at 100% at level 3, while the specified input groups
568 -- are all at level 2.
569 --
570 -- Input arguments:
571 -- simple_plan - name of plan to be created
572 -- consumer_group1 - name of consumer group
573 -- group1_cpu - percentage for group (deprecated)
574 -- consumer_group2 - name of consumer group
575 -- group2_cpu - percentage for group (deprecated)
576 -- consumer_group3 - name of consumer group
577 -- group3_cpu - percentage for group (deprecated)
578 -- consumer_group4 - name of consumer group
579 -- group4_cpu - percentage for group (deprecated)
580 -- consumer_group5 - name of consumer group
581 -- group5_cpu - percentage for group (deprecated)
582 -- consumer_group6 - name of consumer group
583 -- group6_cpu - percentage for group (deprecated)
584 -- consumer_group7 - name of consumer group
585 -- group7_cpu - percentage for group (deprecated)
586 -- consumer_group8 - name of consumer group
587 -- group8_cpu - percentage for group (deprecated)
588 -- group1_percent - percentage for group
589 -- group2_percent - percentage for group
590 -- group3_percent - percentage for group
591 -- group4_percent - percentage for group
592 -- group5_percent - percentage for group
593 -- group6_percent - percentage for group
594 -- group7_percent - percentage for group
595 -- group8_percent - percentage for group
596
597 PROCEDURE create_simple_plan (simple_plan IN VARCHAR2 DEFAULT NULL,
598 consumer_group1 IN VARCHAR2 DEFAULT NULL,
599 group1_cpu IN NUMBER DEFAULT NULL,
600 consumer_group2 IN VARCHAR2 DEFAULT NULL,
601 group2_cpu IN NUMBER DEFAULT NULL,
602 consumer_group3 IN VARCHAR2 DEFAULT NULL,
603 group3_cpu IN NUMBER DEFAULT NULL,
604 consumer_group4 IN VARCHAR2 DEFAULT NULL,
605 group4_cpu IN NUMBER DEFAULT NULL,
606 consumer_group5 IN VARCHAR2 DEFAULT NULL,
607 group5_cpu IN NUMBER DEFAULT NULL,
608 consumer_group6 IN VARCHAR2 DEFAULT NULL,
609 group6_cpu IN NUMBER DEFAULT NULL,
610 consumer_group7 IN VARCHAR2 DEFAULT NULL,
611 group7_cpu IN NUMBER DEFAULT NULL,
612 consumer_group8 IN VARCHAR2 DEFAULT NULL,
613 group8_cpu IN NUMBER DEFAULT NULL,
614 group1_percent IN NUMBER DEFAULT NULL,
615 group2_percent IN NUMBER DEFAULT NULL,
616 group3_percent IN NUMBER DEFAULT NULL,
617 group4_percent IN NUMBER DEFAULT NULL,
618 group5_percent IN NUMBER DEFAULT NULL,
619 group6_percent IN NUMBER DEFAULT NULL,
620 group7_percent IN NUMBER DEFAULT NULL,
621 group8_percent IN NUMBER DEFAULT NULL);
622
623 --
624 -- Initiate an I/O calibration
625 --
626 -- Input arguments:
627 -- num_physical_disks - Approximate number of physical disks in
631 --
628 -- the database storage
629 -- max_latency - Maximum tolerable latency in milliseconds for
630 -- database-block-sized IO requests
632 -- Output arguments:
633 -- max_iops - Maximum number of I/O requests per second that can be
634 -- sustained. The I/O requests are randomly-distributed,
635 -- database-block-sized reads.
636 -- max_mbps - Maximum throughput of I/O that can be sustained,
637 -- expressed in megabytes per second. The I/O requests
638 -- are randomly-distributed, 1 megabyte reads.
639 -- actual_latency - Average latency of database-block-sized I/O
640 -- requests at "max_iops" rate, expressed in milliseconds.
641 --
642 PROCEDURE calibrate_io (
643 num_physical_disks IN PLS_INTEGER DEFAULT 1,
644 max_latency IN PLS_INTEGER DEFAULT 20,
645 max_iops OUT PLS_INTEGER,
646 max_mbps OUT PLS_INTEGER,
647 actual_latency OUT PLS_INTEGER
648 );
649
650 --
651 -- Begin a sql block. The parallel queries in this sql block will have a
652 -- logical queue start time same to the queue start time of the first parallel
653 -- query in this sql block.
654 --
655 -- Input arguments: None
656 --
657 PROCEDURE begin_sql_block;
658
659 --
660 -- End a sql block
661 --
662 -- Input arguments: None
663 --
664 PROCEDURE end_sql_block;
665
666 --
667 -- Create a new consolidation resource plan
668 --
669 -- Note:
670 -- This can only be called when consolidation is enabled.
671 -- This can only be called from the root PDB.
672 --
673 -- Input arguments:
674 -- plan - name of consolidation plan
675 -- comment - user's comment
676 --
677
678 PROCEDURE create_cdb_plan
679 (
680 plan IN VARCHAR2,
681 comment IN VARCHAR2 DEFAULT ''
682 );
683
684 --
685 -- Update a consolidation resource plan
686 --
687 -- Note:
688 -- This can only be called when consolidation is enabled.
689 -- This can only be called from the root PDB.
690 --
691 -- Input arguments:
692 -- plan - name of consolidation plan to update
693 -- new_comment - user's comment
694 --
695
696 PROCEDURE update_cdb_plan
697 (
698 plan IN VARCHAR2,
699 new_comment IN VARCHAR2 DEFAULT NULL
700 );
701
702 --
703 -- Delete a consolidation resource plan
704 --
705 -- Note:
706 -- This can only be called when consolidation is enabled.
707 -- This can only be called from the root PDB.
708 --
709 -- Input arguments:
710 -- plan - name of consolidation plan to delete
711 --
712
713 PROCEDURE delete_cdb_plan
714 (
715 plan IN VARCHAR2
716 );
717
718 --
719 -- Update a consolidation resource plan's default directive
720 -- This directive applies to each PDB that does not have a
721 -- specific directive in the CDB plan.
722 --
723 -- Note:
724 -- This can only be called when consolidation is enabled.
725 -- This can only be called from the root PDB.
726 --
727 -- Input arguments:
728 -- plan - name of consolidation plan to update
729 -- new_comment - user's comment
730 -- new_shares - number of shares for resource allocation
731 -- new_utilization_limit - percentage of resources allowed to use
732 -- new_parallel_server_limit - percentage of parallel servers
733 -- allowed to use
734 --
735
736 PROCEDURE update_cdb_default_directive
737 (
738 plan IN VARCHAR2,
739 new_comment IN VARCHAR2 DEFAULT NULL,
740 new_shares IN NUMBER DEFAULT NULL,
741 new_utilization_limit IN NUMBER DEFAULT NULL,
745 );
742 new_parallel_server_limit IN NUMBER DEFAULT NULL,
743 new_memory_min IN NUMBER DEFAULT NULL,
744 new_memory_limit IN NUMBER DEFAULT NULL
746
747 --
748 -- Update a consolidation resource plan's autotask directive
749 -- This directive applies to the autotask jobs that run in the
750 -- root PDB.
751 --
752 -- Note:
753 -- This can only be called when consolidation is enabled.
754 -- This can only be called from the root PDB.
755 --
756 -- Input arguments:
757 -- plan - name of consolidation plan to update
758 -- new_comment - user's comment
759 -- new_shares - number of shares for resource allocation
760 -- new_utilization_limit - percentage of resources allowed to use
761 -- new_parallel_server_limit - percentage of parallel servers
762 -- allowed to use
763 --
764
765 PROCEDURE update_cdb_autotask_directive
766 (
767 plan IN VARCHAR2,
768 new_comment IN VARCHAR2 DEFAULT NULL,
769 new_shares IN NUMBER DEFAULT NULL,
770 new_utilization_limit IN NUMBER DEFAULT NULL,
771 new_parallel_server_limit IN NUMBER DEFAULT NULL,
772 new_memory_min IN NUMBER DEFAULT NULL,
773 new_memory_limit IN NUMBER DEFAULT NULL
774 );
775
776 --
777 -- Create a consolidation resource plan directive
778 --
779 -- Note:
780 -- This can only be called when consolidation is enabled.
781 -- This can only be called from the root PDB.
782 --
783 -- Input arguments:
784 -- plan - name of consolidation plan
785 -- pluggable_database - pluggable database
786 -- comment - user's comment
787 -- shares - number of shares resource allocation
788 -- utilization_limit - percentage of resources allowed to use
789 -- parallel_server_limit - percentage of parallel servers
790 -- allowed to use
791 -- memory_min - percentage of memory reserved
792 -- memory_limit - percentage of memory allowed to use
793 --
794
795 PROCEDURE create_cdb_plan_directive
796 (
797 plan IN VARCHAR2,
798 pluggable_database IN VARCHAR2,
799 comment IN VARCHAR2 DEFAULT '',
800 shares IN NUMBER DEFAULT NULL,
801 utilization_limit IN NUMBER DEFAULT NULL,
802 parallel_server_limit IN NUMBER DEFAULT NULL,
803 memory_min IN NUMBER DEFAULT NULL,
804 memory_limit IN NUMBER DEFAULT NULL
805 );
806
807 --
808 -- Update a consolidation resource plan directive
809 --
810 -- Note:
811 -- This can only be called when consolidation is enabled.
812 -- This can only be called from the root PDB.
813 --
814 -- Input arguments:
815 -- plan - name of consolidation plan
816 -- pluggable_database - pluggable database
817 -- new_comment - user's comment
818 -- new_shares - number of shares for resource allocation
819 -- new_utilization_limit - percentage of resources allowed to use
820 -- new_parallel_server_limit - percentage of parallel servers
821 -- allowed to use
822 -- new_memory_min - percentage of memory reserved
823 -- new_memory_limit - percentage of memory allowed to use
824 --
825
826 PROCEDURE update_cdb_plan_directive
827 (
828 plan IN VARCHAR2,
829 pluggable_database IN VARCHAR2,
830 new_comment IN VARCHAR2 DEFAULT NULL,
831 new_shares IN NUMBER DEFAULT NULL,
832 new_utilization_limit IN NUMBER DEFAULT NULL,
833 new_parallel_server_limit IN NUMBER DEFAULT NULL,
834 new_memory_min IN NUMBER DEFAULT NULL,
835 new_memory_limit IN NUMBER DEFAULT NULL
836 );
837
838 --
839 -- Delete a consolidation resource plan directive
840 --
841 -- Note:
842 -- This can only be called when consolidation is enabled.
843 -- This can only be called from the root PDB.
844 --
845 -- Input arguments:
846 -- plan - name of consolidation plan
847 -- pluggable_database - pluggable database
848 --
849
850 PROCEDURE delete_cdb_plan_directive
851 (
852 plan IN VARCHAR2,
853 pluggable_database IN VARCHAR2
854 );
855
856 --
857 -- Create a consolidation resource plan profile directive
858 --
859 -- Note:
860 -- This can only be called when consolidation is enabled.
861 -- This can only be called from the root PDB.
862 --
863 -- Input arguments:
864 -- plan - name of consolidation plan
865 -- profile - database profile
866 -- comment - user's comment
867 -- shares - number of shares resource allocation
868 -- utilization_limit - percentage of resources allowed to use
869 -- parallel_server_limit - percentage of parallel servers
870 -- allowed to use
871 -- memory_min - percentage of memory reserved
872 -- memory_limit - percentage of memory allowed to use
873 --
874
875 PROCEDURE create_cdb_profile_directive
876 (
877 plan IN VARCHAR2,
878 profile IN VARCHAR2,
879 comment IN VARCHAR2 DEFAULT '',
880 shares IN NUMBER DEFAULT NULL,
881 utilization_limit IN NUMBER DEFAULT NULL,
882 parallel_server_limit IN NUMBER DEFAULT NULL,
883 memory_min IN NUMBER DEFAULT NULL,
884 memory_limit IN NUMBER DEFAULT NULL
885 );
886
887 --
888 -- Update a consolidation resource plan profile directive
889 --
890 -- Note:
891 -- This can only be called when consolidation is enabled.
892 -- This can only be called from the root PDB.
893 --
894 -- Input arguments:
895 -- plan - name of consolidation plan
896 -- profile - database profile
897 -- new_comment - user's comment
898 -- new_shares - number of shares for resource allocation
899 -- new_utilization_limit - percentage of resources allowed to use
900 -- new_parallel_server_limit - percentage of parallel servers
901 -- allowed to use
902 -- new_memory_min - percentage of memory reserved
903 -- new_memory_limit - percentage of memory allowed to use
904 --
905
906 PROCEDURE update_cdb_profile_directive
907 (
908 plan IN VARCHAR2,
909 profile IN VARCHAR2,
910 new_comment IN VARCHAR2 DEFAULT NULL,
911 new_shares IN NUMBER DEFAULT NULL,
912 new_utilization_limit IN NUMBER DEFAULT NULL,
913 new_parallel_server_limit IN NUMBER DEFAULT NULL,
914 new_memory_min IN NUMBER DEFAULT NULL,
915 new_memory_limit IN NUMBER DEFAULT NULL
916 );
917
918 --
919 -- Delete a consolidation resource plan profile directive
920 --
921 -- Note:
922 -- This can only be called when consolidation is enabled.
923 -- This can only be called from the root PDB.
924 --
925 -- Input arguments:
926 -- plan - name of consolidation plan
927 -- profile - database profile
928 --
929
930 PROCEDURE delete_cdb_profile_directive
931 (
932 plan IN VARCHAR2,
933 profile IN VARCHAR2
934 );
935
936 END dbms_resource_manager;
[Home] [Help]