Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: local write wait event

RE: local write wait event

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Mon, 18 Oct 2004 16:14:07 -0400
Message-ID: <AA29A27627F842409E1D18FB19CDCF275A9B26@AABO-EXCHANGE02.bos.il.pqe>


Perhaps I wasn't clear.

>From what John originally wrote, I got the impression that
he was using a temporary table that was NOT a GTT, and therefore was using truncate to empty the data in the table. A side effect of that truncate was the 'local write wait' event.

My suggestion (though, apparently not very clear ;-)) was to move to a GTT, and then by either committing or disconnecting, depending on the business process, the data will be cleared from the temporary table, and this would preclude needing to do truncate.

Hope that's clear,

-Mark

-----Original Message-----
From: K Gopalakrishnan [mailto:kaygopal_at_gmail.com] Sent: Monday, October 18, 2004 4:06 PM
To: Bobak, Mark
Cc: john.kanagaraj_at_hds.com; oracle-l_at_freelists.org; oradebug_at_freelists.org
Subject: Re: local write wait event

Mark:

Beware, GTT and Truncates do not go together.. Currently we do not differntiate the GTT DDLs and Normal DDLs. Which means the TRUNCATE on the temporary (Session) table locks the entire structure (all-sessions-temp-tables) and all other sessions have to wait for library cache/pin waits.

Recently I was involved in a heavy duty RAC benchmark where we used the = GTT
for data loading/processing and truncate the GTTs after processing. Around 4000 sessions (across various RAC nodes) = connect to
the db and does the CDR (Call Data Record) processing. like this..

1.Load data to temp table
2. do some processing
3 truncate
4. Go to Step 1 ....... in a loop 1000 times with 4000 sessions.

The Truncate locks the entire GTT structure and 3999 sessions were = waiting
for lib cache. I think I had filed a bug and it is not backported (till) to the 9.2 versions yet. So I would be little careful with Truncates on GTTs.

I have just closed and re-opened the connection to workaround that issue = and
we were able to process around 32k CDRs/second on Hp-UX ..9204 RAC.

On Mon, 18 Oct 2004 15:54:18 -0400, Bobak, Mark <mark.bobak_at_il.proquest.com> wrote:
> John,
>=20
> You don't mention a version, but it sounds to me like you'd want
> to use global temporary tables and avoid the TRUNCATE all together.
>=20
> -Mark
>=20
>=20
>=20
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of John Kanagaraj
> Sent: Monday, October 18, 2004 3:42 PM
> To: oracle-l_at_freelists.org
> Subject: RE: local write wait event
>=20
> KG/All,
>=20
> >see this during (rarely) Truncating a large table while most of the
> >buffers of that table in cache. During TRUNCATEs the session has to a
> >local checkpoint and during this process, the session may wait for
> >'local write' wait.
>=20
> We are seeing a number of these waits on a largish Oracle Applications
> database that has frequent TRUNCATEs some 'temporary' tables that are =
=3D
> used
> for Inventory's ATP (Available-To-Promise) processes. Makes sense!
>=20
> John Kanagaraj <><
> DB Soft Inc
> Phone: 408-970-7002 (W)

--=20

Best Regards,
K Gopalakrishnan=20
Co-Author: Oracle Wait Interface, Oracle Press 2004 http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Oct 18 2004 - 15:09:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US