RE: Using multiple Temp tablespaces

Yeah, we had much the same type of problem here a while ago.

I created a separate temp space for the "problem" users and assigned them to that one - that way the rest of production isn't affected and they can be controlled more closely. Seems a good plan to me, also.

We are on 9205, RH Linux.
We get some 1652's in the alert log and we have to determine if we are really out of space; usually not, but we have to chase it down anyway.

Which OS are you on?
Have you seen that bug 2858082 is fixed on 9207 in your environment?

I now have it balanced such that there is very little of the borrowing going on, but I had to waste some space in doing so. I also found that if the instance doesn't have to borrow at all, there is a performance gain.

Hope this helps,
Rich

-----Original Message-----
From: Prabhu, Krishnaswamy [mailto:prabhu_adam_at_hotmail.com] Sent: Saturday, July 08, 2006 7:32 PM
To: ramick_at_dotster.com; oracle-l_at_freelists.org Subject: RE: Using multiple Temp tablespaces

>>May I ask why you intend to create additional temporary tablespaces?

Ramick, we are facing lot of issues related to temp segment space and most of our load jobs failures are happened owing to the temp segment space issues. often we found that this was due to the Apps user who ran a bad query which shoots up heavy temp segment space >100 gig, those times we'll kill the top sessions. Hence, now we thought of creating some additional temp tablespaces to support our load as well as report jobs.

Thanks,
Prabhu

>From: "ramick" <ramick_at_dotster.com>
>To: "'Prabhu, Krishnaswamy'"
><prabhu_adam_at_hotmail.com>,<oracle-l_at_freelists.org>
>Subject: RE: Using multiple Temp tablespaces
>Date: Sat, 8 Jul 2006 18:17:38 -0700
>
>Check which files are owned by which instance. I think this depends upon
>startup times of the instances - sort of like the old rollback segments
>where each instance will try to online any segment it can during startup.
>Someone please correct me if this is wrong.
>
>If all nodes are active during the use of current temp space, I would lean
>towards evenly distributing the temp files so there is the least amount of
>borrowing going on.
>
>I would also monitor the use of these segments during use.
>
>May I ask why you intend to create additional temporary tablespaces?
>
>-----Original Message-----
>From: Prabhu, Krishnaswamy [mailto:prabhu_adam_at_hotmail.com]
>Sent: Saturday, July 08, 2006 5:12 PM
>To: ramick_at_dotster.com; oracle-l_at_freelists.org
>Subject: RE: Using multiple Temp tablespaces
>
>Many Thanks Ramick.
>
>Its a datawarehouse environment, our current temporary tablespace size is
>250 gig. Ten tempfiles are there, each 25 gig in size.
>
>Thanks,
>Prabhu
>
> >From: "ramick" <ramick_at_dotster.com>
> >Reply-To: ramick_at_dotster.com
> >To: <prabhu_adam_at_hotmail.com>,<oracle-l_at_freelists.org>
> >Subject: RE: Using multiple Temp tablespaces
> >Date: Sat, 8 Jul 2006 15:23:41 -0700
> >
> >How many files do you have in your current temporary tablespace?
> >
> >See Bug No. 2858082 - this is supposed to be fixed in 9207 & 10201+, but
> >you
> >might want to make sure.
> >
> >Also, please see Oracle Note:258941.1 - RAC databases and ORA-1652:
>Unable
> >To Extend Temp Segment By %s In Tablespace TEMP
> >This as well as it's referances should give you a good understanding of
>how
> >RAC uses temp segments.
> >
> >-----Original Message-----
> >From: oracle-l-bounce_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org]
> >On Behalf Of Prabhu, Krishnaswamy
> >Sent: Saturday, July 08, 2006 3:11 PM
> >To: oracle-l_at_freelists.org
> >Subject: Using multiple Temp tablespaces
> >
> >Hi List,
> >
> >Currently we are using only one temporary tablespace for all three node
>RAC
> >database (9207). We are planning to create additonal temporary
>tablespaces.
> >In this
> >regard, I would like know if there are any issues/concerns using
>additional
> >tempoary tablespaces in a three node 9207 RAC system.
> >Please advice.
> >
> >Thanks,
> >Prabhu
> >
> >
> >--
> >http://www.freelists.org/webpage/oracle-l
> >
> >
> >
> >--
> >http://www.freelists.org/webpage/oracle-l
> >
> >
>
>
>

--
http://www.freelists.org/webpage/oracle-l