One of the long time problems with Oracle is IO calibration. I am talking, of course, about DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure. The most problematic aspect of this procedure is the fact that the results are not repeatable. It’s like rolling of the dice, it doesn’t really measure anything. I hoped that Oracle 18c will finally fix that, but no such luck:
[oracle@ora18c ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 – Production on Tue Sep 25 16:33:46 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Version 18.3.0.0.0
SQL> set serveroutput on
set timing on
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
— DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 20, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE (‘max_iops = ‘ || iops);
DBMS_OUTPUT.PUT_LINE (‘latency = ‘ || lat);
DBMS_OUTPUT.PUT_LINE (‘max_mbps = ‘ || mbps);
end;
/
SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12
max_iops = 154
latency = 19
max_mbps = 18579
Note: The high I/O latencies from the calibration run indicate that the
calibration I/Os are being serviced mostly from disk. If your storage has a
cache, you may achieve better results by rerunning. Rerunning will warm or
populate the storage cache.
max_iops = 154
latency = 19
max_mbps = 18579
PL/SQL procedure successfully completed.
Elapsed: 00:04:31.86
SQL> SQL> /
max_iops = 1353418
latency = 0
max_mbps = 18850
max_iops = 1353418
latency = 0
max_mbps = 18850
PL/SQL procedure successfully completed.
Elapsed: 00:08:30.15
SQL> /
max_iops = 1351136
latency = 0
max_mbps = 18738
max_iops = 1351136
latency = 0
max_mbps = 18738
PL/SQL procedure successfully completed.
Elapsed: 00:09:00.19
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 3707762528 bytes
Fixed Size 8902496 bytes
Variable Size 855638016 bytes
Database Buffers 2835349504 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL> set serveroutput on
set timing on
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
— DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 20, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE (‘max_iops = ‘ || iops);
DBMS_OUTPUT.PUT_LINE (‘latency = ‘ || lat);
DBMS_OUTPUT.PUT_LINE (‘max_mbps = ‘ || mbps);
end;
/
SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12
max_iops = 274
latency = 22
max_mbps = 83
Note: The high I/O latencies from the calibration run indicate that the
calibration I/Os are being serviced mostly from disk. If your storage has a
cache, you may achieve better results by rerunning. Rerunning will warm or
populate the storage cache.
max_iops = 274
latency = 22
max_mbps = 83
PL/SQL procedure successfully completed.
Elapsed: 00:08:35.87
SQL> SQL>
I’ve run 4 iterations, with an instance restart between the 3rd and the 4th iteration. If you check timing, one iteration takes between 8 and 9 minutes. However, all the results are drastically different, despite the fact that the measurements were taken on an idle instance on a virtual machine. To make things worse, calibration only remembers the last run:
SQL> desc RESOURCE_IO_CALIBRATE$
Name Null? Type
—————————————– ——– —————————-
START_TIME TIMESTAMP(6)
END_TIME TIMESTAMP(6)
MAX_IOPS NUMBER
MAX_MBPS NUMBER
MAX_PMBPS NUMBER
LATENCY NUMBER
NUM_DISKS NUMBER
ADDITIONAL_INFO VARCHAR2(1024)
SQL> select count(*) from RESOURCE_IO_CALIBRATE$;
COUNT(*)
———-
1
There is only a single row in RESOURCE_IO_CALIBRATE$ and this is only the last row:
SQL> set sqlformat ansiconsole
SQL> select * from RESOURCE_IO_CALIBRATE$;
START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NUM_DISKS ADDITIONAL_INFO
25-SEP-18 05.05.05.955884000 PM 25-SEP-18 05.13.41.349030000 PM 274 83 71 22 12
Note: The high I/O latencies from the calibration run indicate that the calibration I/Os are being serviced mostly from disk. If your storage has a cache, you may achieve better results by rerunning. Rerunning will warm or populate the storage cache.
SQL> select * from RESOURCE_IO_CALIBRATE$;
START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NUM_DISKS ADDITIONAL_INFO
25-SEP-18 05.05.05.955884000 PM 25-SEP-18 05.13.41.349030000 PM 274 83 71 22 12
Note: The high I/O latencies from the calibration run indicate that the calibration I/Os are being serviced mostly from disk. If your storage has a cache, you may achieve better results by rerunning. Rerunning will warm or populate the storage cache.
The IO calibration helps determine the automatic degree of parallelism, which makes it a rather critical procedure. However, the calibrating procedure doesn’t measure anything and the results are not repeatable. Unfortunately, even with 18c, we’ll have to do what I’ve been doing for some time: run Kevin Closson’s SLOB and update the table manually with the SLOB results.
Advertisements
