Execution Plan & Estimate Temporary Tablespace Usages

One of our developer was informing that he need extra space on temporary tablespace as one of his adhoc query is running for 2 hours (and may run for 10 hours) and consuming huge amount of temp space.

Database was Oracle 11gR2 on RHEL5.
Now problem is how can I estimate that how much space should I add ? I tried with execution plan
as below:

sqlplus user/pass@DB

sql> delete from myuser.plan_table;

sql> commit;

sql> explain plan for
select * from .....


sql> select * from myuser.plan_table;


But I could not find any field that said about Temp Usage.
Then searching the web I became know that Older version of plan table(I found default) don't have that field. It should be created manually and than explain the a query:

cd /ORAIN/oracle/product/11.2.0/dbhome_1/rdbms/admin/


sqlplus user/pass@DB

sql> drop myuser.plan_table purge;

sql> @utlxplan.sql

Table created.

sql> delete from myuser.plan_table;

sql> commit;

sql> explain plan for
select * from .....


sql> select * from myuser.plan_table;

Here TEMP_SPACE column will show the estimated space usage. Then I added the extra Temp space according to the value [added more than estimated for safety purpose] showed by column TEMP_SPACE and it worked.


Recent optimizer statistics should be gathered for Tables and Indexes used the query for which plan is being generated in order to get more accurate result.

http://oradbatips.blogspot.com/2008/02/tip-69-estimate-temp-usage-without.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:12519780526076