Re: How to size, calculate the temporary tablespace based on database total size OR database growth?
- From: "Shastry(DBA)" <shastry17@xxxxxxxxx>
- To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- Date: Sun, 26 Oct 2008 08:59:32 +0530
Thanks Mark,
Let me take your advice :) Nice quoting too!!
Thanks,
Anantha
On Sat, Oct 25, 2008 at 10:20 PM, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx>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: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of Stephane Faroult [sfaroult@xxxxxxxxxxxx]
> Sent: Saturday, October 25, 2008 10:18 AM
> To: shastry17@xxxxxxxxx
> Cc: oracle-l; ora-apps-dba@xxxxxxxxxxxxx
> 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<http://www.roughsea.com>
> Coming speaking engagements:
> Beijing, Shanghai<
> http://www.oracle.com/global/cn/education/promotions/stephanefaroult_cele_seminar_cn.htm>,
> Hong-Kong<
> http://www.oracle.com/education/images/apac_newsletter/seminar/hk_stephanefaroult.html>
> and Singapore<
> http://www.oracle.com/education/images/apac_newsletter/seminar/sg_stephanefaroult.html
> >.
>
> 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
>
> -- //www.freelists.org/webpage/oracle-l
>
>
Other related posts:
