Oracle-L: Deleting from Global temporary Tables

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Wed, 15 Oct 2003 04:44:34 -0800
Message-ID: <F001.005D33AF.20031015044434@fatcity.com>

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: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.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 Wed Oct 15 2003 - 07:44:34 CDT