GTT performance degradation

From: Teehan, Mark <>
Date: Fri, 19 Mar 2010 09:20:20 +0800
Message-ID: <>

Hi -  

I am trying to find out more about space allocation for global temporary tables - I have a case where a GTT has several million inserts and deletes per day - the table has two indexes and is configured to on commit delete rows. Truncate is not used because rows are deleted as they are processed. After stable performance for almost 100 days, it has suddenly degraded, and the only common denominator that I can see is that any session inserting or merging to the temp table is performing at 2 to 3 times the former elapsed time. Everything else (AFAIK) remains the same ( Does oracle attempt to reuse any the same extents in the temp tablespace for the GTT and its indexes? Would this necessarily be a bad thing?
I found one post on of a similar issue; and after dropping/creating the GTT the performance normalized again - but I cannot find anything to support this action in MOS. Has anyone else come across this?

Mark Teehan

 Please access the attached hyperlink for an important electronic communications disclaimer:
Received on Thu Mar 18 2010 - 20:20:20 CDT

Original text of this message