As Published In
Oracle Magazine
January/February 2009

TECHNOLOGY: OCP


Managing Database Resources

By Sushma Jagannath

New features in Oracle Database 11g help you assess performance and limit I/O usage.

The database resource manager prioritizes and manages the distribution of resources among database sessions, by controlling the execution schedule inside Oracle Database. In releases before Oracle Database 11g, the database resource manager could manage only the CPU; now it can also manage input/output (I/O).

Let’s look at some of Oracle Database 11g’s new database resource manager features that can help you assess storage system performance and limit I/O usage per session. Then test your understanding with sample questions you might encounter in the Oracle Database 11g: New Features for Administrators exam, which enables Oracle Certified Professionals who are certified on Oracle Database 10g to upgrade their certifications to Oracle Database 11g.

I/O Calibration

Oracle Database 11g introduces the I/O calibration feature, which enables you to assess storage subsystem performance and determine whether I/O performance problems are caused by the database or the storage subsystem. I/O calibration uses software libraries to issue I/O to the database files to test the throughput. Using this feature, you can determine I/O metrics such as I/O per second (IOPS), megabytes per second (MBps), and I/O latency. You can access the I/O calibration tool from the Performance tab of Oracle Enterprise Manager or by running the CALIBRATE_IO procedure of the DBMS_RESOURCE_MANAGER package. Listing 1 shows the syntax for the CALIBRATE_IO procedure.

Code Listing 1: Syntax for the CALIBRATE_IO procedure

 

DBMS_RESOURCE_MANAGER.CALIBRATE_IO (
   num_physical_disks               IN  PLS_INTEGER DEFAULT 1,
   max_latency                         IN  PLS_INTEGER DEFAULT 20,
   max_iops                             OUT PLS_INTEGER,
   max_mbps                           OUT PLS_INTEGER,
   actual_latency                      OUT PLS_INTEGER);


The CALIBRATE_IO procedure accepts two parameters: NUM_PHYSICAL_DISKS (the number of physical disks) and MAX_LATENCY (the maximum latency for disk access, in milliseconds). It also has three OUT parameters: MAX_IOPS, the maximum number of I/O operations per second; MAX_MBPS, the maximum number of megabytes per second; and ACTUAL_LATENCY, the actual latency observed during the test.

You can query the calibration status in the V$IO_CALIBRATION_STATUS view at any time during the I/O calibration process. After the I/O calibration finishes, you can view the results in the DBA_RSRC_IO_CALIBRATE table.

Which two statements correctly describe the I/O calibration process?

A. It is used to assess the I/O performance of the storage system of the database servers.
B. It uses the CALIBRATE_IO procedure to collect statistics.
C. The execution time of the CALIBRATE_IO procedure is always the same, irrespective of the number of disks.
D. It issues I/Os sequentially, using Oracle datafiles to access the storage media.

The correct answers are A and B. I/O calibration uses CALIBRATE_IO to ascertain the I/O performance of the storage system of the database servers. Answer C is incorrect because the execution time depends on the number of disks in the storage subsystem and increases with the number of nodes in the database. Answer D is incorrect because, unlike other external I/O calibration tools that issue I/Os sequentially, the I/O calibration feature of Oracle Database issues I/Os randomly , using Oracle datafiles to access the storage media.

What are the prerequisites for running the I/O calibration procedure?

A. Asynchronous I/O must be disabled.
B. TIMED_STATISTICS must be set to TRUE.
C. The user must be granted the SYSDBA privilege.
D. TIMED_STATISTICS must be set to FALSE.

The correct answers are B and C. The latency time is computed only when the TIMED_STATISTICS initialization parameter is set to TRUE, and the user must have the SYSDBA privilege before executing this procedure. Answer A is incorrect because asynchronous I/O must be enabled before execution of this procedure; otherwise, it returns this error message:

 

ORA-56708: Could not find any datafiles with asynchronous i/o capability 


Per-Session I/O Limits

In previous releases of Oracle Database, DBAs could specify the maximum amount of time a session could run before some action—such as aborting the call, killing the session, or migrating the session to a new consumer group—was taken. In Oracle Database 11g, you can configure the database resource manager to take the same set of actions for sessions that exceed I/O resource consumption limits. You can specify the maximum number of I/O requests or the maximum number of megabytes of I/O a session can issue before the session is automatically switched to another consumer group or is killed.

This feature is used for two purposes: to identify runaway queries automatically and to move sessions executing long-running calls to lower-priority consumer groups. When you create a resource plan directive, you can specify the I/O resource limits by providing values to the switch_io_megabytes argument, which specifies the amount of I/O (in megabytes) a session can issue before an action is taken, or the switch_io_reqs argument, which specifies the number of I/O requests a session can issue before an action is taken. In both cases, the default is NULL, which means an unlimited number of I/O requests.

You issued the command in Listing 2. When—and for which session or call—does the database resource manager switch the resource consumer group from oltp_group to batch_group?

Code Listing 2: Switching from oltp_group to batch_group

 

BEGIN
    DBMS_RESOURCE_MANAGER.create_plan_directive (
        plan                             => 'my_plan',
        group_or_subplan           => 'oltp_group',
        comment                       => 'Auto Conditional Switch from
                                               OLTP group to batch_Group',
        mgmt_p1                      => 70,
        switch_group                => 'batch_group',
        switch_time                  => 180,
        switch_io_reqs              => 3000,
        switch_io_megabytes     => 2048,
        switch_for_call              => TRUE);
    END;


A. It switches the resource consumer group for any session that exceeds 180 seconds of usage time.
B. It switches the resource consumer group for the current session when it exceeds 180 seconds of usage time and the number of I/O requests exceeds 3,000 and the amount of I/O requested exceeds 2,048MB.
C. It switches the resource consumer group when any session exceeds 180 seconds of usage time or the number of I/O requests exceeds 3,000 or the amount of I/O requested exceeds 2,048MB.
D. It switches the resource consumer group for the current call within the session if the usage time exceeds 180 seconds or the number of I/O requests exceeds 3,000 or the amount of I/O requested exceeds 2,048MB.

The correct answer is D. The switch is applicable only for the current call within the session. The database resource manager switches this call from OLTP_GROUP to BATCH_GROUP when it exceeds the limits of any of the thresholds: elapsed time, number of I/O requests, or amount of I/O requested.

Which statements are true about limiting I/O per session?

A. It allows automatic consumer group switching based on I/O thresholds.
B. It can kill or abort a session based on any combination of elapsed time, I/O requests, and I/O (in megabytes).
C. It stops the session from using further I/O resources but does not switch to another consumer group or kill the session.
D. It limits the I/O consumption for the session only when there is a resource shortage and the session tries to exceed the limit. 

Next Steps


LEARN more about the Oracle Certification Program

EXPLORE the certification forum

READ Inside OCP columns

The correct answers are A and B. By using the switch_io_reqs, switch_io_megabytes, and switch_time parameters, you can control the I/O usage per session and switch to another consumer group or kill or abort a session if it reaches the threshold. Answer C is incorrect because the limiting of I/O per session either switches the session to another consumer group or kills the session but does not retain the session within the consumer group. Answer D is incorrect because I/O consumption per session is limited, irrespective of the availability of the resources.

I/O Statistics

Oracle Database 11g introduces a set of virtual views that collect statistics for all I/O issued from an Oracle instance. Virtual views show cumulative statistics for each operation type: the number of corresponding requests; the number of megabytes; the total I/O wait time, in milliseconds; and the number of total waits. Component and consumer group statistics are transformed into automatic workload repository metrics that are stored in the repository.

Suppose you maintain a PROD database that has a single instance. You need to collect statistics of all I/Os issued from this instance. Which three virtual views will help you achieve this?

A. V$IOSTAT_FILE
B. V$IOSTAT_FUNCTION
C. V$IOSTAT_CONSUMER_GROUP
D. V$IOSTAT_NETWORK

The correct answers are A, B, and C. V$IOSTAT_FILE displays information about disk I/O statistics of database files (including datafiles, temp files, and other types of database files). V$IOSTAT_FUNCTION displays disk I/O statistics for database functions (such as LGWR and DBWR). V$IOSTAT_CONSUMER_GROUP displays disk I/O statistics for consumer groups. If the database resource manager is enabled, I/O statistics for all consumer groups that are part of the currently enabled resource plan are captured. Answer D is incorrect because V$IOSTAT_NETWORK collects network I/O statistics related to accessing files on a remote database instance.
 



Sushma Jagannath
is a certification exam development manager at Oracle. She has been with the company since 2000.

Send us your comments