Re: "db file async I/O submit" wait event : 11gR2
- From: Yadavalli Aditya <aditya.yadavalli@xxxxxxxxx>
- To: tim@xxxxxxxxx
- Date: Wed, 28 Jul 2010 18:27:15 -0700
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@xxxxxxxxx> 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@xxxxxxxxx
> 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@xxxxxxxxx> 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
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>
>
>
> --
> Thanks & Regards,
> Taral Desai
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
