Re: db_writer_processes and Async IO ???

From: Tanel Poder <tanel_at_poderc.com>
Date: Wed, 19 Aug 2009 22:26:29 +0800
Message-ID: <4602f23c0908190726i74b71f38veb6fda395b71e249_at_mail.gmail.com>

Asynch IO and multiple DB writers are *not* mutually exclusive.

Its just that you shouldn't likely use multiple DBWRs if you have working asynch IO. DBWR is not that CPU intensive so you won't hit a CPU bottleneck. There may be some reason to use multiple DBWRs (with asynch IO) if all these conditions are met:

1. Server uses NUMA architecture with bad remote memory access latency compared to local access (btw, latest Xeon Nehalems have pretty good remote memory access latency)
2. The OS understands and supports NUMA optimizations and placement
3. The Oracle port for that OS and platform has these platform-specific NUMA optimizations built in

Kevin closson has lots of details about this:

http://kevinclosson.wordpress.com/kevin-closson-index/oracle-on-opteron-k8l-numa-etc/

Tanel Poder
http://blog.tanelpoder.com

1)On Tue, Aug 18, 2009 at 1:16 AM, Jared Still <jkstill_at_gmail.com> wrote:

> On Mon, Aug 17, 2009 at 9:39 AM, Jared Still <jkstill_at_gmail.com> wrote:
>
>> On Sat, Aug 15, 2009 at 3:36 PM, dba1 mcc <mccdba1_at_yahoo.com> wrote:
>>
>>> db_writer_processes = 2
>>>
>>> filesystemio_options = asynch
>>> DISK_ASYNCH_IO = true
>>> TAPE_ASYNCH_IO = true
>>>
>>> Based on what I know "db_writer_processes " and "filesystemio_options =
>>> asynch" are exclusive. Can anyone tell me database will use whiche one?
>>>
>>
>> This should help answer your question:
>>
>> ps -flu<oracle_owner> | grep dbw.*$ORACLE_SID
>>
>>
> After re-reading this I realized it wasn't terrible helpful.
>
> Here's a test I performed:
>
> This is 10gR2 on Linux ES 4
>
> alter system set db_writer_processes=2 scope=spfile;
>
> verify the sync parameters:
>
> NAME
> VALUE VAL? MOD? MOD?
> -------------------------
> -------------------------------------------------- ---- ---- ----
> disk_asynch_io
> TRUE Y N N
> tape_asynch_io
> TRUE Y N N
> filesystemio_options
> ASYNCH N N N
>
> restart the database in nomount mode
> start a trace on each of the 2 db writer processes
>
> alter database mount
> alter database open
>
> logon and create a table:
>
> create table xxx
> as
> select * from dba_objects
> where 1=0;
>
> insert /*+ append */ into xxx
> select * from dba_objects
> /
>
> Now shutdown the database.
>
> Examine the trace files and find lots of io_submit calls
> Linux man page says that this is an async_io call.
>
> So, it would appear that both multiple db writers and
> async IO are being used.
>
> One parameter does not preclude the use of the other.
>
> I am sure someone else can give a better explanation of
> the async IO calls, as I only know what I read in the man
> page about them
>
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>
>

-- 
Tanel Poder
http://blog.tanelpoder.com

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Aug 19 2009 - 09:26:29 CDT