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
