星期六, 1月 30, 2016

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

沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...