Re: Is the temp tablespace really too small

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Wed, 17 Oct 2012 13:06:11 -0500
Message-ID: <CAJvnOJYPqqmMqN-fAxBBTMutN9HuY0qWRT_O+Ocke8TwZNkphQ_at_mail.gmail.com>

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_at_gmail.com>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.'


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 17 2012 - 20:06:11 CEST