一.背景
oracle官方提供了DBMS_RESOURCE_MANAGER.CALIBRATE_IO存儲(chǔ)過程,用于測(cè)試當(dāng)前數(shù)據(jù)庫實(shí)例的I/O性能。
二.介紹
2.1.DBMS_RESOURCE_MANAGER.CALIBRATE_IO參數(shù)如下:
DBMS_RESOURCE_MANAGER.CALIBRATE_IO?( num_physical_disks ? IN PLS_INTEGER DEFAULT 1?, max_latency ? IN PLS_INTEGER DEFAULT? 20, max_jobs ? OUT PLS_INTEGER, max_mbps ? OUT PLS_INTEGER, actual_latency ? OUT PLS_INTEGER?);
2.2.常用的輸入?yún)?shù)具體如下
- num_physical_disks:當(dāng)前系統(tǒng)中存儲(chǔ)盤的個(gè)數(shù),根據(jù)實(shí)際情況填寫即可。
- max_latency:所能容忍的最大延遲(單位為ms),對(duì)于OLTP系統(tǒng),建議將該值設(shè)置在20以內(nèi)。
2.3.測(cè)試之前需要確認(rèn)當(dāng)前環(huán)境是否開啟了異步I/O,命令如下:
SQL>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
+DATA/jason/datafile/system01. dbf ASYNCOFF
+DATA/jason/datafile/sysaux01. dbf ASYNCOFF
+DATA/jason/datafile/undotbs01. dbf ASYNCOFF
+DATA/jason/datafile/users01. dbf ASYNCOFF
--AYNC_OFF表示未開啟,開啟異步I/O(需要重啟數(shù)據(jù)庫才能生效)
SQL>?ALTER SYSTEM SET? ?filesystemio_options=setall SCOPE=SPFILE;
SQL>set ?serveroutput? on;
SQL>DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
DBMS_RESOURCE_MANAGER.CALIBRATE_IO(1, 20, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE('max _ iops=' || /iops);
DBMS_OUTPUT.PUT_LINE('latency=' || 1at);
dbms_output.put line('max _ mbps=' || mbps);
end;
2.4.
輸出結(jié)果具體如下:
- Max IOPS=51800 --表示每秒可以維持的最大I/O請(qǐng)求數(shù)。
- Max MBPS = 654 --表示可以維持的最大I/O吞吐量
- Latency = 8 --actual_latency:以max_iops表示當(dāng)前I/O請(qǐng)求的平均延遲,單位為ms。
同樣,我么也可以通過視圖v$io_calibration_status查看測(cè)試結(jié)果。
三.以下是我在墨天輪21c實(shí)訓(xùn)環(huán)境測(cè)試的過程:
SQL> set line 200
SQL> col name for a120
SQL> 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'; 2 3 4
NAME ASYNCH_IO
------------------------------------------------------------------------------------------------------------------------ ---------
/data/app/oracle/oradata/orcl/system01.dbf ASYNC_OFF
/data/app/oracle/oradata/orcl/sysaux01.dbf ASYNC_OFF
/data/app/oracle/oradata/orcl/undotbs01.dbf ASYNC_OFF
/data/app/oracle/oradata/orcl/users01.dbf ASYNC_OFF
/data/app/oracle/oradata/orcl/example01.dbf ASYNC_OFF
SQL>
ALTER SYSTEM SET filesystemio_options=setall SCOPE=SPFILE;
SQL> ALTER SYSTEM SET filesystemio_options=setall SCOPE=SPFILE;
System altered.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1586708480 bytes
Fixed Size 2253624 bytes
Variable Size 989859016 bytes
Database Buffers 587202560 bytes
Redo Buffers 7393280 bytes
Database mounted.
Database opened.
SQL> SQL> SQL> set line 200
SQL> col name for a120
SQL> 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'; 2 3 4
NAME ASYNCH_IO
------------------------------------------------------------------------------------------------------------------------ ---------
/data/app/oracle/oradata/orcl/system01.dbf ASYNC_ON
/data/app/oracle/oradata/orcl/sysaux01.dbf ASYNC_ON
/data/app/oracle/oradata/orcl/undotbs01.dbf ASYNC_ON
/data/app/oracle/oradata/orcl/users01.dbf ASYNC_ON
/data/app/oracle/oradata/orcl/example01.dbf ASYNC_ON
SQL>
set serveroutput on;
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
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> set serveroutput on;
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
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> 2 3 4 5 6 7 8 9 10 11
max_iops = 4993
latency = 18.614
max_mbps = 146
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 may benefit from
the storage cache.
max_iops = 4993
latency= 19
max_mbps = 146
PL/SQL procedure successfully completed.
最后修改時(shí)間:2022-04-24 15:33:30
「喜歡這篇文章,您的關(guān)注和贊賞是給作者最好的鼓勵(lì)」
關(guān)注作者
【版權(quán)聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容,轉(zhuǎn)載時(shí)必須標(biāo)注文章的來源(墨天輪),文章鏈接,文章作者等基本信息,否則作者和墨天輪有權(quán)追究責(zé)任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權(quán)的內(nèi)容,歡迎發(fā)送郵件至:contact@modb.pro進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),墨天輪將立刻刪除相關(guān)內(nèi)容。




