Oracle-L: RE: Deleting from Global temporary Tables

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Thu, 16 Oct 2003 08:24:32 -0800
Message-ID: <F001.005D362D.20031016082432@fatcity.com>

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

ineyman_at_perceptron.com

-----Original Message-----
Jamadagni, Rajendra
Sent: Wednesday, October 15, 2003 7:45 AM To: Multiple recipients of list ORACLE-L

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 Sort options: prsela fchela exeela

count    = number of times OCI procedure was executed 
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing 
disk     = number of physical reads of buffers from disk 
query    = number of buffers gotten for consistent read 
current  = number of buffers gotten in current mode (usually for update)

rows     = number of rows processed by the fetch or execute call 


************************************************************************

DELETE FROM TEMP_SCHEDULE
WHERE session_id = USERENV('sessionid')

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 1 0.01 0.00 0 0 0 0
Execute 52 111.19 128.72 94 10498 12989188 35211
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------

total 53 111.20 128.73 94 10498 12989188 35211

Misses in library cache during parse: 1 Optimizer goal: CHOOSE
Parsing user id: 47 (recursive depth: 1)

Rows Row Source Operation

-------  --------------------------------------------------- 
      0  DELETE  (cr=10498 r=94 w=0 time=128723041 us)

35211 TABLE ACCESS BY INDEX ROWID TEMP_SCHEDULE (cr=9329 r=56 w=0 time=5507639 us)
35211 INDEX RANGE SCAN TEMP_SCHED_INDX (cr=596 r=8 w=0 time=120949 us)(object id 228499)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total

Waited

Waited ----------
global cache open x 229 0.00 0.05 latch free 88 0.65 7.41 buffer busy waits 28 3.00 12.73 log file switch completion 2 0.00 0.01 undo segment extension 2543495 0.00 1.59 enqueue 24 0.00 0.00 global cache s to x 8 0.00 0.00 KJC: Wait for msg sends to complete 30 0.00 0.00 db file sequential read 94 0.02 0.43

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
Raj

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.

**************5

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Oct 16 2003 - 11:24:32 CDT