Re: db_writer_processes and Async IO ???

From: Jared Still <jkstill_at_gmail.com>
Date: Mon, 17 Aug 2009 10:16:01 -0700
Message-ID: <bf46380908171016v291812f0wc822f73eebc5340c_at_mail.gmail.com>

 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

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

Received on Mon Aug 17 2009 - 12:16:01 CDT