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;


   

Comments

Popular posts from this blog

Installing DBMS_JAVA package in Oracle and calling UTL_DBWS web services through Oracle database

stick the job to the specific instance in Oracle RAC

finding fragmentation at table level and tablespace level in Oracle and steps to remove them