RE: How to size, calculate the temporary tablespace based on database total size OR database growth?
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "sfaroult@xxxxxxxxxxxx" <sfaroult@xxxxxxxxxxxx>, "shastry17@xxxxxxxxx" <shastry17@xxxxxxxxx>
- Date: Sat, 25 Oct 2008 12:50:02 -0400
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
