Is the temp tablespace really too small
- From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 17 Oct 2012 11:38:22 -0500
Here is one I ran into some time ago. There is only on indexed column,
myid, on the table.
For your amusement, note them temp spc usage:
DELETE FROM myname WHERE myID NOT IN (SELECT DISTINCT myID FROM myname
WHERE runTIME > :1)
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc|
Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | |
8812K(100)| |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
| 1 | DELETE | MYNAME | | | |
| |
| 2 | HASH JOIN RIGHT ANTI| | 556M| 18G| 2160M| 8812K
(1)| 29:22:29 |
| 3 | TABLE ACCESS FULL | MYNAME | 66M| 1398M| | 3537K
(1)| 11:47:33 |
| 4 | TABLE ACCESS FULL | MYNAME | 1332M| 17G| | 3517K
(1)| 11:43:36 |
They were running this many times per hour and complaining I needed to add
space to the temp tablespace.
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
--
//www.freelists.org/webpage/oracle-l
Other related posts:
