truncate of global temporary tables and UNDO
656121Jan 4 2009 — edited Jan 11 2009Hi,
My application involves using a lot of global temporary tables while running the application.Most of them are DML operations using insert/delete statements. Due to this activity, the performance is degrading and also my undo tablespace is getting filled up very quickly . We thought of implementing the "truncate" instead of "delete",since unconditional deletes are used. Before implementing the "truncate" in the code, we need the advice from you all in regarding the following.
Ours is 10gR2 using RAC.
1) Do we get any other issues if we use truncate instead of delete for global temporary tables?
2) Does the concept of "two phase commit" have any effect on this?
3) My undo tablespace is almost 99% full most of the time. Since the undo tablespace frees up space as and when required depending on the undo_retention parameter, I guess it is of no problem. But, still do we face any performance problem, since the free space needs to be created in the UNDO tbs by deleting the expired undo, for generating the new UNDO
4) Is there any way of clearing the UNDO tablespace manually?
All the replies are welcome and thanks in advance.
Regards
DRK