Method 1:
To do a quick check of Maximum IO per second (IOPS) of your underlying storage system use Oracle supplied IO Calibration tool. I believe this is available from Oracle 10g, but i have used it on Oracle 11g and it works well.
This tool will generate random single and large block reads to generate I/O and measure the IOPS and Mbps.
A very handy tool to estimate the required IOPS when migrating to cloud to build or select the proper storage on your cloud volumes.
Procedure to run the tool:
Note: Use this tool during a quiet time or quiese mode, so no other connections are made to database other than your sqlplus session.
This will give near accurate results.
Login as sysdba to server sqlplus session. Make a local connection.
Run below:
SQL> SET SERVEROUTPUT ON
SQL> 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;
/
Note: The above script will take 2 inputs,
Max Latency – in milli seconds, select the acceptable latency in block reads (default 20 milli seconds is good for most storage types)
Num_Physical_disks – Set it to 1, if you have datafiles spread across multiple physical disks (like in ASM) select the correct number of disks attached to your +Data disk group, otherwise input 1.
This will run for few minutes, leave the session to run.
You can monitor the progress by querying V$IO_CALIBRATION_STATUS.
SQL> select * from V$IO_CALIBRATION_STATUS;
Once the IO calibration is completed, it will give you the results as below:
Max IOPS = 667
Max MBPS = 179
Latency = 21
The above information is also recorded in a table DBA_RSRC_IO_CALIBRATE.
Using the max iops and max mbps you may select the right storage and instance type.
Example: To support 667*10%* around 700 IOPS you may select a GP2 SSD type storage in AWS with 250 GB (it gives approximately 750 IOPS) or you can lock down 250 GB IO storage with 1000 PIOPS.
* To cover for any unforeseen spikes, it would be a good idea to add an additional 10 percent to this peak IOPS number to account for the actual IOPS needed for your database. Also you may add for writes as well.
Method 2:
Another handy method to calculate the IOPS is by using below script and schedule to run it every day or multiple times in a day for a period and check the results.
-Modify the values of run_duration and capture_gap to best fit your needs
– First create a table to store I/O results
CREATE TABLE peak_iops_measurement (capture_timestamp date,total_read_io number, total_write_io number, total_io number,total_read_bytes number, total_write_bytes number, total_bytes number);
– Schedule below script using shell or run it few times on the database as sysdba
The script runs for 1 hour (3600 secs) and captures data every 5 minutes.
DECLARE
run_duration number := 3600;
capture_gap number := 5;
loop_count number :=run_duration/capture_gap;
rdio number;
wtio number;
prev_rdio number :=0;
Prev_wtio number :=0;
rdbt number;
wtbt number;
prev_rdbt number;
Prev_wtbt number;
BEGIN
FOR i in 1..loop_count LOOP
SELECT SUM(value) INTO rdio from gv$sysstat WHERE name =’physical read total IO requests’;
SELECT SUM(value) INTO wtio from gv$sysstat WHERE name =’physical write total IO requests’;
SELECT SUM(value)* 0.000008 INTO rdbt from gv$sysstat WHERE name =’physical read total bytes’;
SELECT SUM(value)* 0.000008 INTO wtbt from gv$sysstat WHERE name =’physical write total bytes’;
IF i > 1 THEN
INSERT
INTO PEAK_IOPS_MEASUREMENT
(
CAPTURE_TIMESTAMP,
TOTAL_READ_IO,
TOTAL_WRITE_IO,
TOTAL_IO,
TOTAL_READ_BYTES,
TOTAL_WRITE_BYTES,
TOTAL_BYTES
)
VALUES
(
SYSDATE,
( RDIO-PREV_RDIO)/5,
(WTIO-PREV_WTIO)/5,
((RDIO-PREV_RDIO)/5)+((WTIO-PREV_WTIO))/5,
(RDBT-PREV_RDBT)/5,
(WTBT-PREV_WTBT) /5,
((RDBT-PREV_RDBT)/5)+((WTBT-PREV_WTBT))/5);
END IF;
prev_rdio := rdio;
prev_wtio := wtio;
prev_rdbt := rdbt;
prev_wtbt := wtbt;
DBMS_LOCK.SLEEP(capture_gap);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
/
– Check the IOPS with below query
SELECT MAX(total_io) PeakIOPS FROM peak_iops_measurement;
Using the max iops and max mbps you may select the right storage and instance type.
Example: To support 667*10%* around 700 IOPS you may select a GP2 SSD type storage in AWS with 250 GB (it gives approximately 750 IOPS) or you can lock down 250 GB IO storage with 1000 PIOPS.
* To cover for any unforeseen spikes, it would be a good idea to add an additional 10 percent to this peak IOPS number to account for the actual IOPS needed for your database. Also you may add for writes as well.
Oracle Applications, OBIEE, EPM and FMW DBA 