BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

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);


PL/SQL procedure successfully completed.

SQL> --
SQL> --connect to plan administrator
SQL> --
SQL> CONNECT system/system_test@ortest1.world
Connected.
SQL> --
SQL> -- Create Plan Pending Area
SQL> --
SQL> EXECUTE dbms_resource_manager.create_pending_area();

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Create plan
SQL> --
SQL> execute dbms_resource_manager.create_plan('MASTER','Example Resource
Plan','EMPHASIS');

PL/SQL procedure successfully completed.

SQL> execute dbms_resource_manager.create_plan('USERS','Example Resource Sub Plan','EMPHASIS');

PL/SQL procedure successfully completed.

SQL> execute dbms_resource_manager.create_plan('REPORTS','Example Resource Sub Plan','EMPHASIS');

PL/SQL procedure successfully completed.

SQL> --
SQL> --Create tiers of groups in plan
SQL> --
SQL> EXECUTE dbms_resource_manager.create_consumer_group('ONLINE_USERS','3rd level
group','ROUND-ROBIN');

PL/SQL procedure successfully completed.

SQL> EXECUTE dbms_resource_manager.create_consumer_group('BATCH_USERS','3rd level
group','ROUND-ROBIN');

PL/SQL procedure successfully completed.

SQL> EXECUTE dbms_resource_manager.create_consumer_group('ONLINE_REPORTS','2rd level
group','ROUND-ROBIN');

PL/SQL procedure successfully completed.

SQL> EXECUTE dbms_resource_manager.create_consumer_group('BATCH_REPORTS','2rd level
group','ROUND-ROBIN');

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Create plan directives
SQL> --
SQL> EXECUTE dbms_resource_manager.create_plan_directive('MASTER', 'USERS',   
  0,60,0,0,0,0,0,0,NULL);

PL/SQL procedure successfully completed.

SQL> EXECUTE dbms_resource_manager.create_plan_directive('MASTER', 'REPORTS',   
0,20,0,0,0,0,0,0,NULL);

PL/SQL procedure successfully completed.

SQL> EXECUTE dbms_resource_manager.create_plan_directive('MASTER','OTHER_GROUPS',   0,20,0,0,0,0,0,0,NULL);

PL/SQL procedure successfully completed.

SQL> EXECUTE dbms_resource_manager.create_plan_directive('USERS',  'ONLINE_USERS',  0,0,70,0,0,0,0,0,NULL);

PL/SQL procedure successfully completed.

SQL> EXECUTE dbms_resource_manager.create_plan_directive('USERS',  'BATCH_USERS',   0,0,30,0,0,0,0,0,NULL);

PL/SQL procedure successfully completed.

SQL> EXECUTE dbms_resource_manager.create_plan_directive('REPORTS','ONLINE_REPORTS',0,0,70,0,0,0,0,0,NULL);

PL/SQL procedure successfully completed.

SQL> EXECUTE dbms_resource_manager.create_plan_directive('REPORTS','BATCH_REPORTS', 0,0,30,0,0,0,0,0,NULL);

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Verify Plan
SQL> --
SQL> EXECUTE dbms_resource_manager.validate_pending_area;

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Submit Plan
SQL> --
SQL> EXECUTE dbms_resource_manager.submit_pending_area;

PL/SQL procedure successfully completed.

SQL> spool off

The other operations allowed against the components of the resource plan are ALTER and DROP. Let’s look at a drop example in Source 9.2.

SOURCE 9.2 Example of a drop procedure.

EXECUTE dbms_resource_manager.delete_plan('MASTER');
EXECUTE dbms_resource_manager.delete_plan('USERS');
EXECUTE dbms_resource_manager.delete_plan('REPORTS');
--
--delete tiers of groups in plan
--
EXECUTE dbms_resource_manager.delete_consumer_group('ONLINE_USERS');
EXECUTE dbms_resource_manager.delete_consumer_group('BATCH_USERS');
EXECUTE dbms_resource_manager.delete_consumer_group('ONLINE_REPORTS');
EXECUTE dbms_resource_manager.delete_consumer_group('BATCH_REPORTS');

Notice that you must drop all parts of the plan. This is because Oracle allows the existence of orphan groups and plans. As you can tell from looking at the scripts, the DBMS_RESOURCE_MANAGER and DBMS_RESOURCE_MANAGER_PRIVS packages are critical to implementing Oracle resource groups. Let’s examine these packages.


See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

   
 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter