Log File Sync

[anandkl]

Hi All,
We are using oracle 8.1.7.4. Sun 2.8.

Since two weeks for every 2 1/2 min log file sync happens and IO WAIT increase to 99%. I tried to check for all possible insert,update,delete and also there were some update happening through a trigger, we dissabled the trigger. There was one datafile in the file system used by redo log files, i moved this datafile to another file system.

I also disabled logging option for some of the important indexes hoping that this will stop it ,but this did not help.
Also the move of datafile to another file system also did not help.

Can you please suggest any good option to find the culprit causing Log file sync.

I checked the stats pack, its showing more of Select rather than insert,updata or delete.

regards
anandkl

[davey23uk]

well log file sync is a wait when writing to redo logs.

That happens either of:

every 3 second
every 1mb
1/3rd log buffer full
a commit

so either you are commiting a lot more than you used to, or you have a slow disk problem

[anandkl]

Hi Dave,
We never had this issue earlier, it all happened two weeks back after we did a release.

From stats pack, i got all the DML and give to engineers. We have disable quite a couple of them.

Inspite of this, it still continues to happen.
Is there a way i can find what is causing this.

regards
anandkl

[slimdave]

log_file_sync occurs specifically when a commit is issued. If you encounter too many of them then it is likely that you are commiting too frequently (often in an attempt to "save resources", but in fact here is a prime example of how it slows things down).

See this: http://download-west.oracle.com/docs...htm#sthref1051

[davey23uk]

saw the problem afte rinstalling a new release - sounds fishy to me. Too many commits would be the problem.

Try comparing the transactions in a statspack report from before and after

[anandkl]

Hi Friends,
I did compare the stats before and after the release. And presented the stats to the engineers. We fixed some of the things that were part of the recent release, but still its coutinuing to happen(LOG FILE SYNC).

what are the other possible ways to identifiy which SQL is causing this.

regards
anandkl

[davey23uk]

its not sql which is the problem here, it is committing too much

[anandkl]

Hi Dave,
I mean to say how to know which DML are doing excessive commit.

regards
anandkl

[davey23uk]

that doesnt make sense.

sql statements dont do commits. It is your application that does it

[slimdave]

There are statistics named "user commits" and "user rollbacks" in v$statname. Use something like ...

Select * from
(
select name,sid,value from v$statname n,
v$sesstat s
where name = 'user commits'
and s.statistic# = n.statistic#
order by 3 desc
)
where rownum < 21