Thanks Christopher. Useful information.
-----Original Message-----
Sent: Tuesday, August 28, 2001 7:26 PM
To: Multiple recipients of list ORACLE-L
When it is perm, you need to recovery it like a normal tablespace if moving
it or recovering, unless you offline it or rebuild the control file, with a
temporary tablespace it is just not "part of the database". It is a bit
simplier, although you can get around it. Although both work perfectly
fine, it is syntaxtic sugar as I would say to just set it as temporary
(tempfile) and not have to worry about it period.
As for putting objects into it, agreed, you can avoid it, but with a
temporary tablespace you do not need to worry about it at all. This is a
minor problem, but is something to note.
As for ST enqueue, perhaps you do not have problems with the ST enqueue, but
there are many cases where perm tablespace for sorting causes ST enqueue
contention, most notably when doing parallel segment creation. It is well
documented that perm temporary tablespaces cause more contention on the ST
enqueue. When you have a perm temporary tablespace, for every extent
created you need to hold the ST enqueue exclusively. This WILL cause ST
enqueue contention, it is very possible you are not suffering from large
waits due to this.
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-----Original Message-----
Sent: Tuesday, August 28, 2001 9:17 AM
To: Christopher Spence; Multiple recipients of list ORACLE-L
Chris,
I've always used a permanent tablespace for temp and have not had the
problems you state. But it does take some consistency to maintain.
1- we never allow anyone a quota on temp or even allow anyone to have that
as their default. Come to think of it only sys has system as it's default
tablespace. We also do not allow 'unlimited tablespace' even to purchased
apps.
2- The ST enqueue has not been a problem, actually have 0 contention there
even in the data warehouse.
3- we don't back it up. Have had one get trashed, along with the disk
drive.
Did an offline drop and rebuilt it elsewhere.
Dick Goulet
____________________Reply Separator____________________
Author: Christopher Spence <[EMAIL PROTECTED]>
Date: 8/28/2001 5:50 AM
No, it is generally better practice to use temporary tablespace for the
following reasons.
1. You will not accidentently store objects in it.
2. Reduced contention for the ST enqueue
3. Reduce backup and recovery time
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-----Original Message-----
Sent: Tuesday, August 28, 2001 8:11 AM
To: Multiple recipients of list ORACLE-L
Oracle recommends creation of separate temporary tablespace for sort
operations.
Is there any advantage in using a permanent tablespace instead of a
temporary for sort operations?
rgds
amar
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE>Message</TITLE>
<META content="MSHTML 5.50.4807.2300" name=GENERATOR></HEAD> <BODY>
<DIV><SPAN class=040424112-28082001><FONT color=#0000ff size=2>No, it is
generally better practice to use temporary tablespace for the following
reasons.</FONT></SPAN></DIV>
<DIV><SPAN class=040424112-28082001><FONT color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=040424112-28082001><FONT color=#0000ff size=2>1. You
will
not accidentently store objects in it.</FONT></SPAN></DIV> <DIV><SPAN
class=040424112-28082001><FONT color=#0000ff size=2>2. Reduced
contention for the ST enqueue</FONT></SPAN></DIV>
<DIV><SPAN class=040424112-28082001><FONT face=Arial color=#0000ff size=2>3.
Reduce backup and recovery time</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV> <DIV><FONT
face="Times New Roman">"Do not criticize someone until you walked a
mile in their shoes, that way when you criticize them, you are a mile a way
and
have their shoes."</FONT></DIV>
<P><FONT face=Script size=5>Christopher R. Spence</FONT> <BR><FONT
face="Comic Sans MS" size=2>Oracle DBA</FONT> <BR><FONT face="Comic Sans MS"
size=2>Phone: (978) 322-5744</FONT> <BR><FONT face="Comic Sans MS"
size=2>Fax: (707) 885-2275</FONT> </P> <P><FONT
face="Comic Sans MS" size=2>Fuelspot</FONT> <BR><FONT
face="Comic Sans MS" size=2>73 Princeton Street</FONT> <BR><FONT
face="Comic Sans MS" size=2>North, Chelmsford 01863</FONT> <BR><FONT
face="Comic Sans MS" size=2></FONT> </P>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> Amar Kumar
Padhi
[mailto:[EMAIL PROTECTED]] <BR><B>Sent:</B> Tuesday, August 28, 2001
8:11
AM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B>
Permanent Tablespace for sorting. <BR><BR></FONT></DIV><BR>
<P><FONT face=Courier size=2>Oracle recommends creation of separate
temporary
tablespace for sort operations. </FONT><BR><FONT face=Courier
size=2>Is
there any advantage in using a permanent tablespace instead of a temporary
for
sort operations? </FONT></P>
<P><FONT face=Courier size=2>rgds</FONT> <BR><FONT face=Courier
size=2>amar</FONT> </P></BLOCKQUOTE></BODY></HTML>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Amar Kumar Padhi
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
