Only users with the SYSDBA privilege can run this procedure. Qualified users must also turn on
timed_statistics
,
and ensure asynch_io
is enabled for datafiles.
This can be achieved by setting filesystemio_options
to
either ASYNCH
or SETALL
.
One can also query the asynch_io
status by
means of the following SQL statement:首先要保证sysdba权限,同事确保用户timed_statistics开启。
oracle对wait event的统计有两种:
1、只统计wait event的等待的次数(TIMED_STATISTICS为false)
2、统计wait event的等待的时间和次数(TIMED_STATISTICS为true)
为了尽可能能获得wait event的运行情况,是要将值设置为true的。
举例来说:
一个等待事件的等待次数很多,但是实际上他的等待时间比较短,如2小时只等待了30秒,那么此事件可能根本不需要优化。
但是若一个等待事件的等待次数很少,但是其等待时间长,1小时中有45分钟在等待,那肯定是需要调查、优化的。
然后磁盘的状态开启asynch或者setall
可以show parameter filesystemio_options查看,一般设置成setall
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'
/
SELECT name, asynch_io FROM v$datafile f,v$iostat_file i
WHERE f.file# = i.file_no
AND filetype_name = 'Data File'
/
上面命令可以查看被检查的磁盘是否具备asynch_io条件
例子如下
SET SERVEROUTPUT 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 (2, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
DBMS_OUTPUT.PUT_LINE ('max_mbps = ' || mbps);
end;
/
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
DBMS_OUTPUT.PUT_LINE ('max_mbps = ' || mbps);
end;
/
注意rac模式下所有实例都会测试到
可以查看
SQL> select * from gv$io_calibration_status ;
INST_ID STATUS CALIBRATION_TIME CON_ID
---------- ------------- --------------------------------------------------------------------------- ----------
1 IN PROGRESS 17-NOV-17 09.58.55.192 AM 0
2 IN PROGRESS 17-NOV-17 09.58.55.192 AM 0
status分为in progress、ready、not aviliabe三种
测试过程中结果可以在DBA_RSRC_IO_CALIBRATE中查看
SQL> select * from DBA_RSRC_IO_CALIBRATE ;
START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NUM_PHYSICAL_DISKS
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ------------------
17-NOV-17 09.46.11.981720 AM 17-NOV-17 09.58.55.191600 AM 495643 7354 3765 0 24