H Managing Input Output Resources

H.1 Overview

In Oracle Database 10g, Oracle Database Resource Manager manages the CPU resource among Oracle processes in an instance. However, at times those processes are bound by the input-output. It would be more effective to be able to also manage the input-output bandwidth. The new Input Output Resource Manager feature helps manage dynamic resources involved in handling disk input-output operations.

You can enable Input Output Resource Manager on all platforms on which Oracle runs. It works on small and large database configurations, and on hardware configurations. Further, Input Output Resource Manager scales with both, the number of disks and the additional channel capacity.

Input Output Resource Manager permits you to perform the following tasks:

• You can enable or disable Input Output Resource Manager without restarting the instance.

• You can disable Input Output Resource Manager independent of CPU resource management.

• You can load a new input-output resource plan on a running system.

• You can change an existing plan on a running system.

• You can switch plans and expect a smooth change in the input-output bandwidth distribution.

Enabling Input Output Resource Manager does not cause any significant decrease in performance.

• At times, honoring resource allocations can result in sub-optimal disk utilization. In such cases, there is an expected decrease in throughput.

• If the input-output load is under the database's limits, then enabling Input Output Resource Manager with a plan with a single consumer group has a minimal effect on performance.

• For consumer groups with high resource allocations, using Input Output Resource Manager for plans with multiple consumer groups shows a significant performance improvement.

H.2 Requirements

The following is the list of the requirements for Input Output Resource Manager:

• You need to specify the type of database configuration, whether it is a dedicated or shared storage configuration, in the resource manager plan.

  Note:

  • Specify the database type for each database.

  • The storage configuration applies to the database as a whole, not a specific plan.

• If a database configuration is a shared storage configuration, then you need to do the following:

  • Specify a per database cap on the maximum amount of input-output bandwidth that the database can use. In this way, you can set a limit to the input-output from one database, when different databases use the same storage.

  • Configure the MBPS and IOPS input-output limits in the plan.

• After enabling Input Output Resource Manager, you need to specify the same plan for each instance. This is because the resource manager plan specifies the type of storage configuration and the IOPS and MBPS caps if the storage is a shared storage configuration.

• You need to specify which database files must be managed as part of the main, critical storage pool.

• You need to specify which database files must be managed by configuring rules based on the files' Automatic Storage Management disk group, file type, and area.

• If you partitioned the input-output resources between Oracle and another application using an external OS-level Input Output Resource Manager, then you need do the following:

  • Specify input-output caps for the Oracle database.

  • Use Input Output Resource Manager for intra-database resource management.

• You need to categorize all database files into any one of the following categories:

  • Sage files

  • Managed files

  • Unmanaged files

  Tip:

  By default, all non-SAGE files are managed.

• For all non-SAGE files, you can specify which files are not managed. For example, a temp file, a log file, or a file in the recovery area.

  Note:

  A file is not managed if it is from an excluded Automatic Storage Management disk group. In general, only one Automatic Storage Management disk group is managed, as Automatic Storage Management disk groups are typically on separate storage devices. Multiple Automatic Storage Management disk groups may be managed, if they share storage controllers or disks.

  The rules for unmanaged files apply to the whole database, and not just a particular plan.

• You can specify the following for each plan and for each group:

  • The maximum amount of input-output and input-output requests that a session can issue before an action is taken, kill session, abort call, or switch to another group.

  • The maximum number of input-output requests that a session can issue as a batch.

• For dedicated storage configurations, you need to perform an input-output calibration, either while creating the database, or later, by using a PL/SQL command. Ensure that you run the input-output calibration tool before turning on Input Output Resource Manager for the first time.

  Note:

  Calibration requires the issuance of sufficient amount of input-output to saturate the storage system. This can affect the performance of critical sessions. Therefore, you must calibrate input-output when the database is inactive. It takes approximately 10 minutes. Only an administrator with SYSDBA privilege can run this procedure.

  If you are unable to run an input-output calibration tool, then the available input-output resources are estimated based on the number of host devices, operating system statistics, and Oracle statistics. You must provide an initial estimate of the input-output limits. Input Output Resource Manager is not enabled until an hour of input-output statistics is available.

H.3 PL/SQL Statement

You can calibrate the input-output capabilities of the storage, by using the following PL/SQL statement:

DBMS_RESOURCE_MANAGER.CALIBRATE_IO()

Input-output calibration involves obtaining the storage's capabilities by issuing an extremely heavy input-output workload.

Storage capability is calculated in terms of how many input-output requests and bytes of input-output can be sustained for each second.

Input-output workload consists of random reads. It uses single database block reads and large reads whose size is determined by the maximum size of input-output that is supported for the host's operating system.

Status and Results View

The input-output calibration procedure returns immediately, however, the results are not available immediately. You need to refer to the V$IO_CALIBRATION table for the status and results of input-output calibration.

Table H-1 lists the components of a V$IO_CALIBRATION table.