Home » RDBMS Server » Performance Tuning » Temporary tablespace growing very fast
Temporary tablespace growing very fast [message #65908] Fri, 28 January 2005 05:52 Go to next message
Vivek Vijai
Messages: 67
Registered: April 2004
Member
In Our Oracle 9i (9204) database, Temporary tablespace is growing very...the application which is using the database is meant for reports
So there is harldy any dml..i know lots of sort operation are required but in last couple of days temporary tablespace is growing like anything
Eg today morning I created a temp tablespace og 1GB and within 1 hr it get filled up.
And is their any way of reducing the size of Temp datafile belonging to non default temporary tablespace

Please let me know if there is any soln for it

Thanks in advance
Vivek
Re: Temporary tablespace growing very fast [message #65909 is a reply to message #65908] Fri, 28 January 2005 06:25 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
It is perfectly normal.
It is the way it should behave.
You can look into sorting behaiour using V$sort_usage.
Re: Temporary tablespace growing very fast [message #65910 is a reply to message #65909] Sat, 29 January 2005 02:21 Go to previous messageGo to next message
Vivek Vijai
Messages: 67
Registered: April 2004
Member
Will it help if i increase the Sort_area_size rite now its 0.5 MB
Re: Temporary tablespace growing very fast [message #65912 is a reply to message #65910] Mon, 31 January 2005 06:08 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
A certain sql statement will require some internal sort.
These operations will done in memory defined by SORT_AREA_SIZE. When more room is needed, the operation is pushed to disk ( into the temp tablespaces) where it is been taken care of.
So, increaseing SORT_AREA_SIZE might help. But a very big value for the parameter may create some problem.
So try it (Increasing sort_area_size). If it helps keep.
I wouldnt bother about the temp. tablespace usage at all even if it shows 99.99% used.
Re: Temporary tablespace growing very fast [message #65920 is a reply to message #65912] Mon, 31 January 2005 22:50 Go to previous message
Vivek Vijai
Messages: 67
Registered: April 2004
Member
Thanks a lot Mahesh
Previous Topic: Buffer Cache and Shared Pool
Next Topic: oracle10g
Goto Forum:
  


Current Time: Sun Jan 17 13:36:21 CST 2021