RE: Simple SQL waiting on 'log file sync'

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

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:34:46 CST