I/O Resource Management on Exadata Engineered Systems

Version 3

    Overview

    The Oracle Exadata Database Machine allows the consolidation of multiple Oracle databases with mixed and disparate workloads. While hosting multiple databases on a single server and shared storage, fair utilization of disk bandwidth can be a challenging exercise. So, how can an application database hosted on an Exadata database machine, ensure predictable and consistent performance for OLTP workloads? How to manage the I/O resource allocation between mixed workloads? Oracle enables the Exadata administrators to manage the I/O resources through the Oracle Exadata I/O Resource Manager. This post will help you to understand the concepts of I/O resource allocation on Exadata database machines.

     

    Note: The features and concepts covered in this post have been validated against the Exadata Storage Software Release 12.1.2.1.


    Introduction

    Consolidation is a key enabler for Oracle database deployments on both public and private clouds. Consolidation reduces the overall IT costs by optimizing the operational and capital expenses. In addition, it enhances the effective utilization of cloud resources. The Exadata database machine has been optimized to run schemas and databases with mixed workloads together, making it the best platform for consolidation.

     

    Organizations follow different approaches to consolidate database instances. Some of the prominent approaches of consolidation are virtualization, schema based consolidation and database aggregation on a single high end physical server. Oracle Database 12c introduces Multitenant Architecture to allow secure consolidation of databases on cloud and achieve benefits like tenant isolation, manage many-as-one capability and enhanced customer satisfaction.

     

    For effective database consolidation, Exadata makes use of Oracle resource management (database resource management, network resource management and I/O resource management). The Exadata IORM enhances the stability of mission critical applications and ensures availability of all databases which share the server resources. The I/O resource plan provides the framework for queuing the low-priority requests while issuing high-priority requests. This post will focus on configuring, enabling and monitoring IORM plans on Exadata database machines.

     

    Oracle Database Resource Manager

    On a database server, the resources are allocated by the operating system, which may be inappropriate and inefficient in maintaining database health. The server stability and database instance is impacted by the high CPU load, thus resulting in sub-optimal performance of the database. Oracle Database Resource Manager, first introduced in Oracle Database 8i, can help you by governing the allocation of resources to the database instance and assuring efficient utilization of CPU resources on the server. It is a database module which allocates the resources to a resource consumer group as per a set of plan directives in a fair way. A resource consumer group comprises of database sessions with "like" resource requirements.

     

    A resource plan can manage the allocation of CPU, disk I/Os and parallel servers among schemas in a single database or multiple databases in a consolidated environment. An intra-database plan can be created to manage allocation across multiple schemas or services within a single database. On an Exadata database machine, the disk I/Os can be managed across multiple databases using IO Resource Manager (IORM) or inter-database plan. The Oracle Database Resource Manager is a feature of Oracle Database Enterprise Edition. However, starting with Oracle Database 11g, it can also be used in Standard Edition to manage maintenance tasks through a default maintenance plan.

     

    This post focuses on configuration of IORM plans on the Exadata database machine. In the post, we shall discuss how to manage disk I/Os, manage flash usage, manage standby database using IORM.


    Using Resource Manager for Schema Consolidation

    A single database with multiple schemas or running mixed workloads can use Oracle Database Resource Manager to control the allocation of CPU, I/Os and parallel servers among the schemas of a database. It can also be used to specify execution time limit for run-away queries and long running sessions or operations. The basic elements of the resource manager are the resource consumer group, a resource plan and the resource plan directive. A resource consumer group is a group of sessions which have similar resource requirements. A resource plan is a box of directives which specify the allocation for a consumer group. A plan directive connects the consumer group to the plan with a percentage. The process flow diagram of configuring and enabling a resource plan is as below.

    Fig1.png

    Figure 1: Steps to configure an Oracle Database Resource Manager plan


    How does the resource manage work?                                                             

    The resource manager works in a similar way to the operating system scheduler. At a given time, the resource manager schedules one Oracle process per CPU, while queuing all the other processes in an internal run queue. By default, the resource manager randomly picks up an Oracle process and schedules it for a small time before choosing another one. If the resource plan is configured and enabled, the selection of Oracle process for scheduling is made as per the allocation in the resource plan i.e. the consumer groups with higher allocation are selected and scheduled more frequently than others. The Oracle processes can be foreground processes or CPU-intensive background processes. The resource manager plan ensures that critical background processes do not starve at peak workload time.

     

    If you have consolidated multiple Oracle Database instances on a single server, you may face challenges of CPU contention across multiple instances. One database instance could issue a CPU intensive workload, while other instances starve for resources. This could lead to performance degradation of critical applications and potentially destabilize the server. Oracle Database 11g Release 2 introduced "Instance caging" to control the maximum CPU utilization limit for a database instance. Used in conjunction with the initialization parameter CPU_COUNT, the number of CPUs that an instance can consume at a given time can be controlled. 

     

    If multiple Oracle databases are consolidated on an Exadata database machine, the disk I/Os can be managed using IO Resource Manager (IORM). The IORM determines the order in which the I/Os from multiple run queues must be selected, scheduled and issued to the disk. Note that the resource manager plan kicks in only if there is resource contention i.e. when the cell disk I/O queue becomes full. The higher the allocation for a database, the more frequently its I/O request gets scheduled on an Exadata storage cell.


    Using Resource Manager for Database Consolidation on Exadata

    The Exadata database machine is an integral part of the Oracle Engineered Systems family. It provides balanced configuration of database server nodes, storage cell grid and high-speed network fabric, making it a preferred platform to run OLTP and data warehouse workloads alike. The high end capacity and highly optimized engineering of Exadata makes it an ideal choice for consolidating Oracle Databases. It uses Oracle Resource Manager to make it an effective consolidation and maintain healthy resource equation amongst the databases. Oracle Resource Manager enables an OLTP database to be hosted alongside a warehouse database and run CPU-intensive operations parallel to critical OLTP operations. In addition, the pay-for-performance capability can be enabled by specifying the maximum I/O bandwidth for a database or applications.

     

    The databases consolidated on Exadata database machine share the intelligent storage. The unique intelligence is credited to I/O tagging on Exadata, which helps the storage to differentiate between an OLTP I/O or a reporting I/O and act accordingly. On Exadata, each I/O is tagged with relevant information like which database issued it, what was the purpose and at what priority. The I/Os from OLTP workloads are prioritized over other workload I/Os on disk as well as in the flash (Figure 2). On a non-Exadata storage, the resource allocation or I/O bandwidth can neither be guaranteed nor managed. On Exadata storage, the user-defined IORM plan can dynamically distribute the disk I/O bandwidth among databases, pluggable databases and consumer groups. The critical background processes, like log file sync and control file I/Os, are critical to database performance and thus, always preferred over user I/Os (Figure 3).

    Fig2.png

    Figure 2: IORM places OLTP I/Os ahead of scan I/Os

     

    Fig3.png

    Figure 3: IORM prefers critical background I/Os over user I/Os


    Configuring an IORM plan on Exadata

    The IORM plan can be configured using the ALTER IORMPLAN command on command-line interface (CellCLI) utility on each Exadata storage cell. It consists of two parameters - dbplan and catplan. While the "dbplan" is used to create the I/O resource directives for the databases, the "catplan" is used to allocate resources by workload category consolidated on the target system. Both the parameters are optional, i.e. if catplan is not specified, category-wise I/O allocation will not take place. The directives in an inter-database plan specify allocations to databases, rather than consumer groups. To create a database plan, IORM uses certain attributes as listed below.

    • name - Specify the database name, profile name (from Exadata Storage Software Release 12.1.2.1). Use "other" when specifying allocation and "default" when specifying share for databases.
    • level - Specify the level of allocation. In a multi-level plan, if the current level is unable to utilize the allocated resources, the resources are cascaded to the next level.
    • role - Specify the database role i.e. primary or standby in an Oracle Data Guard environment. It indicates that the directive is applicable only if the database exists in the role specified. For "other" and "default" directive, the attribute is not applicable.
    • allocation/share - Specify the resource allocation to a specific database in terms of percentage or shares. If you specify both allocation and share, the directive is invalidated. With percentage based allocation, you can specify a "level", so that the unused resources can be cascaded to the successive levels. There can be a maximum of eight levels and the sum of all allocations at a level must not exceed 100. Likewise, there can be a maximum 32 directives.

    With "share" based allocation, you do not have to specify levels and allocation as a percentage. A share can be a value between 1 to 32, which represents the degree of importance for a specific database. Share-based allocations can support up to 1024 directives.

    • limit - Specify maximum limit of disk utilization for a database. This is a handy directive in consolidation exercises because it helps in achieving consistent I/O performance and pay-for-performance capability.
    • flashCache - Specify whether or not a database can make use of flash cache
    • flashLog - Specify whether or not a database can make use of flash log

     

    From Exadata cell versions 11.2.3.2 and above, the IORM is enabled by default with the BASIC objective. The BASIC objective lets IORM protect high latency small I/O requests and manage flash cache. To enable the IORM for user defined plans, you must set the objective to AUTO. To disable the IORM, set the objective back to BASIC.


    CellCLI> ALTER IORMPLAN OBJECTIVE = AUTO;


    The IORM Objective

    The objective is an essential setting in an IORM plan. It is used to optimize the I/O request issue based on the workload characteristics. An IORM objective can be either basic, auto, low_latency, high_throughput, or balanced.

    • Basic - This is the default setting and doesn't deal with the user-defined plans. It only guards the high latency small I/Os while maximum throughput is maintained.
    • low_latency - The objective is to reduce the latency by capping the concurrent I/O requests maintained in the disk drive buffer. The setting is suitable specifically for OLTP workloads.
    • high_throughput - The target is used for warehouse workloads to maximize the throughput by delivering a larger buffer of concurrent I/O requests.
    • balanced - The objective balances the low latency and high throughput
    • auto - The objective lets the IORM to decide the appropriate objective depending on the active workload on the cell


    Case Study - Manage disk I/O using IORM

    Let us consider a consolidation case study. We plan to migrate three databases SALES, FINANCE and PRODUCTS on an Exadata database machine. SALES is the mission critical database amongst the three, while FINANCE is comparatively more business-relevant than PRODUCTS. The objective of the IORM plan is to maintain the consistent I/O performance of SALES application.


    IORM for Multiple Databases

    Let's check how to create level-wise directives so that the unused resources at a certain level can be utilized by other databases.

    • The SALES database gets 50% of I/O resources at the top-priority level
    • The FINANCE database gets 35% of remaining I/O resources plus 70% of unused resources from SALES
    • The PRODUCTS database gets 10% of remaining I/O resources plus 30% of unused resources from SALES
    • All other databases should get 5% of I/O resources.

     

    Table 1. Resource allocation by percetnages for multiple databases

    Database

    Level 1

    level 2

    Sales

    50

     

    Finance

    35

    70

    Products

    10

    30

    Others

    5

     

     

    The IORM plan for the above allocation would look like -

     

    CellCLI> ALTER IORMPLAN

    dbplan =   ((name=sales, level=1, allocation=50), -

       (name=finance, level=1, allocation=35), -

       (name=finance, level=2, allocation=70), -

       (name=products, level=1, allocation=10), -

       (name=products, level=2, allocation=30), -

       (name=other, level=1, allocation=5));

     

    If we wish to go with the share-based allocation, the resource table would have looked like -

    Table 2. resource allocation by shares for databases

    database

    shares

    maximum utilization limit

    Sales

    10

     

    Finance

    4

    40

    Products

    2

    25

    Default

    1

     

     

    Note that SALES, being the critical database, gets 10 shares which implies that at a given time, SALES database will be 2.5 times preferred over FINANCE while issuing an I/O. Similarly, FINANCE will be double preferred over PRODUCTS. In an event that all databases except FINANCE are idle, it can consume a maximum of 40% of the I/O resources. The resource utilization will tend to balance as other database become operational. If nothing is specified for limit in the directive, the database will be permitted to use 100% of all I/O resources. The "default" directive will be applicable for all other databases hosted on the same server, but an explicit directive is not included in the plan.

     

    The share-based plan as per the above allocation is as below.


    CellCLI> ALTER IORMPLAN

    dbplan =   ((name=sales, share=10), -

       (name=finance, share=4, limit=40), -

       (name=products, share=2, limit=25), -

       (name=default, share=1));

     

    The inter-database plans i.e. IORM exposes the first layer of resource allocation in a consolidated stack. Each of the databases may have intra-database or database resource manager plans to manage resources at workload level.


    IORM for Pluggable Databases

    The Oracle Database 12c Multitenant architecture is fully supported with Exadata Software Release 12.1. An Oracle 12c container database (CDB) can host multiple pluggable databases, which share the CDB instance and thereby, the resources. To allocate the CPU and parallel execution servers among each pluggable database (PDB) within a container database, the database resource manager has been enhanced to create CDB plan and specify a share value to each PDB through plan directives. The shares allocated to each PDB are the minimum guaranteed resources. You can also specify the maximum utilization limit to enable pay-for-performance capability and ensure consistent performance. The PDB resource plan determines how the resources allocated to a PDB, are further allocated to consumer groups within the CDB. It is similar to the non-CDB resource plan.

     

    The CDB plan is used to manage I/O resources to each pluggable database on an Exadata machine. Refer the Appendix section of the post for the script to create a CDB plan.


    Managing Exadata Flash Cache

    One of the key enablers of Exadata's extreme performance and scalability is the Exadata Smart flash Cache. The IO Resource Manager allows the enabling and disabling the usage of flash cache by multiple databases consolidated on an Exadata machine. The IORM plan directive can set the "flashCache" attribute to prevent the databases from using flash cache. If the attribute is not specified in the directive, the database is assumed to be using the flash cache. Disabling the flash cache for a database would require considerable thinking and strong justification. The usage of flash logs can also be controlled through IORM plans. You can set the attribute "flashLog" in the plan directive to enable or disable the flash log usage for a database. But since it consumes a very small portion of total flash, it is recommended to make use of flash log.

     

    Starting with Exadata Storage Server software release 12.1.2.1, the IORM can also manage the flash I/Os along with the disk I/Os using a feature known as Flash IORM. OLTP flash I/Os are automatically prioritized over scan I/Os, thus ensuring faster OLTP response times. Based on the allocation made in IORM plan directives, the flash bandwidth can be distributed across multiple databases. The distribution of excess flash bandwidth between scans cascades up to the consumer groups in each database.

    Another new feature in Exadata Storage Server software release 12.1.2.1, the new Flash Cache Resource Management allows the users to configure the minimum and maximum value of flash which can be consumed by a database. The new attributes - "flashCacheMin" determines the minimum flash guaranteed for a database while "flashCacheLimit" is the soft upper limit. The "flashCacheLimit" is enforced only when the flash is full.


    Managing Standby databases using IORM

    In a data guard setup, the primary and standby databases may get impacted by the redo apply and active workloads on both the sites. Synchronous redo transfer may slow down OLTP performance on the primary, while heavy scans could degrade the database performance on the standby.

     

    IO Resource Management allows the database role to be specified as "primary" or "secondary" in the plan directives. The IORM plan on the primary database will continue to function and guarantee disk I/O for all the databases. You may wish to have same IORM plans on primary and secondary so that the database performance remains consistent during switch over. However, the share value may differ depending on the active workload on the secondary database.

     

    Let us revisit our consolidation case study and add database roles to the databases. The share value allocated to FINANCE on standby is comparatively higher than the primary.

     

    Table 3. Resource allocation by share by database role in a data guard setup

    database

    role

    share

    Maximum utilization limit

    Sales

    Primary

    10

     

    Sales

    Standby

    6

    70

    Finance

    Primary

    4

    40

    Finance

    Standby

    3

    50

    Products

    Primary

    2

    25

    Products

    Standby

    1

    25

    Default

     

    1

     

     

    The IORM plan for the above allocation is as below -

     

    CellCLI> ALTER IORMPLAN

    dbplan =   ((name=sales, share=10, role = primary), -

       (name=sales, share=6, limit = 70, role = standby), -

       (name=finance, share=4, limit=40, role = primary), -

       (name=finance, share=3, limit=50, role = standby), -

       (name=products, share=2, limit = 25, role = primary), -

       (name=products, share=1, limit = 25, role = standby), -

       (name=default, share=1));


    Exadata X5 Storage Software Release (12.1.2.1) miscellaneous enhancements

    We have already seen the latest enhancements on flash cache management. Flash cache IORM and flash cache space management can be instrumental in managing flash I/Os and ensuring flash usage for all databases. Besides the flash cache enhancements, let us have a look at other enhancements along the lines of resource management.


    I/O resource management profiles

    The IORM plans in Exadata X5 Storage Software Version 12.1.2.1 allow the creation of profile directives. Before this release, the "name" attribute in the plan directive must be one of the database names consolidated on the Exadata machine. The profile directive eases the creation of IORM plans for a large consolidation of databases.

     

    ALTER SYSTEM set db_performance_profile=gold scope=spfile;

     

    A profile can be created for a database. Multiple databases can have the same profile name. While creating the profile directives, the "name" attribute is the profile name (instead of database name) with an additional attribute type=profile. The IORM plan with profile directives would appear as below -

     

    CellCLI> ALTER IORMPLAN

    dbplan=((name=gold, share=10, limit=100, type=profile),

            (name=silver, share=5, limit=60, type=profile),

            (name=bronze, share=1, limit=20,  type=profile))


    I/O Analysis in AWR Reports

    AWR reports will now include a section on I/O analysis. The I/O analysis contains the drill down performance analysis of top databases by I/O requests and throughput.


    Conclusion

    The Exadata Database Machine is an ideal platform for database consolidation. Applications in a consolidated environment can have their own service level priorities and business criticalities can be met. IO Resource Manager helps in achieving consistent performance for these consolidated databases and provides added capabilities like pay-for-performance and flash cache space management. Essentially, it plays a vital role in consolidation planning and service deployment strategies. Not just the disk I/O bandwidth, database resource manager can help in managing mixed workload and schema consolidation scenarios.

     


    Appendix A: Create a CDB resource plan

    This section demonstrates how to create a CDB level resource plan to allocate the shares across the pluggable databases.

     

    Table 1a. cdb resource allocation

    pluggable database

    Share

    Utilization limit

    guaranteed cpu

    Sales

    6

    100

    60

    Finance

    3

         50

       30

    Products

    1

         25

       10

     

     

    The table above lists the pluggable databases in an Oracle 12c container database with expected no. of CPU shares. The following PL/SQL block will configure the resource manager plan using

     

    DBMS_RESOURCE_MANAGER package.

    BEGIN

    /* Create the pending area */

    DBMS_RESOURCE_MANAGER.create_pending_area;

    /* Create a resource plan */

    DBMS_RESOURCE_MANAGER.create_cdb_plan(

        plan => 'orcl_cdb',

        comment => 'Demo CDB resource plan');

    /* Create the CDB plan directives to allocate shares to each pluggable databases */

    DBMS_RESOURCE_MANAGER.create_cdb_plan_directive

       (plan => 'orcl_cdb',

    pluggable_database => 'sales',

        shares => 6,

        utilization_limit => 100);

     

    DBMS_RESOURCE_MANAGER.create_cdb_plan_directive

       (plan => 'orcl_cdb',

    pluggable_database => 'finance',

        shares => 3,

        utilization_limit => 50);

     

    DBMS_RESOURCE_MANAGER.create_cdb_plan_directive

       (plan => 'orcl_cdb',

    pluggable_database => 'product',

        shares => 1,

        utilization_limit => 25);

     

    /* Validate the pending area */

    DBMS_RESOURCE_MANAGER.validate_pending_area;

     

    /* Submit the pending area */

    DBMS_RESOURCE_MANAGER.submit_pending_area;

    END;

    /

     

     


    Appendix B: Create an Intra-database resource plan

    This section demonstrates the steps to create an intra-database resource plan to manage mixed workloads in a single database. The table below lists the consumer groups with the share allocation.

     

    Table 3. Resource allocation by share by database role in a data guard setup

    Consumer gorup

    shares

    utilization limit

     

    Critical

    5

     

     

    Batch

    2

     

     

    Maintenance

    2

    90

     

    Other

    1

     

     

     

    /* Create the pending area to hold the resource management configuration */

    SQL> exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

     

    /* Create the consumer group Critical */

    SQL> exec DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'Critical',

    COMMENT => 'Consumer Group for Critical requests',

    MGMT_MTH => 'RATIO');

     

    /* Create the consumer group Batch */

    SQL> exec DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'Batch',

    COMMENT => 'Consumer Group for Batch requests',

    MGMT_MTH => 'RATIO');

     

    /* Create the consumer group Maintenance */

    SQL> exec DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'Maintenance',

    COMMENT => 'Consumer Group for Maintenance requests',

    MGMT_MTH => 'RATIO');

     

    /* Create the consumer group Others */

    SQL> exec DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'Others',

    COMMENT => 'Consumer Group for Other requests',

    MGMT_MTH => 'RATIO');

     

    /* Create the Resource Manager Plan */

    SQL> exec DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'Business_plan',

    COMMENT => 'Gold plan for business hours');

     

    /* Create the Resource Manager Plan Directives */

    SQL> exec DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'Business_Plan',

    GROUP_OR_SUBPLAN => 'Critical',

    COMMENT => 'Critical Workload',

    MGMT_P1 => 5);

     

    SQL> exec DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'Business_Plan',

    GROUP_OR_SUBPLAN => 'Batch',

    COMMENT => 'Batch jobs',

    MGMT_P1 => 2);

     

    SQL> exec DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'Business_Plan',

    GROUP_OR_SUBPLAN => 'Maintenance',

    COMMENT => 'Maintenance activities',

    MGMT_P1 => 2);

     

    SQL> exec DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'Business_Plan',

    GROUP_OR_SUBPLAN => 'Others',

    COMMENT => 'Other',

    MGMT_P1 => 1);

     

    /* Map the consumer group CRITICAL to the SCOTT user */

    SQL> exec DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (ATTRIBUTE => DBMS_RESOURCE_MANAGER.ORACLE_USER,

    VALUE => 'SCOTT',

    CONSUMER_GROUP => 'Critical');

     

    /* Map the consumer group BATCH to the ADMIN user */

    SQL> exec DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (ATTRIBUTE => DBMS_RESOURCE_MANAGER.ORACLE_USER,

    VALUE => 'ADMIN',

    CONSUMER_GROUP => 'Batch');

     

    /* Validate the pending area */

    SQL> exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

     

    /* Submit the pending area */

    SQL> exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();@