Search This Blog

Loading...

Tuesday, March 11, 2014

ASYNC IO is not working in 11.2.0.4.3 (January 2014 quarterly full stack patch)

After applying to the Exadata the January 2014 quarterly full stack patch and 11.2.0.4.3 RDBMS we run the IO Calibration dbms_resource_manger.calibrate_io(). It generated the massages in alert log trace files:

calibrate_io (kcfcagmdf): WARNING: ASync I/O not possible for datafile with file number (fno) [1]
calibrate_io (kcfcagmdf): WARNING: ASync I/O not possible for datafile with file number (fno) [2]
calibrate_io (kcfcagmdf): WARNING: ASync I/O not possible for datafile with file number (fno) [3]
calibrate_io (kcfcagmdf): WARNING: ASync I/O not possible for datafile with file number (fno) [4]


We checked the DB parameters:

SQL> show parameter filesystemio

NAME                 TYPE   VALUE
-------------------- ------ ------
filesystemio_options string setall

SQL> show parameter disk_asynch_io

NAME           TYPE    VALUE
-------------- ------- -------
disk_asynch_io boolean TRUE


We checked the  ASYNC IO inside the DB:

SQL> 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';

NAME                                          ASYNCH_IO
--------------------------------------------- ---------
+DATAC1/migdb/datafile/system.256.839351447   ASYNC_OFF
+DATAC1/migdb/datafile/sysaux.257.839351449   ASYNC_OFF
+DATAC1/migdb/datafile/undotbs1.258.839351449 ASYNC_OFF
+DATAC1/migdb/datafile/users.259.839351449    ASYNC_OFF


Then we checked the libaio libraries in Linux (they are well):

[root@dm01dbadm01 ~]# rpm -qa|grep libaio
libaio-0.3.106-5
libaio-devel-0.3.106-5
libaio-devel-0.3.106-5
libaio-0.3.106-5
[root@dm01dbadm01 ~]# find / -name libaio*
/usr/lib64/libaio.so.1
/usr/lib64/libaio.so.1.0.0
/usr/lib64/libaio.so.1.0.1
/usr/lib64/libaio.a
/usr/lib64/libaio.so
/usr/lib/libaio.so.1.0.0
/usr/lib/libaio.so.1
/usr/lib/libaio.so.1.0.1
/usr/lib/libaio.a
/usr/lib/libaio.so
/usr/share/doc/libaio-0.3.106
/usr/include/libaio.h
/u01/app/oracle/product/11.2.0.4/dbhome_1/lib/stubs/libaio.so.1
/u01/app/oracle/product/11.2.0.4/dbhome_1/lib/stubs/libaio.so
/u01/app/oracle/product/11.2.0.4/dbhome_1/lib/stubs/libaio-2.3.4-stub.so
/u01/app/11.2.0.4/grid/lib/stubs/libaio.so.1
/u01/app/11.2.0.4/grid/lib/stubs/libaio.so
/u01/app/11.2.0.4/grid/lib/stubs/libaio-2.3.4-stub.so


And the async syscall statistics:

# cat /proc/slabinfo|grep kio
kioctx 86 110 384 10 1 : tunables 54 27 8 : slabdata 11 11 0
kiocb 0 0 256 15 1 : tunables 120 60 8 : slabdata 0 0 0
# more /proc/sys/fs/aio-max-nr
3145728
# more /proc/sys/fs/aio-nr
11008


So, we understand - we loosed the ASYNC IO in the Exadata.

Solution:  Patch 16618055 :

The fix Patch 16618055 : PHSB: EXADATA DOESN'T SUPPORT ASYNC I/O is now available for download on top of 11.2.0.4.3

This patch is online installable, we installed it.
In the alert log we see:
Tue Mar 11 13:15:02 2014
Patch bug16618055.pch Installed - Update #1
Patch bug16618055.pch Enabled - Update #2
Tue Mar 11 13:15:05 2014
Online patch bug16618055.pch has been installed
Online patch bug16618055.pch has been enabled
 

But the DB showed ASYNC_OFF
NAME                                          ASYNCH_IO
--------------------------------------------- ---------
+DATAC1/migdb/datafile/system.256.839351447   ASYNC_OFF
+DATAC1/migdb/datafile/sysaux.257.839351449   ASYNC_OFF
+DATAC1/migdb/datafile/undotbs1.258.839351449 ASYNC_OFF
+DATAC1/migdb/datafile/users.259.839351449    ASYNC_OFF


After shutdown the ASYNC become ON !
 

Wednesday, March 5, 2014

Is CREATE INDEX smart-scanable ?

The answer is YES !
Look:



set timing on
CREATE UNIQUE INDEX "DW_STG"."UK#CALLS_00" ON "DW_STG"."CALLS_00" ("CALL_ID")   PCTFREE 0 INITRANS 24 MAXTRANS 255 PARALLEL 64  ;




You can see the column "Cell Offloading" = 95%