Re: Help me tuning this wait event:log file sync

From: chao_ping <chao_ping_at_163.com>
Date: 14 Jul 2002 07:13:13 GMT
Message-ID: <3d312489$1@usenetgateway.com>

Yong Huang wrote:
> [Sorry if you see my post several times. Google stops working and
> Mailgate keeps asking for loging]
> If you can't control the frequency of commits, reduce log_buffer. How
> big is it?
> now log buffer is 2M, In early time, it was 1M, and i see much wait time
> in log buffer space,(once it was the 4th top wait event), so i enlarged
> the log buffer size from 1m to 2m.
> Your post is messed up in format. In 2002/08/28 post,
> log file sync 2,247,585 20,529,779 63.71
> you mean the total wait time is only 63.71 centiseconds? That's nothing
> unless this is measured right after the database is started.
> Yong Huang

The wait time is different because the time between the snapshot is not identical. The last snapshot:
2002/07/11

2002/08/11 ________________Snap_Id_____Snap_Time______Sessions
________________-------_------------------_--------
_Begin_Snap:________693_11-Jul-02_13:45:10______336

___End_Snap:________694_11-Jul-02_17:03:51______336

____Elapsed:_________________198.68_(mins)

Top_5_Wait_Events

~~~~~~~~~~~~~~~~~__________________________Wait_____%_Total
Event_____________________________________Waits__Time_(cs)___Wt_Time

log_file_sync____________________________453,862____9,679,513___68.11
db_file_sequential_read__________________500,157____1,660,982___11.69
db_file_parallel_write___________________67,235____1,137,017____8.00
log_file_parallel_write___________________163,180____1,075,201____7.57
log_file_sequential_read___________________32,012______254,327____1.79

It is about 3 hours' time, and the log file wait time is 9,679,513 cs. that is 96795second?(I think the value is caculated after the database boot, it was 2002/6/29, and the record time is 2002/7/11, only 12 days?, right) The 63.71 is the percent of time spent on log file wait event. And now the time spent on log file sync wait is 68.11%. It seems terrible.

Maybe reduce log buffer size reduce this wait event, but i am not sure whether log buffer space will occur again?

sigh.Performance tuning...

--
an oracle fan, 
an oracle beginner

Posted via dBforums
http://dbforums.com

Received on Sun Jul 14 2002 - 02:13:13 CDT