Raj,
Global temporary tables are “session-specific”. They have “global” definition, but each session deals with it’s own “incarnation” of the table.
So, the question is: why would you “DELETE FROM TEMP_SCHEDULE WHERE session_id = USERENV('sessionid')”? Data in temp table will be deleted anyway automatically at the end of transaction or session (your choice). Or, I don’t understand something here?
Now, trace shows some physical reads. What is your “sort_area_size” compared to the “average” size of the temp table? Also, just recently it was a discussion about temp tables “over-using” RedoLog (specifically in 9.2 version). Trace shows waits on “undo extension”, m.b. there is also problem/bug with temp tables using undo tablespace?
Igor Neyman, OCP DBA [EMAIL PROTECTED]
-----Original Message-----
Any specific reason what this should be costly ?? Look below for a snippet from a tkprof analysis ... db is 9202, 2 node RAC, everything is LMT and TEMP TS is 16GB Trace file:
abc1_ora_9879592_crenshaj_1014.trc DELETE FROM TEMP_SCHEDULE
call
count cpu
elapsed disk
query current rows
Misses in library cache
during parse: 1 Rows
Row Source Operation
Elapsed times include waiting
on following events: Needless to say this is a heavily used table, heavy inserts and deletes, no updates. Any tricks to insert/delete large number of rows from GTTs? All inserts are bulk inserts, but not delets. TIA
|
Title: Deleting from Global temporary Tables
- Deleting from Global temporary Tables Jamadagni, Rajendra
- Igor Neyman