Wednesday, 12 March 2014

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;


   

No comments:

Post a Comment