RE: How to size, calculate the temporary tablespace based on database total size OR database growth?

From: Bobak, Mark <>
Date: Sat, 25 Oct 2008 12:50:02 -0400
Message-ID: <>

Stephane has offered some sage advice here. "Don't fix symptoms, fix root causes." I like that. It reminds me of a Tom Kyte quote, "Tune the question, not the query."

Seriously though, temp tablespace size isn't necessarily about total database size or growth rates, it's more about the nature of the queries being written.


From: [] On Behalf Of Stephane Faroult [] Sent: Saturday, October 25, 2008 10:18 AM To:
Cc: oracle-l; Subject: Re: How to size, calculate the temporary tablespace based on database total size OR database growth?


   Let me tell you a story: some years ago the situation was rather hot in a big European bank where I was consulting because a report that they were supposed to run daily had been failing every night for a week for lack of temporary space. The DBA had added more space several times, it failed each time, only later in the night. Temporary storage had reached absolutely ridiculous levels - the temporary tablespace alone was bigger than many a database. They were supposed to send the report at the SEC in New-York and they were afraid they might lose their banking license in the US, so there was much at stake. You know how it was fixed? Not by adding still more temporary space, but by rewriting the query in a sounder way. There was a hash join of death somewhere where it should at been a nested loop or something of that kind. Rethinking the query (no, no hints) made it incredibly faster - without any need for much temporary space.

You also mention a high open cursor count. All of this points to a poorly written application, and whatever you can do can only, at best, postpone the day of reckoning. Don't fix symptoms, fix root causes. You can "need" far more temporary storage than you have data if you write your application badly enough.


Stephane Faroult
RoughSea Ltd<>
Coming speaking engagements:
Beijing, Shanghai<>, Hong-Kong<> and Singapore<>.

Shastry(DBA) wrote:
Hello Gurus,

How to size, calculate the temporary tablespace based on database total size OR database growth?, One of our application support raised a tar on high open cursor count and they inturn asked the DBA's to size appropriately the TEMP tablespace. Could anyone please advice on this? I am mainly looking out scenarios to handle the TEMP issues and one of the reason is high open cursor count which is causing the performance issue.

Also we have a dataware house database where they run data to load every 2 hours. Even their process got failed because of TEMP space unable to extend, and had to restart the job post temp space addition. I have least idea on how to size the TEMP space required for a particular activity. Please let me know any links to refer as well( Metalink notes). Thanks in anticipation.


-- Received on Sat Oct 25 2008 - 11:50:02 CDT

Original text of this message