linux oracle io 查看,Linux上Oracle是否使用异步io的诊断

客户的数据库IO负载较重，检查后发现并未设置异步IO。

整个数据库的负载都集中在IO相关的等待上：

Top 5 Timed Foreground Events

Event

Waits

Time(s)

Avg   wait (ms)

%   DB time

Wait   Class

log   file sync

697,116

70,128

101

36.29

Commit

db   file sequential read

2,982,135

54,498

18

28.20

User   I/O

db   file scattered read

754,680

38,741

51

20.05

User   I/O

free   buffer waits

35,410

20,560

581

10.64

Configuration

DB   CPU

6,172

3.19

检查后发现，当前系统并未使用异步io：

[oracle@localhost ~]$ cat /proc/slabinfo | grep kio

kioctx         37   140   384   10   1 : tunables   54   27   8 : slabdata    14    14     0

kiocb           0     0   256   15   1 : tunables  120   60   8 : slabdata     0     0     0

[oracle@localhost ~]$ cat /proc/slabinfo | grep kio

kioctx         37   140   384   10   1 : tunables   54   27   8 : slabdata    14    14     0

kiocb           0     0   256   15   1 : tunables  120   60   8 : slabdata     0     0     0

返回结果中kiocp对应的前两项为0，说明系统中没有使用异步io。

检查显示oracle已经链接了aio的包：

[oracle@localhost ~]$ /usr/bin/ldd $ORACLE_HOME/bin/oracle | grep libaio

libaio.so.1 => /lib64/libaio.so.1 (0x0000003e13000000)

[oracle@localhost ~]$ /usr/bin/ldd $ORACLE_HOME/bin/oracle | grep libaio

libaio.so.1 => /lib64/libaio.so.1 (0x0000003e13000000)

而数据库中检查发现filesystemio_options设置有误：

SQL> show parameter disk_asynch_io

NAME                                 TYPE                             VALUE

------------------------------------ -------------------------------- --------------------

disk_asynch_io                       BOOLEAN                          TRUE

SQL> SHOW parameter filesystemio_options

NAME                                 TYPE                             VALUE

------------------------------------ -------------------------------- -------------------

filesystemio_options                 string                           NONE

NAME                                 TYPE                             VALUE

------------------------------------ -------------------------------- --------------------

disk_asynch_io                       boolean                          TRUE

SQL> show parameter filesystemio_options

NAME                                 TYPE                             VALUE

------------------------------------ -------------------------------- -------------------

filesystemio_options                 string                           none

当前使用的是文件系统，因此需要将filesystemio_options设置为asynch，才能开启异步io：

SQL> ALTER SYSTEM SET FILESYSTEMIO_OPTIONS = ASYNCH SCOPE = SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE

DATABASE closed.

DATABASE dismounted.

ORACLE instance shut down.

SQL> STARTUP

ORACLE instance started.

Total System Global Area 6.0264E+10 bytes

Fixed SIZE 2242912 bytes

Variable SIZE 2147485344 bytes

DATABASE Buffers 5.7982E+10 bytes

Redo Buffers 131960832 bytes

DATABASE mounted.

DATABASE opened.

SQL>

SQL> ALTER SYSTEM SET FILESYSTEMIO_OPTIONS = ASYNCH SCOPE = SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP

ORACLE instance started.

Total System Global Area 6.0264E+10 bytes

Fixed Size 2242912 bytes

Variable Size 2147485344 bytes

Database Buffers 5.7982E+10 bytes

Redo Buffers 131960832 bytes

Database mounted.

Database opened.

SQL>

再次检查系统上异步io的设置，发现异步io已经启动：

[oracle@localhost ~]$ more /proc/slabinfo |grep kio

kioctx       130   160   384   10   1 : tunables   54   27   8 : slabdata    16    16     0

kiocb         16    30   256   15   1 : tunables  120   60   8 : slabdata     2     2     1

[oracle@localhost ~]$ more /proc/slabinfo |grep kio

kioctx       130   160   384   10   1 : tunables   54   27   8 : slabdata    16    16     0

kiocb         16    30   256   15   1 : tunables  120   60   8 : slabdata     2     2     1

可以看到，目前异步IO已经生效。