Oracle Database Resource Manager (DBRM)-Exadata

 The Oracle Database Resource Manager allows

The database more control over how hardware resources are allocated. In an environment with multiple concurrent user sessions that run jobs with differing priorities, all sessions should not be treated equally. The Resource Manager enables you to classify sessions into groups based on session attributes, and to then allocate resources to those groups in a way that optimizes hardware utilization for your application environment.

With the Resource Manager, you can:

• Guarantee certain sessions a minimum amount of processing resources regardless of the load on the system and the number of users.
• Dstribute available processing resources by allocating percentages of CPU time to different users and applications. In a data warehouse, a higher percentage can be given to ROLAP (relational online analytical processing) applications than to batch jobs.
• Limit the degree of parallelism of any operation performed by members of a group of users.
• Create an active session pool. An active session pool consists of a specified maximum number of user sessions allowed to be concurrently active within a group of users. Additional sessions beyond the maximum are queued for execution, but you can specify a timeout period, after which queued jobs will terminate. The active session pool limits the total number of sessions actively competing for resources, thereby enabling active sessions to make faster progress.
•   Manage runaway sessions or calls in the following way
  ==> By placing an absolute limit on the percentage of CPU that a group can consume
  ==>By detecting when a session or call consumes more than a specified amount of CPU or I/O, and then automatically either terminating the session or call, or switching it to a consumer group that is allocated a small amount of CPU, which would in effect mitigate the impact of the runaway session or call
• Prevent the execution of operations that the optimizer estimates will run for a longer time than a specified limit.
•   Limit the amount of time that a session can be idle. This can be further defined to mean only sessions that are blocking other sessions.
• Configure an instance to use a particular scheme for allocating resources. You can dynamically change the scheme, for example, from a daytime scheme to a nighttime scheme, without having to shut down and restart the instance.

Thus, the Oracle Database Resource Manager facility allows a more granular definition of user categories (“consumer groups”) and the allocation of resources.  It also has the ability to detect and halt “runaway” queries.  This is particularly useful when ad hoc access to the database is provided.

DBRM

Oracle’s Database Resource Manager (DBRM) allows DBAs to address resource allocation within the domain of the database itself.  It is a well-organized, framework that is automated by design. It ensures that critical system resources like CPU and I/O will be available to the important applications whenever they are needed, even when the system is under a heavy workload. This is done by creating resource allocation schemes that define priorities based on the needs of the business.

Remember that Resource Management is a “Gate Keeper”, not an “Accelerator”.  It is used to make sure that you live within the confines of the available capacity limits while maintaining consistent performance.  Resource Management kicks in whenever you are hitting the capacity limits of the server.  If this is happening frequently, you may need to seriously consider expanding your capacity.

Services are the highest level component when defining workload management, but at a more granular control may be taken within a specific database by separating users into consumer groups. Once those groups are in place, the database resource manager (DBRM) will ensure that appropriate level computing resources are made available to each group.  Multiple resource plans can be defined allowing the database to shift between those plans in order to adapt to changing processing patterns such as nightly ETL and month-end reporting.

For systems that are processing multiple jobs at any given time, parallelism can be a beneficial tool or, if unconstrained, also be an impediment to good system performance.  DBRM allows the administrator to set a maximum degree of parallelism for users in a consumer group to prevent over-subscription of certain resources. 

DBRM also provides a way of dynamically moving queries from one resource plan to the other before it executes.  Allocation of resources and switching before the statement actually starts is beneficial as it helps with getting the right resources assigned from the start.  When executed properly it also helps with runaway query prevention [i.e. Cartesian joins] by choosing the right group (resource profile) for the statement.

The following illustration shows the execution process by which a dynamic switching approach can allocate resources based on query execution time. 

Three groups are defined; one group is for "long running" queries, the second for "medium" and the third for "short". The defined running time for each of these groups are pre-defined from business user requirements. If groups are set for 10, 100 and 1000 seconds respectively, then that is the query timing which will be used as the switching boundary.

When a user submits a statement the database cost-based optimizer (CBO) determines the estimated execution time duration of the statement.  The DBRM then assigns the query to a specific resource group allowing the query to run with the appropriate allocated database and system resources.

  DBRM Strategy

Resource Manager

The Database Resource Manager (DBRM) is geared toward managing CPU resources and indirectly the disk I/O on the database tier.  The I/O Resource Manager (IORM) is geared toward managing and prioritizing I/O at the storage cell level.  When databases on Exadata request I/O from the storage cells, they send additional information along with those requests which identifie the database making the request as well as the consumer group making the request.  The software on the storage cells knows about the priorities you established inside the database (DBRM) and/or at the Storage Cell (IORM), and it manages how I/O is scheduled.  DBRM and IORM are tightly linked together, so knowing about DBRM is the foundation to understanding IORM.   

The Resource Manager components are listed in the following table: 

Resource consumer group / Consumer group	These are the various names by which you may see resource consumer groups referred.  Resource Manager allocates resources to consumer groups, rather than user sessions.  A consumer group is a set of database sessions that may be grouped together based on their priority, and/or resource requirements.
Resource plan directive / Plan directive / Directive	These are the names by which you may see resource plan directives referred.  Resource allocations are not assigned directly to consumer groups. They are defined in a resource plan directive.  A consumer group is then assigned to the plan directive so that resource allocations may be enforced.
Resource plan / Plan	Resource plans are sometimes referred to simply as “plans”, or “the plan.”  Plan directives are grouped together to create a resource plan, thus defining the overarching allocation of resources to all sessions within the database.

Consumer groups: A consumer group can represent a single database session or a group of sessions. Generally speaking, consumer groups consist of end users or application groups that share a common business priority. Grouping sessions together in this manner allow resources to be assigned and managed collectively.

In a mixed-workload database environment, consumer group assignments allow you to collectively assign more CPU and I/O resources to your high-priority business applications while reducing the resources allocated to low priority applications.

Plan directives: Plan directives are where you define your resource allocations. A plan directive is created using the CREATE_PLAN_DIRECTIVE procedure. In addition to defining the resource allocation itself (percentage of CPU, for example), the procedure also requires you to name one resource plan and one consumer group.

In this way, a plan directive “links,” or assigns the consumer groups to a resource plan.

Only one consumer group may be assigned to a directive, and resource plans typically consist of multiple directives. You can say that a consumer group is assigned to a resource plan through the creation of a plan directive.

Resource plan: The resource plan is the collection of directives that determine how and where resources are allocated. Only one plan may be activated in the database instance at any given time. The resource plan is activated when assigning it to the

RESOURCE_MANAGER_PLAN instance parameter.

For example, the following alter system command activates the resource plan ‘myplan’:
SQL> alter system set resource_manager_plan=’myplan’;

A database’s resource plan may be changed at any time, allowing you to reallocate resources at various times of the day, week, or month in order to meet the varying workload requirements of your business. When the resource plan is activated, no resources are allocated to individual user sessions. Instead, resources are allocated to the consumer groups according to the directives in the resource plan.

Consumer Groups

When a resource plan is activated, DBRM examines key attributes of all sessions in the database and assigns them to consumer groups. Sessions are assigned to consumer groups by means of mapping rules that you define. For example, a set of user accounts can be mapped to a consumer group based on their user name or the machine from which they are logging in. A user may belong to many different consumer groups and may be dynamically reassigned from one consumer group to another even in the middle of executing a SQL statement or query. Since database resources are allocated only to consumer groups, reassigning a session to another group immediately changes its resource allocation. All sessions in a consumer group share the resources of that group. For example, if the APPS consumer group is allocated 70% of the total CPU on the server, all sessions belonging to that consumer group will equally share the 70% allocation of CPU. There are two built-in consumer groups in every database:

SYS_GROUP and OTHER_GROUPS: These groups cannot be modified or dropped.

SYS_GROUP: This is the default consumer group for the SYS and SYSTEM user accounts. These accounts may be assigned to another consumer group using mapping rules we’ll discuss in the next section.

OTHER_GROUPS: This is the default consumer group. Any sessions that belong to a consumer group that is not defined in the currently active resource plan will automatically be mapped to this consumer group. This is Oracle’s way of making sure all user sessions are assigned to a consumer group in the active resource plan.

Consumer Group Mapping Rules

All user sessions (except SYS and SYSTEM) are mapped by default to the consumer group OTHER_GROUPS. This behavior can be modified using mapping rules so that sessions are automatically reassigned to other consumer groups. If there is no mapping rule for a session, or if the mapping rules assign the session to a consumer group that is not named in the currently active plan, then it will be automatically assigned to this built-in consumer group. Every resource plan must have the OTHER_GROUPS resource group to handle this condition. The following example shows how a mapping rule is created. This mapping rule calls for the TPODER account to be automatically assigned to the REPORTS consumer group, while anyone logging in using the Payroll.exe application will be mapped to the APPS consumer group:

BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'TPODER', 'REPORTS');
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM, 'payroll.exe', 'APPS');
END;

There are two types of session attributes that can be used to create mapping rules: login attributes and runtime attributes. Login attributes are set when the user logs in and do not change during the life of the session. Resource Manager uses login attributes to determine which consumer group the session should initially be assigned to. Runtime attributes are set at runtime and can be changed at any time during the life of the session by the client application. Table 7-2 describes the session attributes Resource Manager can use for creating session-to-consumer-group mapping rules.

Session Attribute	Type	Description
EXPLICIT	N/A	This attribute refers to the explicit request by a user to switch to another consumer group using one of the following stored procedures in the DBMS_SESSION package: SWITCH_CURRENT_CONSUMER_GROUP SWITCH_CONSUMER_GROUP_FOR_SESS SWITCH_CONSUMER_GROUP_FOR_USER
ORACLE_USER	Login	This is the USERNAME column from v$session. It is the user name the session used to authenticate to the database during login.
SERVICE_NAME	Login	This is the database service name used to connect to the database. It is the SERVICE_NAME column in the v$session view.
CLIENT_OS_USER	Login	This is the operating system user account of the machine the user is connecting from. It is the OSUSER column from the v$session view.
CLIENT_PROGRAM	Login	This is the executable file the end user is using to connect to the database; for example, sqlplusw.exe. Resource Manager evaluates this value without consideration of case, (upper or lower).
CLIENT_MACHINE	Login	This is the machine from which the user is connecting to the database. It appears in the MACHINE column of the v$session view.
MODULE_NAME	Runtime	This is the module name set by the application connecting to the database. It is stored in the MODULE column of the v$session view and is set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure. This is an optional setting, and some applications do not use it.
MODULE_NAME_ACTION	Runtime	This is a concatenation of the module and action in the form module.action. The application sets these attributes by calling the following procedures: DBMS_APPLICATION_INFO.SET_MODULE          DBMS_APPLICATION_INFO.SET_ACTION
SERVICE_MODULE	Runtime	This attribute is the concatenation of the service name used to connect to the database, and the MODULE attribute in the form service.module.
SERVICE_MODULE_ACTION	Runtime	This attribute is the concatenation of the service name, module, and action in the form service.module.action.
ORACLE_FUNCTION	Runtime	This is a special attribute that is maintained internally by the database. It is set when running RMAN or Data Pump. This attribute can be set to BACKUP, to perform a backup … as backupset, or COPY, to perform a backup … as copy. When Data Pump is used to load data into the database, this attribute is set to DATALOAD. These attributes are automatically mapped to built-in consumer groups such as BATCH_GROUP and ETL_GROUP.

It is a common practice to set the priority of this mapping attribute to the highest level.

Tip: For attributes other than ORACLE_USER and SERVICE_NAME in the previous table, you can also use wildcards such as _ and % for single and multiple characters, respectively. Conflicts can occur between mapping rules when a user account matches more than one rule. Oracle resolves these conflicts by allowing you to specify the relative priority of each attribute. In this way, Oracle can automatically determine which rule (attribute) should take precedence when session attributes satisfy multiple mapping rules. The default priorities for the ORACLE_USER and CLIENT_APPLICATION attributes are 6 and 7, respectively.

In the following code example, the CLIENT_PROGRAM promoted to position 2, and the ORACLE_USER placed in position 3. Now the client application establishing a database connection will take precedence over the client’s USERNAME.

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping_pri(
EXPLICIT => 1,
CLIENT_PROGRAM => 2,
ORACLE_USER => 3,
SERVICE_MODULE_ACTION => 4,
SERVICE_MODULE => 5,
MODULE_NAME_ACTION => 6,
MODULE_NAME => 7,
SERVICE_NAME => 8,
CLIENT_MACHINE => 9,
CLIENT_OS_USER => 10 );
dbms_resource_manager.submit_pending_area();
END;

Plan Directives

DBRM allocates database resources to consumer groups through plan directives. A plan directive consists of one consumer group and one or more management attributes. There is a one-to-one relationship between a plan directive and a consumer group, and no two directives may be assigned to the same resource group (within the same plan). A plan directive is made up of a list of management attributes in a key=value fashion. For example, the following listing shows how a set of directives may be defined in DBRM:



DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(
PLAN => 'example_plan',
GROUP_OR_SUBPLAN => 'APPS',
COMMENT => 'OLTP Application Sessions',
MGMT_P1 => 70,
MAX_UTILIZATION_LIMIT => 90,
MAX_EST_EXEC_TIME => 3600
);

MGMT_Pn

Exadata’s IO Resource Manager works with DBRM through plan directives using the CPU management attributes MGMT_Pn, (where n may be 1–8), and MAX_UTILIZATION_LIMIT. Do to their important interface these attributes will be reviewed. CPU is allocated in a level + percentage manner. Usage of the MGMT_Pn attribute determines the relative priority in which CPU is allocated to consumer groups across the various levels, where 1 is the highest level/priority. The percentage assigned to the MGMT_Pn attribute determines how available CPU resources (unallocated plus unused) are allocated within a particular level. Whatever CPU is unused or unallocated from level 1 is allocated to level 2. Unused and unallocated CPU from level 2 is then passed to the consumer groups on level 3. If there are two consumer groups on level 2 and one of them doesn’t use its allocation, the unused CPU is always passed to the next level in the Plan. The other consumer group on level 2 can’t utilize it. You borrow from the higher level.

The following example shows a simple resource plan and illustrates how this level + percentage method of allocating CPU resources works.



The APPS group is allocated 70% of total CPU available to the database. Sessions in the REPORTS group are the next highest priority at level 2 and will be allocated half of the unallocated CPU (30%) from level 1. Sessions in the resource groups MAINTENANCE and OTHER_GROUPS equally share unallocated CPU (50%) from level 2. This can be expressed in formula form as follows:

APPS = 70% (100% × 70%)
REPORTS = 15% ((100% – 70%) × 50%)
MAINTENANCE = 7.5% (((100% 70%) × 50%) × 50%)
OTHER_GROUPS = 7.5% (((100% 70%) × 50%) × 50%)

Resource Manager is designed to maximize CPU utilization. This is important to understand because it means that there are times when consumer groups may actually exceed their allocation. When CPU resources are limited, plan directives define guaranteed service levels for consumer groups. But when extra CPU is available, plan directives also determine how unused CPU resources are allocated among consumer groups.

As an example, if CPU utilization in the APPS group falls below 70%, half of the unused CPU is redistributed to the REPORTS group on level 2 (mgmt._p2=50%), and half is distributed to the consumer groups on level 3. If the REPORTS group does not fully utilize its allocation of CPU, the unused CPU is also redistributed to the consumer groups on level 3. If you need to set an absolute limit on CPU for a consumer group, use the MAX_UTILIZATION_LIMIT directive.

Resource Plan
A resource plan is a collection of plan directives that determine how database resources are to be allocated. You may create any number of resource plans for your database that allows you to meet the specific service levels of your business, but only one may be active at any given time. You may deactivate the current resource plan and activate another plan whenever the needs of the business change. When the active resource plan changes, all current and future sessions will be allocated resources based on directives in the new plan. Switching between various resource plans is commonly done to provide suitable allocations for particular times of the day, week, or month. For example, an after-hours plan may be activated in the evening to favor database backups, batch jobs, extracts, and ETL data-loading activities. Other applications for maintaining multiple plans may include month-end processing, year-end processing, etc.

The Pending Area
Resource plans in the database cannot be directly modified; nor can you directly define new plan directives or resource groups. Oracle provides a work space called the pending area for creating and modifying all the elements of a resource plan. You can think of it as a loading zone where all the elements of your resource plan are staged and validated together before they are submitted to DBRM.

There may be only one pending area in the database at any given time. If a pending area is already open when you try to create one, Oracle will display the error message, “ORA-29370: pending area is already active.” The pending area is not a permanent fixture in the database. You must explicitly create it before you can create or modify resource plans. The following listing shows the typical process of creating a pending area, validating your changes, and then submitting it. After the pending area is submitted, it is automatically removed and a new one must be created if you want to perform any additional work on DBRM components. The following listing shows how the Pending Area is created, validated, and submitted.

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); --Create the pending area
<create, modify, delete your resource plan>
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA(); --Validate your work
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA(); --Install your work into DBRM
END;

Resource Manager Views

Oracle supplies a number of views that report configuration, history, and metrics for Resource Manager. These views are useful for reviewing and monitoring resources in your DBRM configuration.

V$RSRC_CONSUMER_GROUP: The V$RSRC_CONSUMER_GROUP view displays information about the active resource consumer groups. It also contains performance metrics that are useful for tuning purposes. We’ll take a closer look at this view when we test a resource plan later on in the chapter.

V$RSRC_PLAN: This view displays the configuration of the currently active resource plan.

V$RSRC_PLAN_HISTORY: The V$RSRC_PLAN_HISTORY view shows historical information
about your resource plans, including when they were activated and deactivated, and whether they were enabled by the database scheduler or scheduler windows.

V$RSRC_SESSION_INFO: This view shows performance statistics for sessions and how they were affected by the Resource Manager.

V$SESSION: The V$SESSION view is not specifically a Resource Manager view but its
RESOURCE_CONSUMER_GROUP field is useful for determining what resource group a session is assigned to.

DBA_RSRC_CATEGORIES: This view displays the resource categories that are configured in the database. Categories are used by the I/O Resource Manager for controlling storage cell I/O allocation within a database.

DBA_RSRC_CONSUMER_GROUP_PRIVS: This view reports users, and the resource groups to which they have been granted permission. A user must have permission to switch to a consumer group before the session-to-consumer group mapping rules will work.

DBA_RSRC_GROUP_MAPPINGS: This view lists all of the various session-to-resource group mapping rules defined in the database.

DBA_RSRC_MAPPING_PRIORITY: This view reports the priority of session attributes which
are used in resolving overlaps between mapping rules.

DBA_RSRC_IO_CALIBRATE: This view displays the I/O performance metrics DBRM uses for I/O resource management. Maximum read rates are captured for I/O operations per second (IOPS), megabytes per second (MBPS), and latencies for data block read requests.

DBA_RSRC_PLANS: This view lists all resource plans and the number of plan directives assigned to each plan in the database.

DBA_RSRC_PLAN_DIRECTIVES: This view lists all resource plan directives, resource allocation percentages, and levels defined in the database.

DBA_USERS: This view is not actually a Resource Manager view but it does display the username and initial resource group assignment in the INITIAL_RSRC_CONSUMER_GROUP column.

DBA_HIST_RSRC_CONSUMER_GROUP: This view displays historical performance metrics for Resource consumer groups. It contains AWR snapshots of the V$RSRC_CONS_GROUP_HISTORY view.

DBA_HIST_RSRC_PLAN: This is a simple view that displays historical information about resource plans such as when they were activated and deactivated.

       You can learn in detail on Exadata from book Expert Oracle Exadata

 ==========================================================

Please check our other blogs for Exadata.