Re: Is the temp tablespace really too small
- From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 17 Oct 2012 13:06:11 -0500
The irritating part is the rewrite to use no temp is so simple, I blame a
code generator.
delete from myname where runtime<=:1;
On Wed, Oct 17, 2012 at 11:38 AM, Andrew Kerber <andrew.kerber@xxxxxxxxx>wrote:
> 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.'
>
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
--
//www.freelists.org/webpage/oracle-l
Other related posts:
