RE: Simple SQL waiting on 'log file sync'
- From: "Marquez, Chris" <cmarquez@xxxxxxxxxxxxxxxx>
- To: "Marquez, Chris" <cmarquez@xxxxxxxxxxxxxxxx>, <sharmakdeep_oracle@xxxxxxxxx>
- Date: Fri, 18 Nov 2005 13:34:39 -0500
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@xxxxxxxxxxxxx on behalf of Marquez, Chris
Sent: Fri 11/18/2005 1:31 PM
To: sharmakdeep_oracle@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx on behalf of Deepak Sharma
Sent: Fri 11/18/2005 1:17 PM
To: Kevin Closson
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
