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

From: Shastry(DBA) <>
Date: Sun, 26 Oct 2008 08:59:32 +0530
Message-ID: <>

Thanks Mark,

Let me take your advice :) Nice quoting too!!


On Sat, Oct 25, 2008 at 10:20 PM, Bobak, Mark <>wrote:

> 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.
> -Mark
> ________________________________________
> 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?
> Ann,
> 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.
> HTH,
> --
> 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.
> Thanks,
> Ann
> --

Received on Sat Oct 25 2008 - 22:29:32 CDT

Original text of this message