5.2.9 Verifying the Configuration of I/O Resource Management

Use this checklist to verify that I/O Resource Management (IORM) is configured correctly.

• Verify that the following criteria are met when using IORM to manage I/O resources within a database:
  • A resource plan has been enabled.
  • The same resource plan has been enabled on all database instances.
    • If Oracle Database Resource Manager is enabled using Scheduler Window, then the same plan is always enabled on all database instances.
    • If Oracle Database Resource Manager is enabled using the RESOURCE_MANAGER_PLAN parameter, then use sid='*' to set the parameter for all database instances.
  • The resource plan includes MGMT_P[1-8] directives for each consumer group in the resource plan.

  The following query can be used to verify the preceding criteria have been met:

  SELECT DECODE(count(*), 0, 'Intra-Instance IORM Plan Enabled', 
  'No Intra-Instance IORM Plan Enabled') status 
  FROM gv$instance 
  WHERE inst_id NOT IN 
    (SELECT inst_id FROM gv$rsrc_plan WHERE cpu_managed = 'ON');
  

• Verify that the interdatabase plan has been configured properly when IORM is used to manage I/O resources from multiple databases

  CellCLI> LIST IORMPLAN DETAIL
  

  If no interdatabase plan has been configured, then use the CellCLI ALTER IORMPLAN command to configure a plan. Each active database should have its own directive in the dbPlan parameter.

• Verify that sessions are mapped to the correct consumer group.

  Run the following query while a workload is running:

  SELECT r.sid,
         c.consumer_group current_consumer_group
    FROM v$rsrc_session_info r, dba_rsrc_consumer_groups c 
    WHERE r.current_consumer_group_id = c.consumer_group_id
  UNION
  SELECT sid, 'OTHER_GROUPS' from v$rsrc_session_info 
    WHERE current_consumer_group_id = 0;
  

  A session may not be in the expected consumer group due to the following configuration errors:

  • Missing privilege: In order for a session to switch into a consumer group, its user or role must have permission to switch into that consumer group. The following query shows the permissions for all consumer groups.

    SELECT grantee, granted_group 
    FROM DBA_RSRC_CONSUMER_GROUP_PRIVS
    ORDER BY granted_group;
    

    Use the following command to grant permission for any session to switch into the consumer group. This example shows to how grant the permission for BATCH_GROUP.

    EXEC dbms_resource_manager_privs.grant_switch_consumer_group -
      ('public', 'BATCH_GROUP', FALSE);
    

  • Inactive consumer group: If a session maps to or is manually switched to a consumer group that is not part of the current resource plan, then the session is switched into the default consumer group, OTHER_GROUPS.

    If sessions are being assigned to consumer groups using mapping rules, then the following query can be used to determine the consumer group that the mapping rules selected, the mapping attribute that was used, and the consumer group that the session started in originally.

    SELECT r.sid,
           r.mapped_consumer_group,
           r.mapping_attribute, 
           c.consumer_group original_consumer_group
    FROM v$rsrc_session_info r, dba_rsrc_consumer_groups c 
    WHERE r.orig_consumer_group_id = c.consumer_group_id;
    

    If the mapped consumer group differs from the original consumer group, then the mapped consumer group was not part of the resource plan.

• Use CellCLI to list the number of small and large I/O requests that were issued for each consumer group across all databases.

  CellCLI> LIST METRICCURRENT CG_IO_RQ_LG, CG_IO_RQ_SM   ATTRIBUTES name, -
           metricObjectName, metricValue, collectionTime;
  

  Each consumer group that has an active I/O workload should generate small or large I/O requests according to these metrics.

• While a workload is running, verify that I/O loads are being managed in the correct consumer groups.

  The following CellCLI command lists the number of small and large I/O requests that were issued for each consumer group across all databases:

  CellCLI> LIST METRICCURRENT CG_IO_RQ_LG, CG_IO_RQ_SM   ATTRIBUTES name, -
           metricObjectName, metricValue, collectionTime;
  

• While the workload is running, query the actual I/O utilization for each category, database and consumer group.

  The following CellCLI command lists the small and large I/O utilization for each database running on Oracle Exadata Storage Server:

  CellCLI> LIST METRICCURRENT DB_IO_UTIL_LG, DB_IO_UTIL_SM ATTRIBUTES name, -
           metricObjectName, metricValue, collectionTime;
  

  The output shows the percentage of disk resources utilized by small and large requests from the databases.