GTT performance degradation
Date: Fri, 19 Mar 2010 09:20:20 +0800
Message-ID: <82A20AE725E34B43B3F6C9DF5DBF1E3409D0A656_at_ESNG17P32003A.csfb.cs-group.com>
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 (10.2.0.4/linux). 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 forums.oracle.com 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?
Thanks!
Mark Teehan
Singapore
Please access the attached hyperlink for an important electronic communications disclaimer: http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 18 2010 - 20:20:20 CDT