Remote DBA Plans
Remote DBA Service

Oracle Resource Groups

Oracle Tips by Burleson Consulting

Oracle8i added the concept of Oracle resource groups. A resource group specification allows you to specify that a given group of database users can use only a certain percentage of the CPU resources on the system. A resource plan must be developed in a waterfall-type structure that defines the various levels within the application and their percentage allotment of CPU resources, where each subsequent level’s percentage is based on the previous level.

Creating a Resource Plan

Rather than a simple CREATE RESOURCE PLAN command, Oracle8i has a series of packages that must be run in a specific order to create a proper resource plan. All resource plans are created in a pending area before being validated and committed to the database. The requirements for a valid resource plan are outlined later in section titled “DBMS_RESOURCE_MANAGER Package.”

Resource plans can have up to 32 levels with 32 groups per level, allowing the most complex resource plan to be easily grouped. Multiple plans, subplans, and groups can all be tied together in an application, spanning CPU resource utilization rule set through the use of directives.

Creating a Resource Plan Manually

By manually creating a resource plan, you can change the resource allocations for the active session pool, which defaults to 1000000; the queuing resources for sessions, which defaults to 1000000; the maximum estimated execution time, which defaults to 1000000; and the undo pool allocation, which, as you have probably guessed, defaults to 1000000. If you use the Oracle Enterprise Manager to create and maintain plans, you are limited to controlling CPU allocation and parallel process allocation only. An example resource plan would be a simple two-tier plan like that shown in Figure 9.1.

Figure 9.1 Example of a resource plan.

An example of how this apportioning of CPU resources works would be to examine what happens in the plan shown in Figure 9.1. There, the top level, called MASTER, will have 100 percent of the CPU. The next level of the plan creates two subplans, USERS and REPORTS, which will get 60 and 20 percent of the CPU, respectively. Under USERS, are two groups, ONLINE_USERS and BATCH_USERS: ONLINE_USERS gets 70 percent of USERS’ 60 percent, or an overall percentage of CPU of 42 percent; the other subgroup, BATCH_USERS, gets 30 percent of the 60 percent, for a total overall percentage of 18.

Figure 9.2 Steps to create a resource plan.

The manual steps for creating a resource plan, its directives, and its groups are shown in Figure 9.2. Notice here that the last step shows several possible packages that can be run to assign or change the assignment of resource groups. The first package listed, DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP, must be run the first time a user is assigned to a resource group or you won’t be able to assign the user to the group. After the user has been given the SWITCH_CONSUMER_GROUP system privilege, you don’t have to rerun the package. Source 9.1 shows the code to manually create the resource plan in Figure 9.2. Listing 9.2 shows the results from running the source in Source 9.1.

SOURCE 9.1 Script to create a sample resource plan.

set echo on
spool test_resource_plan.doc
— Grant system privilege to plan administrator
—
execute
dbms_resource_manager_privs.grant_system_privilege('SYSTEM','ADMINISTER_RESOURCE_
MANAGER',TRUE);
—
—connect to plan administrator
—
CONNECT system/system_test@ortest1.world
—
— Create Plan Pending Area
—
EXECUTE dbms_resource_manager.create_pending_area();
—
— Create plan
—
execute dbms_resource_manager.create_plan('MASTER','Example Resource Plan','EMPHASIS');
execute dbms_resource_manager.create_plan('USERS','Example Resource Sub
Plan','EMPHASIS');
execute dbms_resource_manager.create_plan('REPORTS','Example Resource Sub
Plan','EMPHASIS');
—
—Create tiers of groups in plan
—
EXECUTE dbms_resource_manager.create_consumer_group('ONLINE_USERS','3rd level
group','ROUND-ROBIN');
EXECUTE dbms_resource_manager.create_consumer_group('BATCH_USERS','3rd level
group','ROUND-ROBIN');
EXECUTE dbms_resource_manager.create_consumer_group('ONLINE_REPORTS','2rd level
group','ROUND-ROBIN');
EXECUTE dbms_resource_manager.create_consumer_group('BATCH_REPORTS','2rd level
group','ROUND-ROBIN');
—
— Create plan directives
—
EXECUTE dbms_resource_manager.create_plan_directive('MASTER', 'USERS',
0,60,0,0,0,0,0,0,NULL);
EXECUTE dbms_resource_manager.create_plan_directive('MASTER', 'REPORTS',
0,20,0,0,0,0,0,0,NULL);
EXECUTE dbms_resource_manager.create_plan_directive('MASTER','OTHER_GROUPS',
0,20,0,0,0,0,0,0,NULL);
EXECUTE dbms_resource_manager.create_plan_directive('USERS', 'ONLINE_USERS', 0,0,70,0,0,0,0,0,NULL);
EXECUTE dbms_resource_manager.create_plan_directive('USERS', 'BATCH_USERS',
0,0,30,0,0,0,0,0,NULL);
EXECUTE
dbms_resource_manager.create_plan_directive('REPORTS','ONLINE_REPORTS',0,0,70,0,0,0,0,0,NULL);
EXECUTE dbms_resource_manager.create_plan_directive('REPORTS','BATCH_REPORTS',
0,0,30,0,0,0,0,0,NULL);
—
— Verify Plan
—
EXECUTE dbms_resource_manager.validate_pending_area;
—
— Submit Plan
—
EXECUTE dbms_resource_manager.submit_pending_area;
spool off
set echo off

Notice that the script in Source 9.1 follows the chart in Figure 9.2. These are the proper steps to create a resource plan.

LISTING 9.2 Example of a script run to create a sample resource plan.

SQL> -- Grant system privilege to plan administrator
SQL> --
SQL> execute
dbms_resource_manager_privs.grant_system_privilege('SYSTEM','ADMINISTER_RESOURCE_MANAGER',TRUE);