Oracle PL/SQL for DBAs

Using the Resource Manager

Oracle job scheduling priorities are managed through a database feature called the Resource Manager, which in turn is made available through the built-in package, DBMS_RESOURCE_MANAGER. It’s beyond the scope of this book to discuss the Resource Manager, so I will assume you are aware of and familiar with it.

Let’s look at an example. First, I define a resource manager group known as OLTP_GROUP to handle the database load during regular OLTP activities.

    BEGIN
       DBMS_RESOURCE_MANAGER.clear_pending_area (  );
       DBMS_RESOURCE_MANAGER.create_pending_area (  );
       DBMS_RESOURCE_MANAGER.create_consumer_group
              (consumer_group      => 'oltp_group',
               COMMENT             => 'OLTP Activity Group'
              );
       DBMS_RESOURCE_MANAGER.submit_pending_area (  );
    END;

Next, I need to define plan directives that show how resources are allocated among various groups in the plan. Oracle comes with a predefined group known as OTHER_GROUP. In this example, I define a second group known as OLTP_GROUP, which serves as the group for all OLTP activities. I am going to assign 80% of the CPU to OLTP activities and the rest to the OTHER_GROUP.

 BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area ( ); DBMS_RESOURCE_MANAGER.create_pending_area ( ); DBMS_RESOURCE_MANAGER.create_plan ('OLTP_PLAN', 'OLTP Database Activity Plan' ); DBMS_RESOURCE_MANAGER.create_plan_directive (PLAN => 'OLTP_PLAN', group_or_subplan => 'OLTP_GROUP', COMMENT => 'This is the OLTP Plan', cpu_p1 => 80, parallel_degree_limit_p1 => 4, switch_group => 'OTHER_GROUPS', ...