High performance database deployment relies on effective management of system resources. Only Oracle Exadata provides fully integrated management of system resources including Oracle databases, server compute resources, and I/O.
This blog and accompanying video show how Consumer Groups can be used in Oracle Resource Manager for fine-grained control of resource usage by users, groups of users, applications, and other criteria.
This demo video shows how to manage resources WITHIN a database using Consumer Groups. Smaller, less critical databases will typically use resource applications for the entire database, without fine grained control of resources inside the database. However, larger, more critical databases such as Data Warehouses or critical OLTP systems might also require management of resources to ensure critical business processes are completed in a timely manner or critical users receive the proper amount of resources to complete their work in the database.
The full demo can be viewed here: https://youtu.be/UhfHLZ0sU8E
Our two previous demos showed how Resource Manager is used for managing resources between databases to guard against the "Noisy Neighbor" problem and ensure each database receives the system resources it requires to meet business needs. This demo shows how to use Consumer Groups, which consist of 3 components that allow administrators to manage resources within a database:
Consumer Groups serve as an identifier that provides a linkage between users (or other consumers) and system resources. Consumer Group Mappings are rules the database executes when connections are established to the database and place that connection under a Consumer Group. Of course database connections can be for users, but also for batch jobs or other processes that act upon the database, and mapping rules cover all connections regardless of their source. Finally, Resource Plans control the resources assigned to each Consumer Group.
Resource Plans for Consumer Groups are based on the concept of shares and limits, which provides a minimum guarantee (or share) of resources for each Consumer Group, while also allowing Consumer Groups to use any idle resources (up to their limit). For example, Extract Transform and Load jobs (ETL) might have a high share of resources, but might also need to be limited in the amount of resources they consume.
In this sample screenshot from Oracle Enterprise Manager (OEM), the Resource Plan does not include utilization limits. This means that each consumer group is able to use ALL of the system resources if other groups are not using those resources at a given point in time. Utilization Limits should be configured in cases where users need a more consistent experience regardless of other activity in the database.
Exadata I/O Resource Manager (IORM) can be configured to simply inherit the same resource ratios used for Database Resource Manager (DBRM) by using IORM Objective "auto". For example, if a Consumer Group is supposed to have 25% of compute resources (CPU) on a system, that Consumer Group should (in most cases) also have 25% of I/O resources. A noisy neighbor can consume excessive CPU, but might also consume excessive I/O on a system. It's important to control both, and using a single setting works best. The following screenshot from OEM shows the running sessions for each consumer group.
For systems configured with Exadata IORM Objective "auto", the same resource ratios from the database are applied to the storage level as well. This means a single set of controls are used to govern resources on the system, making the system simpler to manage.
Parallel statement queueing is used to increase overall throughput by allocating more resources to queries in turn rather than spreading those resources across multiple sessions simultaneously. In other words, this is reducing concurrency (through queueing) but increases overall throughput. Parallel Statement Queueing is a feature of Oracle Database Resource manager, and database administrators can easily see how it is performing through Oracle Enterprise Manager (OEM).
Each consumer group is allowed a certain share of Parallel Query resources. Parallel Statement Queueing (if configured) will queue statements if the Consumer Group exceeds the provisioned amount of Parallel Servers.
Runaway queries are managed by setting resource limits and taking action when those limits are exceeded. The action to take when resource limits are exceeded include the following:
Sessions can be switched into a lower consumer group if the session runs a query exceeding the resource limits. Cancelling SQL statements or killing sessions is a much more drastic measure to take, but might be appropriate in a ad-hoc query environment. The "log only" action is useful for investigating design of a runaway query management strategy.
Automatic Consumer Group Switching can be used to downgrade priority of queries that exceed resource consumption rules. The following graphic shows an example of this approach:
Consumer Groups are assigned a share or percentage of resources within a database, while resource shapes are applied to each database within a server, virtual machine, or cluster. Resource Shapes define the overall resources allocated to a particular database. It is important to note that Resource Shapes are typically used in Database Consolidation environments,
Resource Shapes can also be used to prevent a single database from overrunning dedicated resources as well. Database Resource Shapes are outlined in the Oracle Maximum Availability Architecture (MAA) Best Practices for Database Consolidation white paper available here:
Oracle Resource Manager provides the most flexible method for managing system resources across databases, as well as within databases using Consumer Groups. Resources can be easily allocated and managed at any level required to meet business needs. Exadata I/O Resource Manager (IORM) is fully integrated with Oracle Database Resource Manager (DBRM) to provide a single set of integrated controls that govern use of system resources. Resource Management is designed to prevent the "Noisy Neighbor" problem in multi-user and multi-database (consolidation) environments. Resource Manager also guards against over-use of resources even in dedicated environments, providing improved system stability and availability.