Recently during one Performance Problem i have attached one better SQL Profile to the statement which includes to add DOP (Degree Of Parallelism) to reduce the impacts of a definite FTS (Full Table Scan) but found one NOTE coming during the execution plan generation which reads
“automatic DOP: skipped because of IO calibrate statistics are missing”

SQL> execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_33935’,task_owner => ‘SYS’, replace => TRUE, profile_type =>DBMS_SQLTUNE.PX_PROFILE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.86
SQL> explain plan for select count(*) from DIXIT_EVW_ETAILQ;

Explained.

Elapsed: 00:00:00.03
SQL> @xplan

PLAN_TABLE_OUTPUT
———————————————————————————————————————————————————————–

————————————————————————————————————————————-
Plan hash value: 584586630

—————————————————————————————————————-
| Id  | Operation              | Name             | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
—————————————————————————————————————-
|   0 | SELECT STATEMENT       |                  |     1 | 36776   (1)| 00:07:22 |        |      |            |
|   1 |  SORT AGGREGATE        |                  |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |                  |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000         |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |                  |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |                  |   177M| 36776   (1)| 00:07:22 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| DIXIT_EVW_ETAILQ |   177M| 36776   (1)| 00:07:22 |  Q1,00 | PCWP |            |
—————————————————————————————————————-

Note
—–
   – automatic DOP: skipped because of IO calibrate statistics are missing
   – SQL profile “SYS_SQLPROF_0148a0b0821b0005” used for this statement

The ‘AUTOMATIC DOP’ is skipped because I/O calibration is not run to gather the required statistics. Required statistics can be collected using DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure.

SQL> select status from V$IO_CALIBRATION_STATUS;

STATUS
——————————-
NOT AVAILABLE

DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
–DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (28, 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;
/

max_iops = 5944
latency = 9
max_mbps = 75

18 rows selected.

Elapsed: 00:00:00.11

Issue:
If using DBMS_RESOURCE_MANAGER.CALIBRATE_IO there are times when you might recieve beloe error message
ORA-56708: Could not find any datafiles with asynchronous i/o capability

Resolution:
Then we need to enable asynch I/O, set below two values to mentioned settings in the init.ora file.

disk_asynch_io = true
filesystemio_options = asynch

SQL> select status from V$IO_CALIBRATION_STATUS;

STATUS
——————–
AVAILABLE

Now you can implement the DOP for the query and this way you can reduce FTS impacts.

Hope That Helps
Prashant Dixit