For more control over how resources are allocated when multiple database workloads are running in a small or medium configuration, we used the Oracle Database Resource Manager.
We followed these practices:
- Allowed a database to use different resource plans, based on changing workload requirements
- Limited the degree of parallelism of any operation performed by members of a group of users
- Distributed available CPU by allocating percentages of CPU time to different resource group users
- Managed runaway sessions by detecting when the session consumed more than a specified amount of CPU and then automatically switching to a consumer group with a lower resource allocation or a limit on the percentage of CPU that the group can use
The following process shows how we customized a resource plan named “DSS_PLAN” for the DSS resource group. This plan switched any session in the DSS resource group to the OTHER_GROUPS consumer group if the session exceeded the CPU allocation. This example prevented long queries in the OLAP database from consuming too many resources. The OTHER_GROUPS consumer group that was switched to is one with lower resource allocation.
- In Oracle Enterprise Manager Cloud Control 13c, under Administration, click resource manager.
- As shown in Figure 64, in the view resource plan window, select DSS_PLAN.
- Under DSS_PLAN, select the DSS_GROUP consumer group into which we added Swingbench user SH and the OTHER_GROUP consumer group, which is the consumer group we switch to when the session from DSS_GROUP consumes too many resources.
- Set the utilization limit% for the consumer group.
In following example, we keep the default value for Utilization Limit % for SYS_GROUP, which is 90%. We set the value to 10% for the OTHER_GROUPS consumer group, which has a limit on the percentage of I/O that the group can use.
Figure 64. Customizing resource manager plan DSS_PLAN by setting utilization limit percentage
- As shown in Figure 65, click Runaway Query, select DSS_GROUP, and type a number for I/O Limit (MB) for this consumer group. In this example, we set the I/O limit to 450 MB/s. We also chose the Switch to Group OTHER_GROUPS action, which manages runaway sessions by automatically switching the DSS_GROUP consumer group to the OTHER_GROUPS consumer group when the session consumes more than a specified amount of I/O.
Figure 65. Customizing resource manager plan DSS_PLAN by setting I/O limit
- Return to the view resource plan window and activate the resource plan DSS_PLAN, as shown in Figure 66.
Figure 66. Activating the DSS_PLAN Resource Plan