Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help me tuning this wait event:log file sync

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

From: chao_ping <chao_ping_at_163.com>
Date: 13 Jul 2002 07:15:45 GMT
Message-ID: <3d2fd3a1$2@usenetgateway.com>


Jonathan Lewis wrote:
> Log file sync can be very misleading.
> I note that log file WRITES do not appear as a problem. Remember that a
> log file write wait will cause a log file sync for every user who
> commits for the duration of that wait.
> So if you have two hundred very busy users committing very frequently,
> you could easily end up with log file sync showing up with 200 times the
> impact of log file sync.
> Look at the session level - is any one session being impacted
> significantly by log file sync ?

Thanks, it seems there is about 40 commits per second. I get this information from statspack: Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 76,336.86 2,208.94 Logical reads: 12,555.73 363.32 Some snapshot of v$session_wait:
SQL> select sid,event from v$session_wait where event not like 'rdbms%' SQL> and event not like 'SQL*N%' and sid>8;

       SID EVENT ----------
       ----------------------------------------------------------------
       198 log file sync 199 log file sync 200 log file sync 201 log
       file sync 222 log file sync 364 log file sync

6 rows selected.

SQL> /

       SID EVENT ----------
       ----------------------------------------------------------------
       48 log file sync 160 log file sync 347 log file sync

SQL> /

       SID EVENT ----------
       ----------------------------------------------------------------
       136 log file sync 190 log file sync 199 log file sync 200 log
       file sync 325 log file sync 347 log file sync 355 db file
       sequential read

7 rows selected.

SQL> /

       SID EVENT ----------
       ----------------------------------------------------------------
       200 log file sync 325 log file sync

I don't quite understand your words"Look at the session level - is any one session being impacted significantly by log file sync ?", we are using tuxedo middleware and do users do not directly connect to database. So it is hard to find how much impact on end users(web browser). I think for one user, the wait time should not be quite long, but will this wait event have bad impact on the scalability of the database? Maybe because of this, no more transaction can be made ?

And because of the poor business logic, everyuser who do the transaction must commit, so maybe it is hard to reduce the frequency of commit.

My another wonder is that there is only 2Mbytes/second written to the disk array, but there is 10% cpu spent on wait(from top, iostat and sar). How to explain this? Will move to raw help?

Thanks very much.:)

--
an oracle fan, 
an oracle beginner

Posted via dBforums
http://dbforums.com
Received on Sat Jul 13 2002 - 02:15:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US