IO calibrate statistics are missing in oracle explain plan
IO calibrate statistics are missing in explain plan, then below mentioned steps should be executed one by one :-
set these parameters
1. alter system set filesystemio_options=asynch scope=spfile
2. alter system set disk_asynch_io = true scope=spfile;
3. bounce the db
4. Ensure asynchronous I/O is enabled on all datafiles and tempfiles. The following query can be used to verify asynchronous I/O for these files.
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' or filetype_name='Temp File');
/
5. run the calibration procedure :-
SET SERVEROUTPUT ON
DECLARE
l_max_iops PLS_INTEGER;
l_max_mbps PLS_INTEGER;
l_actual_latency PLS_INTEGER;
BEGIN
DBMS_RESOURCE_MANAGER.calibrate_io (
num_physical_disks => 1,
max_latency => 20,
max_iops => l_max_iops,
max_mbps => l_max_mbps,
actual_latency => l_actual_latency);
DBMS_OUTPUT.put_line ('l_max_iops = ' || l_max_iops);
DBMS_OUTPUT.put_line ('l_max_mbps = ' || l_max_mbps);
DBMS_OUTPUT.put_line ('l_actual_latency = ' || l_actual_latency);
END;
Note :- Here num_physical_disks should be confirmed from system admin and This should be set to the defined response time SLA for your application;
e.g., your 95th percentile response time SLA is 10secs.
6. then find the status of the calibration runs:- select * from v$io_calibration_status;
set these parameters
1. alter system set filesystemio_options=asynch scope=spfile
2. alter system set disk_asynch_io = true scope=spfile;
3. bounce the db
4. Ensure asynchronous I/O is enabled on all datafiles and tempfiles. The following query can be used to verify asynchronous I/O for these files.
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' or filetype_name='Temp File');
/
5. run the calibration procedure :-
SET SERVEROUTPUT ON
DECLARE
l_max_iops PLS_INTEGER;
l_max_mbps PLS_INTEGER;
l_actual_latency PLS_INTEGER;
BEGIN
DBMS_RESOURCE_MANAGER.calibrate_io (
num_physical_disks => 1,
max_latency => 20,
max_iops => l_max_iops,
max_mbps => l_max_mbps,
actual_latency => l_actual_latency);
DBMS_OUTPUT.put_line ('l_max_iops = ' || l_max_iops);
DBMS_OUTPUT.put_line ('l_max_mbps = ' || l_max_mbps);
DBMS_OUTPUT.put_line ('l_actual_latency = ' || l_actual_latency);
END;
Note :- Here num_physical_disks should be confirmed from system admin and This should be set to the defined response time SLA for your application;
e.g., your 95th percentile response time SLA is 10secs.
6. then find the status of the calibration runs:- select * from v$io_calibration_status;
Comments
Post a Comment