Oracle 11g/ 12c 如何簡單的測試Storage Array 效能

Prerequisites for I/O Calibration:

Before running I/O calibration, ensure that the following requirements are met:

The user must be granted the SYSDBA privilege

timed_statistics must be set to TRUE(default)

Asynchronous I/O must be enabled

Oracle 11g 如何簡單的測試Storage Array 效能

1.檢查是否有開啟async IO

SQL>

COL NAME FORMAT A50

SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I WHERE  F.FILE#=I.FILE_NO

AND    FILETYPE_NAME='Data File';

SQL>

+DATA/xxx/DATAFILE/tbs2.295.902228267

ASYNC_OFF

2.修改filesystemio_options=setAll

3.重新啟動資料庫instance (filesystemio_options是靜態參數, 須重啟才會生效)

4. 檢查是否有開啟async IO

SQL>

COL NAME FORMAT A50

SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I WHERE  F.FILE#=I.FILE_NO

AND    FILETYPE_NAME='Data File';

SQL>

+DATA/xxx/DATAFILE/tbs2.295.902228267

ASYNC_ON

5.開始進行測試

set serveroutput on

declare

v_max_iops number;

v_max_mbps number;

v_actual_latency number;

begin

dbms_resource_manager.calibrate_io(num_physical_disks=>2,max_latency=>10,max_iops=>v_max_iops,max_mbps=>v_max_mbps,actual_latency=>v_actual_latency); 

 dbms_output.put_line('IOPS:'||v_max_iops);

dbms_output.put_line('MBPS:'||v_max_mbps);

dbms_output.put_line('ACTUAL_LATENCY:'||v_actual_latency);

end;

/

IOPS:99     --OLTP要求此數字越高越好

MBPS:38     --DW要求此數字越高越好

ACTUAL_LATENCY:9  --OLTP要求此數字越小越好

PL/SQL procedure successfully completed.


ref:
其他Tuning建議:
init.ora settings
_high_priority_processes='LMS*|LGWR|PMON'
filesystemio_options = SETALL

log_buffer = {at least 15MB}

https://support.purestorage.com/Solutions/Databases/Oracle_Database/Oracle_Recommended_Settings
https://oracle-base.com/articles/misc/measuring-storage-performance-for-oracle-systems#orion