How To Estimate TEMP tablespace Requirement

<big><font face="sans-serif">Last week I was having problem creating a materialized view because of TEMP
tablespace getting full. I have increased the size of the TEMP to 20G but
still the script failed after the TEMP space was maxed out.

If I had know of this technique prior to that problem, I would have realized
that 28G was the size I needed.. too bad i scrapped the MV creation, but that was
more of server disk space issue than not knowing :) But have i known that I'm 8GB
short of the required, im sure i would have pushed for it by using the few gigs on
the other partitions.</font></big>

<font face="sans-serif">

I have a 4.2 Billion row table with 8 columns
I need to group by 5 columns.
This table is 380 Gigs.

how much space in temp tablespace will I need.
Will it be close to 380 X 2 = 760 Gigs.
I need to use parallel query ( degree 8 or 16 probably )

</font><br /><br /><hr class="jump" /><br /><br /><br />

and Tom  said…

<font face="sans-serif">in 9iR2 as you have, this is pretty straightforward.<br /><br /></font>big_table@ORA920> delete from plan_table;<br /> <br />4 rows deleted.<br /> <br />big_table@ORA920><br />big_table@ORA920> explain plan for<br />  2  select object_name, owner, object_id, created, last_ddl_time, count(*)<br />  3    from big_table<br />  4   group by object_name, owner, object_id, created, last_ddl_time;<br /> <br />Explained.<br /> <br />big_table@ORA920><br />big_table@ORA920> select * from table( dbms_xplan.display );<br /> <br />PLAN_TABLE_OUTPUT<br />-------------------<br /> <br />------------------------------------------------------------------------------<br />|Id| Operation            |  Name       | Rows  | Bytes |TempSpc| Cost (%CPU)|<br />------------------------------------------------------------------------------<br />|0 | SELECT STATEMENT     |             |  1833K|    85M|       | 21993   (6)|<br />|1 |  SORT GROUP BY       |             |  1833K|    85M|   225M| 21993   (6)|<br />|2 |   TABLE ACCESS FULL  | BIG_TABLE   |  1833K|    85M|       |  2417  (12)|<br />------------------------------------------------------------------------------<br /> <br />8 rows selected.<br /><br /><font face="sans-serif"><br />the plan as shown by dbms_xplan includes this.<br /><br /><br />Bear in mind, it is not a function so much of the number of rows in the source table -- <br />more the number of distinct values of the group by columns!</font><br />


About this entry