Managing Database ResourcesBy 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.
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.
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.
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.
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.
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.
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?
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.