Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

consumer groups tips

Oracle Database Tips by Donald BurlesonJanuary 16, 2016

Question: I want a machanism to limit my end-users resource consumption and I understand that there is a component of the Oracle resource manager called consumer groups that will allow me to govern the amount of computing resources used by each end user.  Can you show an example of a consumer group?

Answer:  Consumer groups are a component of the Oracle resource_manager.  Also see my notes on changing resource manager objects.

Also see related switch_elapsed_time and switch_for_call.

To illustrate the use of the resource manager, assume there is a system in which OLTP operations must take priority over batch operations during the day.  At night, the situation is reversed such that batch operations take priority over OLTP operations.

To model this scenario, create two new consumer groups for the OLTP and batch tasks using the create_consumer_group procedure.

PROCEDURE create_consumer_group(
  consumer_group  IN  VARCHAR2,
  comment         IN  VARCHAR2,
  cpu_mth         IN  VARCHAR2 DEFAULT 'ROUND-ROBIN')

The create_consumer_groups.sql script uses this procedure to create the OLTP and batch consumer groups.

* create_consumer_groups.sql

--

CONN sys/password AS SYSDBA
BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;

  -- Create the consumer groups
  DBMS_RESOURCE_MANAGER.create_consumer_group(
    consumer_group => 'oltp_consumer_group',
    comment        => 'OLTP process consumer group.');

  DBMS_RESOURCE_MANAGER.create_consumer_group(
    consumer_group => 'batch_consumer_group',
    comment        => 'Batch process consumer group.');

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

The consumer_groups.sql script listed below uses the dba_rsrc_consumer_groups view to display information about the consumer groups that have been created.

* consumer_groups.sql

column comments format a60

select
 
  consumer_group,
   comments
from
   dba_rsrc_consumer_groups
order by
   consumer_group
;

The output from this script is displayed below.

SQL> @consumer_groups.sql

CONSUMER_GROUP                 COMMENTS
------------------------------ ------------------------------------------------
AUTO_TASK_CONSUMER_GROUP       System maintenance task consumer group
BATCH_CONSUMER_GROUP           Batch process consumer group.
DEFAULT_CONSUMER_GROUP         consumer group for users not assigned to any
                               group
LOW_GROUP                      Group of low priority sessions
OLTP_CONSUMER_GROUP            OLTP process consumer group.
OTHER_GROUPS                   consumer group for users not included in any
                               group in the active top-plan
SYS_GROUP                      Group of system sessions

The delete_consumer_groups.sql script uses the delete_consumer_group procedure to clean up the consumer groups created for the example.  The consumer groups can only be removed if they have no dependant plan directives.

* delete_consumer_groups.sql

BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area();
  DBMS_RESOURCE_MANAGER.create_pending_area();

  -- Delete consumer groups.
  DBMS_RESOURCE_MANAGER.delete_consumer_group (
    consumer_group => 'oltp_consumer_group'); 

  DBMS_RESOURCE_MANAGER.delete_consumer_group (
    consumer_group => 'batch_consumer_group'); 

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/

With the consumer groups present, a resource plan can be created using the create_plan procedure, and it can be associated to the consumer groups using the create_plan_directive procedure.

PROCEDURE create_plan (
  plan                      IN  VARCHAR2,
  comment                   IN  VARCHAR2,
  cpu_mth                   IN  VARCHAR2 DEFAULT 'EMPHASIS',
  active_sess_pool_mth      IN  VARCHAR2 DEFAULT 'ACTIVE_SESS_POOL_ABSOLUTE',
  parallel_degree_limit_mth IN  VARCHAR2 DEFAULT 'PARALLEL_DEGREE_LIMIT_ABSOLUTE',
  queueing_mth              IN  VARCHAR2 DEFAULT 'FIFO_TIMEOUT')                       

PROCEDURE create_plan_directive (
  plan                      IN  VARCHAR2,
  
group_or_subplan          IN  VARCHAR2,
  comment                   IN  VARCHAR2,
  cpu_p1                    IN  NUMBER DEFAULT NULL,
  cpu_p2                    IN  NUMBER DEFAULT NULL,
  cpu_p3                    IN  NUMBER DEFAULT NULL,
  cpu_p4                    IN  NUMBER DEFAULT NULL,
  cpu_p5                    IN  NUMBER DEFAULT NULL,
  cpu_p6                    IN  NUMBER DEFAULT NULL,
  cpu_p7                    IN  NUMBER DEFAULT NULL,
  cpu_p8                    IN  NUMBER DEFAULT NULL,
  active_sess_pool_p1       IN  NUMBER DEFAULT NULL,
  queueing_p1               IN  NUMBER DEFAULT NULL,
  parallel_degree_limit_p1  IN  NUMBER DEFAULT NULL,
  
switch_group              IN  VARCHAR2 DEFAULT NULL,
  switch_time               IN  NUMBER DEFAULT NULL,
  switch_estimate           IN  BOOLEAN DEFAULT FALSE,
  max_est_exec_time         IN  NUMBER DEFAULT NULL,
  undo_pool                 IN  NUMBER DEFAULT NULL,
  max_idle_time             IN  NUMBER DEFAULT NULL,
  max_idle_blocker_time     IN  NUMBER DEFAULT NULL,
  switch_time_in_call       IN  NUMBER DEFAULT NULL)

The day_plan.sql script uses these procedures to create a resource plan suitable for daytime processing.  The OLTP operations are associated 80% of the CPU on level one; while batch operations receive 100% of the remaining CPU at level two. 

The switch_group and switch_time parameters are used in the OLTP plan directive to specify that OLTP processes lasting more than 60 seconds should be switched to the batch consumer group.  The other_groups consumer group must be included in any valid plan as it provides resource allocation information for any processes that are not explicitly associated with the consumer groups.

* day_plan.sql


BEGIN
 
DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;

  -- Create a new plan
  DBMS_RESOURCE_MANAGER.create_plan(
    plan    => 'day_plan',
    comment => 'Plan suitable for daytime processing.');

  -- Assign consumer groups to plan and define priorities
  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan             => 'day_plan',
    group_or_subplan => 'oltp_consumer_group',
   
 comment          => 'Give OLTP processes higher priority - level 1',
    cpu_p1           => 80,
    switch_group     => 'batch_consumer_group',
    switch_time      => 60);

  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan             => 'day_plan',
    group_or_subplan => 'batch_consumer_group',
    comment          => 'Give batch processes lower priority - level 2',
    cpu_p2           => 100);

  DBMS_RESOURCE_MANAGER.create_plan_directive(
    plan             => 'day_plan',
    group_or_subplan => 'OTHER_GROUPS',
    comment          => 'all other users - level 3',
    cpu_p3           => 100);

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

The night_plan.sql script creates a resource plan suitable for nighttime processing in which the resource allocation is the reverse of the daytime processing, such that batch processes receive 80% of the CPU at level one, and OLTP operations receive 100% of the remaining CPU at level two.  Once again, the other_groups consumer group is specified as a catch-all.

* night_plan.sql

BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;

  -- Create a new plan
  DBMS_RESOURCE_MANAGER.create_plan(
    plan    => 'night_plan',
    comment => 'Plan suitable for daytime processing.');

  -- Assign consumer groups to plan and define priorities
  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan             => 'night_plan',
   
group_or_subplan => 'batch_consumer_group',
    comment          => 'Give batch processes lower priority - level 2',
    cpu_p1           => 80);

  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan             => 'night_plan',
    group_or_subplan => 'oltp_consumer_group',
    comment          => 'Give OLTP processes higher priority - level 1',
    cpu_p2           => 100);

  DBMS_RESOURCE_MANAGER.create_plan_directive(
  
 plan             => 'night_plan',
    group_or_subplan => 'OTHER_GROUPS',
    comment          => 'all other users - level 3',
    cpu_p3           => 100);

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

The resource_plan_directives.sql script uses the dba_rsrc_plan_directives view to display information about the resource plans currently defined on the system.

* resource_plan_directives.sql

select
   plan,
   group_or_subplan,
   status
from
   dba_rsrc_plan_directives
order by
   plan,
   group_or_subplan
;

The output from the resource_plan_directives.sql script is displayed below.

SQL> @resource_plan_directives.sql

PLAN                           GROUP_OR_SUBPLAN               STATUS
------------------------------ ------------------------------ ------
DAY_PLAN                       BATCH_CONSUMER_GROUP           ACTIVE
DAY_PLAN                       OLTP_CONSUMER_GROUP            ACTIVE
DAY_PLAN                       OTHER_GROUPS                   ACTIVE
INTERNAL_PLAN                  OTHER_GROUPS                   ACTIVE
INTERNAL_QUIESCE               OTHER_GROUPS                   ACTIVE
INTERNAL_QUIESCE               SYS_GROUP                      ACTIVE
NIGHT_PLAN                     BATCH_CONSUMER_GROUP           ACTIVE
NIGHT_PLAN                     OLTP_CONSUMER_GROUP            ACTIVE
NIGHT_PLAN                     OTHER_GROUPS                   ACTIVE
SYSTEM_PLAN                    LOW_GROUP                      ACTIVE
SYSTEM_PLAN                    OTHER_GROUPS                   ACTIVE
SYSTEM_PLAN                    SYS_GROUP                      ACTIVE

 

   
Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training
 
 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster