RE: local write wait event
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: "K Gopalakrishnan" <kaygopal@xxxxxxxxx>
- Date: Mon, 18 Oct 2004 16:14:07 -0400
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@xxxxxxxxx]
Sent: Monday, October 18, 2004 4:06 PM
To: Bobak, Mark
Cc: john.kanagaraj@xxxxxxx; oracle-l@xxxxxxxxxxxxx;
oradebug@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of John Kanagaraj
> Sent: Monday, October 18, 2004 3:42 PM
> To: oracle-l@xxxxxxxxxxxxx
> 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/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
