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,
>
> 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.
>
> -Mark
>
>
>
> -----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
>
> KG/All,
>
> >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.
>
> We are seeing a number of these waits on a largish Oracle Applications
> database that has frequent TRUNCATEs some 'temporary' tables that are =
> used
> for Inventory's ATP (Available-To-Promise) processes. Makes sense!
>
> John Kanagaraj <><
> DB Soft Inc
> Phone: 408-970-7002 (W)

-- 

Best Regards,
K Gopalakrishnan 
Co-Author: Oracle Wait Interface, Oracle Press 2004
http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/
--
http://www.freelists.org/webpage/oracle-l