A big challenge DBAs face is aligning database resources with the real-world demands of daily business and realigning those resources as demands change—especially in terms of meeting SLAs (service-level agreements) with internal and external customers. Oracle Database Resource Manager, introduced in Oracle8i and improved with each subsequent release, provides new capabilities that make using it a viable approach to managing a fundamental system resource, the CPU, and how the CPU allocates its cycles to the processes running on the system.
Note that the concurrent use of Database Resource Manager with OS resource managers is supported only for utilities that use technologies such as processor sets or domains to isolate each database instance into its own "virtual partition." For more information, see MetaLink or the Oracle Database Administrator's Guide.
Overview of Oracle Database Resource Manager
Oracle Database Resource Manager takes a database-instance-centric approach to managing resources, letting DBAs apply business-operational policies to the system so that resources will be allocated to various user communities and applications in the context of the entire system, adjusting the balance according to user-defined rules.
The rules, or directives, are defined in a resource plan and apply to specific groups of users—consumer groups—that you also define. You can define many different resource plans—one for daytime operations, one for nighttime operations, for example—but only one can be active per database instance at any moment. (By default, Oracle Database Resource Manager is not enabled; it remains disabled until you select an active resource plan for your system. Also note that because Database Resource Manager works at the instance level, in an Oracle Real Application Clusters environment, you must enable it on each instance in the cluster.)
A resource plan can encompass numerous consumer groups and subplans, letting you finely control resource allocation. You can create and manage all the elements of Oracle Database Resource Manager—resource plans, resource consumer groups, and directives—with Oracle Enterprise Manager Database Control (or Grid Control, if you're running Oracle Real Application Clusters) or the DBMS_RESOURCE_MANAGER built-in PL/SQL package.
Getting Started with a Resource Plan
You'll find the Resource Manager section on the Oracle Enterprise Manager Database Control Administration page (click on the Administration link on the Oracle Enterprise Manager Database Control home page to get to the Administration page. The Resource Manager menu is adjacent to the Scheduler). Starting from this page, you can perform the administration tasks for setting up the necessary elements and monitor how an active plan is allocating resources at any moment (on the Resource Monitors page).
Before creating a new plan, you must create a few Consumer Resource Groups (click on Resource Consumer Groups on the menu under Resource Manager on the Administration page). The Resource Consumer Group page lists any existing groups (some are built-in defaults), and you can click on the Create button to create a new group and give it a name and a description, upon which you'll see all the user (schema) names in your system displayed. Until you move users into groups you create, most users in the system are members of the DEFAULT_CONSUMER_GROUP. (Another built-in group, SYS_GROUP, contains the SYSTEM user).
The plan displays the list of consumer groups in our system—CUSTOMERS and BATCH_APPS—and an Oracle-provided consumer group (OTHER_GROUPS). The CUSTOMERS consumer group comprises user accounts with strict SLA requirements—in this case, user accounts associated with purchasing applications. The BATCH_APPS consumer group comprises applications that print invoices and statements. Typically these run at night, but occasionally they are run during the day, time permitting. OTHER_GROUPS is mandatory on every resource plan and is not editable—it exists to allocate resources to processes, such as background processes, that are not accounted for in any other group.
How the Plan Affects Utilization
Consumer groups share system resources, using percentages specified in the plan. CUSTOMERS have been allocated 85 percent of system resources and BATCH_APPS 10 percent; OTHER_GROUPS has been allocated 5 percent. The plan is a simple single-level plan, with all allocations at level 1. You can, however, devise multilevel plans in which level-2 consumer groups (and groups on subsequent levels) receive their allocations only after level-1 consumer groups have received their full specified allocation of resources. (See the Oracle Database Administrator's Guide for details about constructing such plans.)
You can define plans to account for a variety of scenarios, ensuring that batch job processing won't interfere with customers trying to buy products, for example, and that distinct requirements can always be met.
By default, consumer groups share resources as percentages (you can also define simple ratios). However, if CUSTOMERS are ever the only type of consumer using the system, they will get 100 percent of the CPU, not just 85 percent. That's because Oracle Database Resource Manager is designed to maximize Oracle Database throughput rather than arbitrarily enforce allocations; that is, until the CPU or multiple CPUs are fully taxed—reach 100 percent utilization—the allocations don't come into play.
More About Resource Plan Directives
The plan is fairly straightforward, and we didn't change any directives from their default settings (null values). However, using directives lets us build even-more-powerful plans that manage resources proactively and automatically. For example, the max_idle_time directive (whose null default value means unlimited) can be set for a consumer group to ensure that sessions aren't idle beyond the limit set for that group, freeing resources for serving other consumer groups in the plan (or other sessions in the consumer group).
You set plan directives by clicking on the appropriate link on the Resource Plan Detail page; clicking on one displays the appropriate page of options available, with consumer groups and subplans.
Dynamically Switching Sessions into Groups
Several directives available on the Group Switching page (or available through the API) can be combined in various ways in a resource plan to automate your operations.
In Oracle Database 10g, the switching capability has been enhanced, with the switch_time_in_call and switch_time directives. The former sets how long, in seconds, a session can execute before switching to a new consumer group. At the end of the call, the session's consumer group is restored to the previous one. You can implement these directives in a plan with the DBMS_RESOURCE_MANAGER API. Listing 1 shows a slightly modified version of the resource plan we created with Oracle Enterprise Manager, implementing the switch_time_in_call directive for an additional group, WEB_CUSTOMERS.
The switch_time_in_call directive is especially useful in multitier applications in which a middle-tier application server has implemented session pooling. With switch_time_in_call, one client's resource consumption won't affect a future client executed in the same session.
Before setting up the directives for switching in a resource plan, grant the consumer group the privilege to switch into the target consumer group. (Actually, you can set up the consumer group to switch into, but this is ineffective until you grant the proper permission.) To grant permission to switch consumer groups, use the DBMS_ RESOURCE_MANAGER_PRIVS package, as in this example:
begin
dbms_resource_manager_privs
.grant_switch_consumer_group
('WEB_CUSTOMERS', 'CUSTOMERS', true);end;
In Listing 1, members of the consumer group WEB_CUSTOMERS are switched to the CUSTOMERS consumer group if the request they're making will exceed five seconds—they're moved into the higher-priority CUSTOMERS group until the request is processed. When the call completes, the user is moved back into the WEB_CUSTOMERS group.
Conclusion
Operating-system-level resource managers do a good job in many circumstances but often don't provide effective control of a system focused primarily on running an Oracle database instance. With new capabilities, such as the ability to switch automatically into and out of groups, Oracle Database Resource Manager is a tool to seriously consider using, especially for running a highly loaded system with a variety of applications and for meeting demanding SLAs.
REFERENCES
Database Resource Manager By Kimberly Floss, Oracle Corporation
No comments:
Post a Comment