Is the temp tablespace really too small

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Wed, 17 Oct 2012 11:38:22 -0500
Message-ID: <CAJvnOJY_JV9PJN0iNb2K8813DzyA9eSD+os5GDv+B3=0JopbLQ_at_mail.gmail.com>

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


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

Received on Wed Oct 17 2012 - 18:38:22 CEST