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', ...