Estimate temp space before running sql?

[Axr2]

What's a good way to go about estimating the amount the temp space required by a sql (with a bunch of DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS etc) before running it?

[tamilselvan]

In 9i, it is simple.

• You can now estimate Sort Area Size (in temp tablespace) needed by a Query that has group by (or order by ) clause.

delete from plan_table ;

explain plan for
select row_id, created_by, conflict_id, x_profile_ind, x_segment, count(*)
from s_org_ext
group by row_id, created_by , conflict_id, x_profile_ind, x_segment ;

select * from table( dbms_xplan.display );

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11M| 222M| | 156K|
| 1 | SORT GROUP BY | | 11M| 222M| 766M| 156K|
| 2 | TABLE ACCESS FULL | S_ORG_EXT | 11M| 222M| | 108K|
----------------------------------------------------------------------------------------------------

From the plan, I come to know that 766M temp space is required.

Tamil

[Axr2]

Thanks Tamilselvan. I'd also be interested in solutions for 8i, 7.3.4 (if available..that is). Just wondering how to go about making an 'educated guess' actually.

[tamilselvan]

Practically, I do not estimate temp space requirement before I execute a statement. I usually configure 20 GB disk space for the temp tablespace, no matter how much is used by all sessions.

Still you use 7.3.4 !!!!
I have Oracle experience from Rel 5 to 9i.
To me Release 7.3.4 is the best Oracle Release I ever worked.

To answer your question, you can't do it in 8i, or 7.3.4. Period.

Tamil

[Axr2]

Thanks for the response. We are an ASP/hosters and host databases for a bunch of corporations. Sadly, some are still on 7.3.4, 8i.

Practically, I do not estimate temp space requirement before I execute a statement. I usually configure 20 GB disk space for the temp tablespace, no matter how much is used by all sessions.

How about 'while' executing the transaction (in either 8i or 7.3.4)? I have a 7.3.4 customer trying to implement some new reporting tool..and is trying to execute a statement (that runs a few pages) across some HUGE tables. The guy ends up blowing the temp space time and again, as expected. I am reluctant to allocate any more space to TEMP, coz in all probability he'll blow it, no matter what.

I'm having to make do with very limited space on this machine. I'd like to make an educated guess on the amount of space the transaction will require, before asking him to cough up more $$ to buy additional disk, or simply not run the transaction.

[tamilselvan]

========
How about 'while' executing the transaction (in either 8i or 7.3.4)? I have a 7.3.4 customer trying to implement some new reporting tool..and is trying to execute a statement (that runs a few pages) across some HUGE tables. The guy ends up blowing the temp space time and again, as expected. I am reluctant to allocate any more space to TEMP, coz in all probability he'll blow it, no matter what
=======

You can't do anything. Let the application fail.

This happened frequently in every project I handled. Basically the SQL needs to be tuned or the amount of rows returned to the client should be restricted. If the app team is not changing the code, you don't have any option.

Or you can give some quota on temp space to the particular user. This will stop db crash.

Tamil