Home » RDBMS Server » Server Administration » TEMP tablespace estimation (Oracle 10.2.0.4 on Solaris 5.10 [SUN SPARC v240])
TEMP tablespace estimation [message #526646] Wed, 12 October 2011 03:45 Go to next message
suddhasatwa_bhaumik
Messages: 18
Registered: October 2011
Location: India
Junior Member

Hello.
We have a database running with Oracle 10.2.0.4 on Solaris 5.10 [SUN SPARC v240] where the assigned TEMP tablespace size looks to be quite huge ( = 8GB!). There are not much SQL queries being run on the database, but what can I do/find/investigate to be sure of how much TEMP space is 'actually' required for the application to run OK?
Note. I have seen the previous DBA's have marked AUTOEXTEND = ON for the TEMP datafiles as well.

Separate query (but linked to TEMP tablespace):
Even if the TEMP datafiles are created with AUTOEXTEND = ON, sometimes I am seeing 'ORA-01652: unable to extend temp segment by 128'. Can someone please explain why this is the case?

Cheers!
SB.

Report message to a moderator

Re: TEMP tablespace estimation [message #526647 is a reply to message #526646] Wed, 12 October 2011 03:56 Go to previous messageGo to next message
suddhasatwa_bhaumik
Messages: 18
Registered: October 2011
Location: India
Junior Member

Additional notes:

W.Ref:
http://webcache.googleusercontent.com/search?q=cache:nb7hOssfkJUJ:asktom.oracle.com/pls/apex/f%3Fp%3D100:11:0::NO::P11_QUESTION_ID:37 4218170986+estimation+of+TEMP+tablespace&cd=2&hl=en&ct=clnk&gl=in

I can see that use of DBMS_XPLAN can be used on top of an EXPLAIN PLAN to estimate the TEMP space required for the query. This is a bit difficult in my case, since there are many batch jobs and related queries running on the database. Only way to implement this solution is to find the most 'costly' SQL statement somehow (from database statistics) and run it with above.

Please advice.

Report message to a moderator

Re: TEMP tablespace estimation [message #526648 is a reply to message #526646] Wed, 12 October 2011 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 54223
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am seeing 'ORA-01652: unable to extend temp segment by 128'. Can someone please explain why this is the case?

When you rebuild index for instance, temp segment is in the index tablespace not in TEMP one.

If the TEMP tablespace has increased to 8GB this means that at some point in time it needed it. This should be quite sufficient to let it at this size unless you know that at this time a very temporary space consumming one time job was running.

Regards
Michel

Report message to a moderator

Re: TEMP tablespace estimation [message #526654 is a reply to message #526648] Wed, 12 October 2011 04:56 Go to previous messageGo to next message
suddhasatwa_bhaumik
Messages: 18
Registered: October 2011
Location: India
Junior Member

Many thanks Michael for the answer.

Quote:

If the TEMP tablespace has increased to 8GB this means that at some point in time it needed it. This should be quite sufficient to let it at this size unless you know that at this time a very temporary space consumming one time job was running.


I agree with the above justification.
however if I would like to know the max utilisation of TEMP tablespace and the specific query/user which is doing so, what can I do?
also, can you advice if keeping AUTOEXTEND = ON for a non-OTLP database like this one is a good idea?

Requoting my initial question-

Quote:
what can I do/find/investigate to be sure of how much TEMP space is 'actually' required for the application to run OK?


Please advice.
Thanks,
SB.

Report message to a moderator

Re: TEMP tablespace estimation [message #526657 is a reply to message #526654] Wed, 12 October 2011 05:24 Go to previous message
Michel Cadot
Messages: 54223
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if I would like to know the max utilisation of TEMP tablespace and the specific query/user which is doing so, what can I do?

Nothing I am aware to.

Quote:
also, can you advice if keeping AUTOEXTEND = ON for a non-OTLP database like this one is a good idea?

The point is: is it better to let grow the temp tablespace or to have one job aborted?
Only you and your client can answer this. I tend to say that disks are cheap and so let the tablespace grows (and 8GB is not so much, I have some temp tablespace over 50GB).

Quote:
what can I do/find/investigate to be sure of how much TEMP space is 'actually' required for the application to run OK?

Periodically snap v$sort_usage join with v$sql on sql_id to get the sql text.

Regards
Michel

Report message to a moderator

Previous Topic: ASM add disk issue
Next Topic: listener could not hand off client connection
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu May 23 21:27:10 CDT 2013

Total time taken to generate the page: 0.27955 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.