Overview I/O Calibration
Oracle Database 11g introduces an I/O Calibration mechanism, whereby you can run I/O calibration tests either through the Enterprise Manager Performance page or a PL/SQL package. Oracle’s I/O calibration is a variation on the Clarion tool. In an Oracle database, the I/O workload is of two basic types—small random I/O and large sequential I/O. OLTP applications usually experience the small random I/O workload, where the speed with which small I/O requests are serviced is paramount. Thus, disk spinning and seeking times are of critical importance. OLAP applications, on the other hand, employ the large sequential I/O in general. For these types of applications, the critical factor is the capacity of the I/O channel. The larger the I/O channels between the database server and the storage system, the larger the I/O throughput. Oracle uses the following two metrics, each measuring the efficacy of one type of I/O workload:
- IOPS (I/O per second) The IOPS rate is the number of small random I/Os the system can perform in a second and depends on the spin speed of disks. You can increase the IOPS rate by increasing the number of disks in the storage array or by using faster disk drives, which have a high RPM and lower seek time.
- MBPS (megabytes per second) This metric measures the data transfer rate between the server and the storage array and depends on the capacity of the I/O channel between the two systems. A larger I/O channel means a higher MBPS rate.
Two important terms need clarification in this discussion: throughput and latency. The throughput of a system determines how fast it can transfer data and is measured by the MBPS metric. The channel capacity determines the overall throughput of the system, and it thus puts the ceiling on the amount of data transfer. Latency refers to the lag between the time an I/O request is made and when the request is serviced by the storage system. High latency indicates a system that’s overloaded and you can reduce latency by striping data across multiple spindles, so different disks can service the same I/O request in parallel.
Oracle recommends that you use the new I/O Calibration tool to determine I/O metrics in a database. It takes about 10 minutes to run the tools and you should pick a time when the database workload is light to avoid overstressing the storage system. You can run only a single calibration task at a time. If you perform the task in an RAC environment, the workload is generated simultaneously from all instances in the system. You can either run the tool with Enterprise Manager or through PL/SQL.
Calibrating I/O Using PL/SQL
You can also use the new procedure CALIBRATE_IO from the DBMS_ RESOURCE_MANAGER package to run the I/O Calibration task. Here is an example:
begin
exec dbms_resource_manager.calibrate_io(-
num_disks => 1, -
max_latency => 10, -
max_iops => :max_iops, -
max_mbps => :max_mbps, -
actual_latency => :actual_latency);
end;
/
In the CALIBRATE_IO procedure, the following are the key parameters:
- num_disks: Approximate number of disks in the storage array.
- max_latency: Maximum tolerable latency (in milliseconds) for an I/O request.
- max_ios: Maximum number of random DB block-sized read requests that can be serviced.
- max_mbps: Maximum number of randomly distributed 1MB reads that can be serviced (in megabytes per second).
- actual_latency: Average latency of DB block-sized I/O requests at max_iops rate (in milliseconds).
The procedure only works if asynchronous I/O is enabled. If asynchronous I/O is not enabled, the procedure returns the following error.
DECLARE
*
ERROR at line 1:
ORA-56708: Could not find any datafiles with asynchronous i/o capability
ORA-06512: at "SYS.DBMS_RMIN", line 453
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1153
ORA-06512: at line 6
You can use the FILESYSTEMIO_OPTIONS static initialization parameter to enable or disable asynchronous I/O or direct I/O on file system files. This parameter is platform-specific and has a default value that is best for a particular platform.
FILESYTEMIO_OPTIONS can be set to one of the following values:
- ASYNCH: enable asynchronous I/O on file system files, which has no timing requirement for transmission.
- DIRECTIO: enable direct I/O on file system files, which bypasses the buffer cache.
- SETALL: enable both asynchronous and direct I/O on file system files.
- NONE: disable both asynchronous and direct I/O on file system files.
SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string none
ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
Usage Notes
- Only users with the SYSDBA privilege can run this procedure. Qualified users must also turn on timed_statistics, and ensure asynch_io is enabled for datafiles. This can be achieved by setting filesystemio_options to either ASYNCH or SETALL. One can also query the asynch_io status by means of the following SQL statement:
col name format a50
SELECT name, asynch_io FROM v$datafile f,v$iostat_file i
WHERE f.file# = i.file_no
AND filetype_name = 'Data File'; - Only one calibration can be run at a time. If another calibration is initiated at the same time, it will fail.
- For an Oracle Real Application Clusters (Oracle RAC) database, the workload is simultaneously generated from all instances.
For timed_statistics paramter,please refer to following illustration :
TIMED_STATISTICS specifies whether or not statistics related to time are collected.Values:
- true: The statistics are collected and stored in trace files or displayed in the V$SESSTATS and V$SYSSTATS dynamic performance views.
- false: The value of all time-related statistics is set to zero. This setting lets Oracle avoid the overhead of requesting the time from the operating system.
Starting with release 11.1.0.7.0, the value of the TIMED_STATISTICS parameter cannot be set to false if the value of STATISTICS_LEVEL is set to TYPICAL or ALL.
On some systems with very fast timer access, Oracle might enable timing even if this parameter is set to false. On these systems, setting the parameter to true can sometimes produce more accurate statistics for long-running operations.
The [G]V$IO_CALIBRATION_STATUS views show the current status of the calibration runs. During a run the status of 'IN PROGRESS' is displayed. Once a run is complete the status switches to 'READY' and the calibration time is displayed.Besides,NOT AVAILABLE mean Calibration results not available and CALIBRATION_TIME tell us End time of the last calibration run
SQL> SELECT * FROM v$io_calibration_status;
STATUS CALIBRATION_TIME
------------- -------------------------------
IN PROGRESS
SQL> SELECT * FROM v$io_calibration_status;
STATUS CALIBRATION_TIME
------------- ---------------------------------------------------------------------------
READY 28-JUL-2008 14:37:38.410
1 row selected.
Once you execute the CALIBRATE_IO procedure, you can query the V$IO_ CALIBRATION_STATUS and the DBA_RSRC_IO_CALIBRATE views to check the results. Here’s a sample query:
SQL> select max_iops, max_mbps, max_pmbps, latency
from dba_rsrc_io_calibrate;
MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY
---------- ------------ ------------ ----------
133 12 6 64
Oracle Database 11g collects I/O statistics in three different dimensions to provide a consistent set of statistics for I/O calls.These dimensions are RDBMS components grouped into 12 functional groups.
- The V$IOSTAT_ FUNCTION view provides the details. I/O statistics are collected for each consumer group that’s part of the currently enabled resource plan.
- The V$IOSTAT_CONSUMER_GROUP view has the details.
- Individual file level I/O statistics are collected and stored in the V$IOSTAT_ FILE view for details.
参考至:《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》
http://www.oracle-base.com/articles/11g/resource-manager-enhancements-11gr1.php
http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams259.htm#REFRN10218
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_resmgr.htm#ARPLS67599
本文原创，转载请注明出处、作者
如有错误，欢迎指正
邮箱:czmcj@163.com
扫一扫
2047
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
