Your Browser is Out of Date

ShareDemos uses technology that works best in other browsers.
For a full experience use one of the browsers below
Configuration Details
Database performance data collection Database parameters AppSync identification of VMAX volumes SwingBench parameters Resource allocation management for databases

Configuration Details

Resource allocation management for databases

  • 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.

    1. In Oracle Enterprise Manager Cloud Control 13c, under Administration, click resource manager.
    1. As shown in Figure 64, in the view resource plan window, select DSS_PLAN.
      1. 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.
      2. 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.

    C:\Users\linm7\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\88RZEQIG\resource1.PNG

    Figure 64.     Customizing resource manager plan DSS_PLAN by setting utilization limit percentage

    1. 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.

    C:\Users\linm7\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\88RZEQIG\resource2.PNG

    Figure 65.          Customizing resource manager plan DSS_PLAN  by setting I/O limit

    1. Return to the view resource plan window and activate the resource plan DSS_PLAN, as shown in Figure 66.

    C:\data\Office\Project\Ready Bundle for Oracle VMAX 250F PRD\pic\pic_resource_manager\Capture_db15ap_DDS_PLAN.PNG

    Figure 66.     Activating the DSS_PLAN Resource Plan