Oracle10gR2在RHEL 5下开启DIRECT IO

平台: Red Hat Linux Enterprise 5.3 64 bit， Oracle 10gR2 10.2.0.4 64 bit

Oracle开启direct io前

SQL> show parameter filesystemio_options

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      ASYNCH

重启动oracle实例后，cached内存为160700KB
# free
             total       used       free     shared    buffers     cached
Mem:      32887744    4583296   28304448          0       2992     160700
-/+ buffers/cache:    4419604   28468140
Swap:      4192924          0    4192924

现在做一个大表查询
SQL> select /*+ full(channels) parallel(channels,4) */ count(*) from channels;
  COUNT(*)
----------
 793103894
Elapsed: 00:13:09.42

再看看内存使用情况，cached内存为9669284KB
# free
             total       used       free     shared    buffers     cached
Mem:      32887744   14155316   18732428          0      26484    9669284
-/+ buffers/cache:    4459548   28428196
Swap:      4192924          0    4192924

开启direct io
SQL> alter system set filesystemio_options=SETALL scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

把cached内存释放掉
# sync; echo 3 > /proc/sys/vm/drop_caches
# free
             total       used       free     shared    buffers     cached
Mem:      32887744    4374724   28513020          0        540      47532
-/+ buffers/cache:    4326652   28561092
Swap:      4192924          0    4192924

重启Oracle instance
SQL> startup
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  2089432 bytes
Variable Size             301993512 bytes
Database Buffers         3976200192 bytes
Redo Buffers               14684160 bytes
Database mounted.
Database opened.
SQL> show parameter filesystemio_options

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      SETALL

此时cached内存94612KB
# free
             total       used       free     shared    buffers     cached
Mem:      32887744    4503964   28383780          0       1872      94612
-/+ buffers/cache:    4407480   28480264
Swap:      4192924          0    4192924

再做一个同样的查询
SQL> select /*+ full(channels) parallel(channels,4) */ count(*) from channels;
  COUNT(*)
----------
 793103894
Elapsed: 00:03:37.87

速度快了不少，cached内存96872KB，基本不变
# free
             total       used       free     shared    buffers     cached
Mem:      32887744    4559484   28328260          0      43556      96872
-/+ buffers/cache:    4419056   28468688
Swap:      4192924          0    4192924

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/441887/viewspace-667369/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/441887/viewspace-667369/