Home » RDBMS Server » Server Administration » Need Help for Temp Tablespace (Unix)
icon5.gif  Need Help for Temp Tablespace [message #675185] Wed, 13 March 2019 21:44 Go to next message
Akmmhto
Messages: 38
Registered: September 2018
Member
Dear Team,

I Need some information regarding my below point , Pls contribute your valuable inputs:

a> Do small Temp datafile affects Performance of any query?
b> How to find that my query is generating excess temp?
c> Is there any way to tune any query to generate less Temp?
d> Ideal Temp Tablespace size for a 200GB Database.
e> Sudden increase in temp files , Should we need to resize them or let them be as in their current condition ?

All Above Points i raised because my developers will surely ask me that what he should/can do with his query for less temp usage etc.

Advance Thanks,
Regards
Anup

[Updated on: Wed, 13 March 2019 21:45]

Report message to a moderator

Re: Need Help for Temp Tablespace [message #675186 is a reply to message #675185] Wed, 13 March 2019 22:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Akmmhto wrote on Wed, 13 March 2019 19:44
Dear Team,

I Need some information regarding my below point , Pls contribute your valuable inputs:

a> Do small Temp datafile affects Performance of any query?
b> How to find that my query is generating excess temp?
c> Is there any way to tune any query to generate less Temp?
d> Ideal Temp Tablespace size for a 200GB Database.
e> Sudden increase in temp files , Should we need to resize them or let them be as in their current condition ?

All Above Points i raised because my developers will surely ask me that what he should/can do with his query for less temp usage etc.

Advance Thanks,
Regards
Anup
a) no
b) quantify excess temp
c) depend upon query. ORDER BY may require temp
d) it depends. there is NO direct relationship between DB size & TEMP size
e) it depends upon source of increase in size.

More than likely your obsession on TEMP is misguided & misplaced.

Post SQL & results that show a problem that needs to be solved.
Re: Need Help for Temp Tablespace [message #675188 is a reply to message #675185] Thu, 14 March 2019 02:27 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
wrt to (c), some operations require more PGA (and therefore possibly more temp space) than others. For example, if you use nested loop joins rather than hash joins temp space usage may drop. Of course, performance may drop too.
Re: Need Help for Temp Tablespace [message #675190 is a reply to message #675188] Thu, 14 March 2019 02:46 Go to previous messageGo to next message
Akmmhto
Messages: 38
Registered: September 2018
Member
Currently as a work around solution I have resized some temp files...
I will post the culprit queries when I will get the issue again.

Thanks for the kind reply
Re: Need Help for Temp Tablespace [message #675191 is a reply to message #675190] Thu, 14 March 2019 06:36 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Akmmhto wrote on Thu, 14 March 2019 02:46
Currently as a work around solution I have resized some temp files...
I will post the culprit queries when I will get the issue again.

Thanks for the kind reply
As a work around for what, exactly?

So far you have not identified a problem, nor have any of your bullet-point questions been directed at any potential problem.
In general, the temp ts needs to be big enough to handle anything thrown at it, short of a large Catesian join. So what's the problem with having it "oversized"? Your entire 200gb database is tiny, and disk is cheap. You can buy a 1 TB drive for less than $60 US. Your director spends more than that on a single business lunch, and you can keep 3 of your databases on it with room to spare.
Previous Topic: How To Get the username that is currently Proxy To
Next Topic: Corrupted index causing data-integrity issues
Goto Forum:
  


Current Time: Thu Mar 28 17:53:49 CDT 2024