Skip to Main Content

Breadcrumb

Warning

Before you submit your comment...

Remember - this is about feedback to the original question. AskTOM is about building a strong Oracle community, not stealing time away from others needing assistance by sneaking in a brand new question under the guise of "Comment".

If your comment requires a response that might include a query, please supply very simple CREATE TABLE and INSERT statements. Nice simple scripts so we can replicate your issue simply.

Remember to take a look at Legal Notices and Terms of Use before supplying a comment...

Don't forget, your comments are public. If you want to send a comment to just the AskTOM team, please use the feedback option

Comment

Highlight any SQL, PL/SQL, or fixed-width text and click the <code> button
 (will never be published or used to spam you)

Question and Answer

Tom Kyte

Thanks for the question, Sharon.

Asked: July 18, 2007 - 1:11 pm UTC

Last updated: July 13, 2011 - 1:54 pm UTC

Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

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.5
In 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

and we said...

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)

Rating

  (36 ratings)

Is this answer out of date? If it is, please let us know via a Comment