Hi Tom.
I have a question regarding log file sync waits. From the stats pack reports, it is always in the Top 5 Timed events.
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 2,446 75.27
log file sync 12,883 212 6.52
latch free 17,869 182 5.60
log file parallel write 35,538 174 5.34
SQL*Net break/reset to client 5,544 80 2.46
-------------------------------------------------------------
Wait Events for DB: PROD01 Instance: PROD01 Snaps: 222 -223
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file sync 12,883 59 212 16 0.5In a previous question (
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:16470446503580 ) you stated that there might not be a performance benefit from tuning these waits.
I submitted my stats report to Anjo Kolk's website, OraPerf.com . The analysis said to reduce the number of process in the init.ora to reduce some of the waits. "
Another option is to decrease the init.ora parameter "processes". The LGWR needs to scan all processes to find each process that is waiting for the commit to be written. The current value for processes is 2400. Try setting it close to the number of process that you really need." I have the processes set at 2400 but we usually max out at around 900.
My question is why would reducing the number of process in the init.ora reduce the log file snyc waits? Are 2400 processes created at db startup? Are there really more processes than users logged in?
We are running 9.2.0.7 with the E-Business Suite.
Thanks for your insight,
Sharon
well, the average wait is 0.016 seconds - one or two one hundredths of a second, it would see that is the time to wait for the IO to complete much more than anything else.
I just did a quick and dirty example with processes = 100 / 2000 (2000 is the max on my little test machine)
Did 100,000 insert/commits.
connect / as sysdba
alter system set processes=100 scope=spfile;
startup force;
connect /
drop table t;
create table t ( x int );
alter session set events '10046 trace name context forever, level 12';
begin
for i in 1 .. 100000
loop
insert into t values (i);
commit work write immediate wait;
end loop;
end;
/
disconnect
!tkprof `ls -t $ORACLE_HOME/admin/$ORACLE_SID/udump/*ora_*.trc | head -1` ./100.prf
connect / as sysdba
alter system set processes=2000 scope=spfile;
startup force;
connect /
drop table t;
create table t ( x int );
alter session set events '10046 trace name context forever, level 12';
begin
for i in 1 .. 100000
loop
insert into t values (i);
commit work write immediate wait;
end loop;
end;
/
disconnect
!tkprof `ls -t $ORACLE_HOME/admin/$ORACLE_SID/udump/*ora_*.trc | head -1` ./2000.prf
!grep 'log file sync' 100.prf 2000.prf
first time I ran it:
100.prf: log file sync 99934 0.05 32.77
100.prf: log file sync 99934 0.05 32.77
2000.prf: log file sync 99933 0.04 36.74
2000.prf: log file sync 99933 0.04 36.74
so, a small difference, but that was only one run (statistically not too meaningful)
100.prf: log file sync 99952 0.06 35.00
100.prf: log file sync 99952 0.06 35.00
2000.prf: log file sync 99928 0.37 36.85
2000.prf: log file sync 99928 0.37 36.85
second time - I'm skeptical that it would give you any huge boost, anything "meaningful"
the best way to reduce log file sync waits are:
a) ensure lgwr has free and clear access to the devices the logs are on (make IO faster)
b) commit less frequently (you wait for a log file sync when you commit)