RE: Simple SQL waiting on 'log file sync'

From: Marquez, Chris <cmarquez_at_collegeboard.org>
Date: Fri, 18 Nov 2005 13:34:39 -0500
Message-ID: <B30C2483766F9342B6AEF108833CC84E05BD60A9@ecogenemld50.Org.Collegeboard.local>

Hmmm...maybe I'm wrong.

When a user session commits, the session's redo information needs to be flushed (FROM MEMORY) to the redo logfile (TO DISK). The session issuing the commit will wait on the log file sync event.

Sounds like you MUST commit to get a "log file sync" (wait) event? So you must be committing!?
And the solution is often as I said tune redo;

• Where possible reduce the commit frequency. Eg: Commit at batch intervals and not per row.
• Speed up redo writing (Eg: Do NOT use RAID 5, use fast disks etc..)
• Tune LGWR to get good throughput to disk . eg: Do not put redo logs on RAID 5.
• If there are lots of short duration transactions see if it is possible to BATCH transactions together so there are fewer distinct COMMIT operations.
• See if any activity can safely be done with NOLOGGING / UNRECOVERABLE options.

hth

Chris Marquez
Oracle DBA

-----Original Message-----
From: oracle-l-bounce_at_freelists.org on behalf of Marquez, Chris Sent: Fri 11/18/2005 1:31 PM
To: sharmakdeep_oracle_at_yahoo.com
Cc: oracle-l_at_freelists.org
Subject: RE: Simple SQL waiting on 'log file sync'  

Deepak,

Ever wonder why a commit normaly on takes a sec or less even when you are committing tons of row chagnes? Because the rows have already changed!...redo, rollback, and commit logic. All of this has already been logged to the redo (and archive) logs just waiting on you to commit or rollback (that will take time, the rollback).

You are waiting on redo log files access/time...redo contention. What until you have a lot of sessions doing this and you start having tons of latch wait contention.

Is your db busy today?...my suggestions is tune your redo log file (disks).

hth

Chris Marquez
Oracle DBA

-----Original Message-----
From: oracle-l-bounce_at_freelists.org on behalf of Deepak Sharma Sent: Fri 11/18/2005 1:17 PM
To: Kevin Closson
Cc: oracle-l_at_freelists.org
Subject: RE: Simple SQL waiting on 'log file sync'  

I am in the process of debugging an application that uses a PL/SQL procedure that ha sa statement similar to this. In my testing, I do have a step to commit, but that is 'after' this insert..select statement. What I am noticing is that if I run a trace, there are 'log file sync' messages.

Step1:
insert.. select statement <<- generated trace has the log file sync message

Step 2:
commit <<- I'm not even there yet

Thanks,
Deepak

• Kevin Closson <kevinc_at_polyserve.com> wrote:

> >>>
> >>>INSERT INTO A1 SELECT * FROM B1;
> >>>
> >>>1) Isn't 'log file sync' related to commits? In
> above I am
> >>>not even commiting.
>
> um, if you are not committing, what is the purpose
> of this
> select ?
>
>
                

---

Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com

--
http://www.freelists.org/webpage/oracle-l






--
http://www.freelists.org/webpage/oracle-l

Received on Fri Nov 18 2005 - 13:41:48 CST