弹冠相庆

October 10, 2010

Oracle10gR2在RHEL 5下开启DIRECT IO

Filed under: Uncategorized — Corey @ 23:01
Tags: ,

平台: 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

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: