The DBA can assess the I/O capability of the database’s storage system by using the PL/SQL function DBMS_RESOURCE_MANAGER.CALIBRATE_IO() – this routine issues a very I/O intensive read-only workload to thedatabase’s files to assess the maximum IOPS (I/O requests per second) and MBPS (megabytes of I/O per second) that canbesustained. This data can be reexamined at any time using the DBA table, DBA_RSRC_IO_CALIBRATE, that stores I/Ocalibration results. The calibration should be performed when the database is idle and during off-peak hours to minimizethe heavy I/O workload from interfering with the regular workload and vice versa. I/O calibration can be used tounderstand the performance of the storage subsystem and figure out whether I/O performance problems stem from thedatabase or the storage subsystem. Unlike various external I/O calibration tools, this tool uses the Oracle code stack and issues I/O randomly rather than sequentially the results therefore much more closely match the actual database Performance
calibrate_io.sql
rem ***********************************************************
rem
rem
File: calibrate_io.sql
rem
Description: PL/SQL reoutine to calibrate IO
rem
rem
From ‚Oracle Performance Survival Guide‘ by Guy Harrison
rem
Chapter 21 Page 624
rem
ISBN: 978-0137011957
rem
See www.guyharrison.net for further information
rem
rem
This work is in the public domain NSA
rem
rem
rem *********************************************************
set serveroutput on
set echo on
DECLARE
v_max_iops NUMBER;
v_max_mbps NUMBER;
v_actual_latency NUMBER;
BEGIN
DBMS_RESOURCE_MANAGER.calibrate_io(
num_physical_disks => 8,
max_latency => 10,
max_iops => v_max_iops,
max_mbps => v_max_mbps,
actual_latency => v_actual_latency);
DBMS_OUTPUT.put_line(‚Max IOPS=‘ || v_max_iops);
DBMS_OUTPUT.put_line(‚Max MBps=‘ || v_max_mbps);
DBMS_OUTPUT.put_line(‚Latency =‘ || v_actual_latency);
END;
/
This entry was posted on Mittwoch, Juni 27th, 2018 at 21:45 and is filed under Administration, ORACLE Database. You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.