MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Using CALIBRATE_IO

without comments

Using Oracle’s Resource Manager requires you to understand the IO dynamics. The first step requires you to run the CALIBRATE_IO procedure from the DBMS_RESOURCE_MANAGER package.

Oracle provides some great examples about how to use the CALIBRATE_IO procedure of the DBMS_RESOURCE_MANAGER package in the Oracle Database Database PL/SQL Packages and Types Reference. The CALIBRATE_IO procedure returns the best answer when you provide a valid number of files, which you can capture by querying the V$ASM_DISK view.

The following code queries the view and assigns the value to a session level variable:

CLEAR BREAKS
CLEAR COLUMNS
CLEAR COMPUTES
 
VARIABLE files NUMBER
 
BEGIN
  SELECT COUNT(DISTINCT name) disks
  INTO :files
  FROM v$asm_disk;
END;
/

When you have the number of files, you can calibrate the IO with the following anonymous block. The query should always work but just in case the NVL function on line 9 assigns the default number of files.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DECLARE
  lv_num_physical_disks BINARY_INTEGER; — v$asm_disk
  lv_max_latency BINARY_INTEGER := 10;
  lv_max_iops BINARY_INTEGER;
  lv_max_mbps BINARY_INTEGER;
  lv_actual_latency BINARY_INTEGER;
BEGIN
  /* Assign actual files to anonymous block variable. */
  lv_num_physical_disks := NVL(:files,2);
 
  /* Run the calibrate_io procedure. */
  DBMS_RESOURCE_MANAGER.CALIBRATE_IO(
      num_physical_disks => lv_num_physical_disks
    , max_latency => lv_max_latency
    , max_iops => lv_max_iops
    , max_mbps => lv_max_mbps
    , actual_latency => lv_actual_latency);
END;
/

You can query the results like this:

SELECT max_iops
,      max_mbps
,      max_pmbps
,      latency
,      num_physical_disks
FROM   dba_rsrc_io_calibrate;

It should show results like these:

MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NUM_PHYSICAL_DISKS
-------- -------- --------- ------- ------------------
    8894      443       294       9                 18

Hope this helps those using the CALIBRATE_IO procedure of the DBMS_RESOURCE_MANAGER package.

Written by maclochlainn

August 31st, 2015 at 8:59 pm