Thursday, March 31, 2011

automatic DOP: skipped because of IO calibrate statistics are missing

just tested Automatic DOP and found "automatic DOP: skipped because of IO calibrate statistics are missing" on explain plan output.
SQL> alter session set parallel_degree_policy=AUTO;

Session altered.

SQL> explain plan for SELECT empno, ename from emp;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1571304207
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 140 | 22 (0)| 00:00:01 | | |
| 1 | PARTITION HASH ALL| | 14 | 140 | 22 (0)| 00:00:01 | 1 | 4 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 140 | 22 (0)| 00:00:01 | 1 | 4 |
-------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
automatic DOP: skipped because of IO calibrate statistics are missing
Because I/O calibration is not run to gather the required statistics. I/O calibration statistics can be gathered with the PL/SQL DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure.
SQL> select status from V$IO_CALIBRATION_STATUS;

STATUS
-------------
NOT AVAILABLE
then used DBMS_RESOURCE_MANAGER.CALIBRATE_IO Procedure.
SQL> SET SERVEROUTPUT ON
SQL> 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

PL/SQL procedure successfully completed.
if using DBMS_RESOURCE_MANAGER.CALIBRATE_IO and error ORA-56708: Could not find any datafiles with asynchronous i/o capability
Need to enable asynch I/O, set two values in the init.ora file.
disk_asynch_io = true
filesystemio_options = asynch
After used CALIBRATE_IO, then
SQL> select status from V$IO_CALIBRATION_STATUS;

STATUS
-------------
READY

SQL> explain plan for SELECT empno, ename from emp;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1571304207
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 140 | 22 (0)| 00:00:01 | | |
| 1 | PARTITION HASH ALL| | 14 | 140 | 22 (0)| 00:00:01 | 1 | 4 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 140 | 22 (0)| 00:00:01 | 1 | 4 |
-------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

2 comments:

Anonymous said...

Works well.

Thank you !

oraculix said...

Surachart,

just as a sidenote: You don't necessarily need to set "disk_asynch_io=true" to run the calibration. At least if you use file systems, setting "filesystemio_options=SETALL" (or asynch) should be enough according to the Oracle docs - and I just verified that, again.

Cheers,
Uwe