 Need Help for Temp Tablespace [message #675185] |
Wed, 13 March 2019 21:44  |
 |
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 #675188 is a reply to message #675185] |
Thu, 14 March 2019 02:27  |
John Watson
Messages: 8960
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 #675191 is a reply to message #675190] |
Thu, 14 March 2019 06:36 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
Akmmhto wrote on Thu, 14 March 2019 02:46Currently 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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
