OraFAQ Forum: Performance Tuning » Temporary tablespace growing very fast

Home » RDBMS Server » Performance Tuning » Temporary tablespace growing very fast

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Temporary tablespace growing very fast [message #65908]

Fri, 28 January 2005 05:52

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

Report message to a moderator

Re: Temporary tablespace growing very fast [message #65909 is a reply to message #65908]

Fri, 28 January 2005 06:25

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

Re: Temporary tablespace growing very fast [message #65910 is a reply to message #65909]

Sat, 29 January 2005 02:21

Vivek Vijai
Messages: 67
Registered: April 2004

Member

Will it help if i increase the Sort_area_size rite now its 0.5 MB

Report message to a moderator

Re: Temporary tablespace growing very fast [message #65912 is a reply to message #65910]

Mon, 31 January 2005 06:08

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

Re: Temporary tablespace growing very fast [message #65920 is a reply to message #65912]

Mon, 31 January 2005 22:50

Vivek Vijai
Messages: 67
Registered: April 2004

Member

Thanks a lot Mahesh

Report message to a moderator

Previous Topic:	Buffer Cache and Shared Pool
Next Topic:	oracle10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 10:31:19 CDT 2024