Yadavalli Aditya | 29 Jul 03:27 2010
Picon

Re: "db file async I/O submit" wait event : 11gR2

Thank Tim/Taral/Frits for your time and responses. I am running huge
dataloads, almost 8G data was loaded in last 1.5Hrs.

Tim,

Here is the info.

OS/platform and version : SUSE Linux Enterprise Server 10 SP3,
2.6.16.60-0.54.5-smp x86_64
Datafile and logfiles are on OS Filesystem, its type is : xfs
DB Version: 11.2.0.1.0.

 NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io                       boolean     TRUE
dbwr_io_slaves                       integer     0
filesystemio_options                 string      none
db_writer_processes                  integer     2

I changed parameter filesystemio_options to async, after that "db file
async I/O submit " wait event disappeared and observed "log file
switch (checkpoint incomplete)" consuming 89% almost as below.

 Top 5 Timed Events
Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
log file switch (checkpoint incomplete)             15         280  18674   88.6
CPU time                                                        26           8.3

Increased redo logs size to 500M, after that i am seeing free_buffer_waits.

op 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
free buffer waits                                  117         195   1669   84.8
CPU time                                                        30          13.2

Performance has not much improved overall, compared to another 9i
instance which is having less system resources and buffer sizes the
current instance is taking twice the time.  Only the difference is
that top wait event is changing ... still researching the issue..

Cheers/Aditya

On Wed, Jul 28, 2010 at 12:53 PM, Tim Gorman <tim@...> wrote:
> Taral and Aditya,
>
> The two top wait events may well be related, but the suggested "resolution"
> to increase time between log switches (which is assuming that checkpoints do
> not happen more frequently than at log switch) is -- at best -- somewhat
> like suggesting that applying compression to a gushing arterial rupture is a
> resolution.  It does not address the underlying problem.
>
> It appears that the base problem involves asynchronous write I/O from DBWR
> activity, wouldn't you say?
>
> What is the OS/platform and version?  For datafiles, are you using "raw
> disk" partitions, Oracle ASM, or OS-filesystem?  If OS file-system, what
> file-system type?  What are your database parameter settings for
> DB_WRITER_PROCESSES, DISK_ASYNC_IO, FILESYSTEMIO_OPTIONS, and
> DBWR_IO_SLAVES, just for starters?
>
> Notice that you are seeing large waits on four (13) occurrences of "db file
> async I/O submit", averaging 4 secs per wait, but please also notice that
> "log file parallel write", which is write I/O from the LGWR process, seems
> to be operating much more normally with 1,452 occurrences averaging only
> 0.004 seconds per wait.  Are the online redo logfiles located on different
> devices or disk-groups or file-system mount-points than the datafiles?  Or,
> are they all located on the same devices, disk-groups, or file-system
> mount-points?  Dumping FILE_NAME from DBA_DATA_FILES and MEMBER from
> V$LOGFILE might be useful...
>
> Hope this helps...
>
> Tim Gorman
> consultant -> Evergreen Database Technologies, Inc.
> postal     => P.O. Box 630791, Highlands Ranch CO  80163-0791
> website    => http://www.EvDBT.com/
> email      => Tim@...
> mobile     => +1-303-885-4526
> fax        => +1-303-484-3608
> Lost Data? => http://www.ora600.be/ for info about DUDE...
>
> On 7/28/2010 1:10 PM, Taral Desai wrote:
>
> Well the real culprit would be log file switch (checkpoint incomplete)
> there is possibility that your i/o bandwidth is very low. You can try
> increasing logfile size to see if this can give some time to write to
> logfiles.
>
> On Wed, Jul 28, 2010 at 1:18 PM, Yadavalli Aditya
> <aditya.yadavalli@...> wrote:
>>
>> Hi All,
>>
>> I am seeing poor performance with one of the 11gR2 instances, and when
>> i ran statspack reports for the time period , i am seeing "db file
>> async I/O submit" wait event on the top hitting almost 56%. I have
>> checked metalink as well as google, did not get any information.
>> Please share your experiences if any one has seen this before. here is
>> the snapshot (numbers are galbled, please excuse!)
>>
>>
>> Top 5 Timed Events                                                    Avg
>> %Total
>> ~~~~~~~~~~~~~~~~~~                                                   wait
>>   Call
>> Event                                            Waits    Time (s)   (ms)
>>   Time
>> ----------------------------------------- ------------ ----------- ------
>> ------
>> db file async I/O submit                            13          53   4068
>>   56.2
>> log file switch (checkpoint incomplete)              4          27   6808
>>   28.9
>> CPU time                                                         6
>>   6.2
>> log file parallel write                          1,452           5      4
>>    5.8
>> control file parallel write                         65           1     20
>>    1.4
>>
>> Thank you,
>> Aditya
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>
>
>
> --
> Thanks & Regards,
> Taral Desai
>
--
http://www.freelists.org/webpage/oracle-l


Gmane