DBMS_RESOURCE_MANAGER.CALIBRATE_IO
Introduced in Oracle Database 11g Release 1, the CALIBRATE_IO
procedure gives an idea of the capabilities of the storage system from within Oracle. There are a few restrictions associated with the procedure.
- The procedure must be called by a user with the SYSDBA priviledge.
TIMED_STATISTICS
must be set to TRUE, which is the default whenSTATISTICS_LEVEL
is set to TYPICAL.- Datafiles must be accessed using asynchronous I/O. This is the default when ASM is used.
You can check your current asynchronous I/O setting for your datafiles using the following query.
SELECT d.name, i.asynch_io FROM v$datafile d, v$iostat_file i WHERE d.file# = i.file_no AND i.filetype_name = 'Data File'; NAME ASYNCH_IO -------------------------------------------------- --------- /u01/app/oracle/oradata/DB11G/system01.dbf ASYNC_OFF /u01/app/oracle/oradata/DB11G/sysaux01.dbf ASYNC_OFF /u01/app/oracle/oradata/DB11G/undotbs01.dbf ASYNC_OFF /u01/app/oracle/oradata/DB11G/users01.dbf ASYNC_OFF /u01/app/oracle/oradata/DB11G/example01.dbf ASYNC_OFF 5 rows selected. SQL>
To turn on asynchronous I/O, issue the following command and restart the database.
ALTER SYSTEM SET filesystemio_options=setall SCOPE=SPFILE;
Provided your storage supports asynchronous I/O, the ASYNC_IO flag should now have changed.
SELECT d.name, i.asynch_io FROM v$datafile d, v$iostat_file i WHERE d.file# = i.file_no AND i.filetype_name = 'Data File'; NAME ASYNCH_IO -------------------------------------------------- --------- /u01/app/oracle/oradata/DB11G/system01.dbf ASYNC_ON /u01/app/oracle/oradata/DB11G/sysaux01.dbf ASYNC_ON /u01/app/oracle/oradata/DB11G/undotbs01.dbf ASYNC_ON /u01/app/oracle/oradata/DB11G/users01.dbf ASYNC_ON /u01/app/oracle/oradata/DB11G/example01.dbf ASYNC_ON 5 rows selected. SQL>
You can now call the procedure by running the following code.
CONN / AS SYSDBA SET SERVEROUTPUT ON DECLARE l_latency PLS_INTEGER; l_iops PLS_INTEGER; l_mbps PLS_INTEGER; BEGIN DBMS_RESOURCE_MANAGER.calibrate_io (num_physical_disks => 1, max_latency => 20, max_iops => l_iops, max_mbps => l_mbps, actual_latency => l_latency); DBMS_OUTPUT.put_line('Max IOPS = ' || l_iops); DBMS_OUTPUT.put_line('Max MBPS = ' || l_mbps); DBMS_OUTPUT.put_line('Latency = ' || l_latency); END; / Max IOPS = 95 Max MBPS = 449 Latency = 20 PL/SQL procedure successfully completed. SQL>
In addition to appearing on screen, the results of a calibration run can be displayed using the DBA_RSRC_IO_CALIBRATE
view.
SET LINESIZE 100 COLUMN start_time FORMAT A20 COLUMN end_time FORMAT A20 SELECT TO_CHAR(start_time, 'DD-MON-YYY HH24:MI:SS') AS start_time, TO_CHAR(end_time, 'DD-MON-YYY HH24:MI:SS') AS end_time, max_iops, max_mbps, max_pmbps, latency, num_physical_disks AS disks FROM dba_rsrc_io_calibrate; START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY DISKS -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- 20-JUL-010 18:07:21 20-JUL-010 18:13:55 95 449 461 20 1 SQL>
https://oracle-base.com/articles/misc/measuring-storage-performance-for-oracle-systems