Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Temporary Tablespace and RAC

RE: Temporary Tablespace and RAC

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Sat, 22 Mar 2003 15:30:43 +0800
Message-Id: <24738.322763@fatcity.com>


I agree. I have a similar situation with my 8.1.5 OPS database [going to 9iRAC next month].
One instance is used by query-only users and the other by transaction users, to reduce the probability of "block-pinging". However, as both use the same schema, both share the same TEMPORARY Tablespace. A bad query can blow the usage of the Temporary Segment from the Query Instance and reduce the available space for the Temporary Segment of the Transaction Instance. What makes it worse is that the Tablespace datafile is a Raw Device and I cannot resize or extend it any more !!

At least on 9iRAC on Tru64CFS, I will be placing the Temporary Tablespace on a Unix File System so that I can resize or add new files.

One option would be to create a seperate Schema with all the required SELECT [and INSERT/UPDATE/DELETE] privileges and use the seperate Schema from the second instance. Then, have a seperate Temporary Tablespace for each schema !
Hemant
At 11:24 AM 21-03-03 -0800, you wrote:
>Here's a scenario -
>
>Connections are load balanced (server-side) over both the instances of the
>RAC. Temp Space is 10GB.
>
>User "A" (non-expert) connects to instance 2 from a GUI tool and clicks on
>the data-tab of a 50-million row table and chooses to order by some huge
>column. This results in temp space allocated to Instance 2's temp segment
>and allocated to session A. 9.5 GB has been allocated to Instance 2's temp
>segment, user is still running query. Instance 1 has only 500MB in its TEMP
>segment to serve its sessions.
>
>A production application "B" connects to instance 1 and runs a valid query
>which doesn't have enough TEMP space since all the space has been
>de-allocated from Instance 1's temp segment and allocated to Instance 2 temp
>segment. At this point sessions connected to Instance 1 have very little or
>no temp space.
>
>If 10 GB could be split among instances, or even separate TEMP tablespaces
>tied to each instance, then you can (to some extent) guarantee TEMP space at
>instance level. You're trying to protect sessions connected to one instance
>from a rogue-session on another instance which is draining all TEMP space.
>
>Another way is to have obscene large TEMP space, but there should be a
>better way to handle this.
>
>Hope this makes sense. Any ideas?
>
>-----Original Message-----
>Sent: Thursday, March 20, 2003 6:09 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Hello Ashok
>
>What are u seeing to make you believe that you need separate temp segements
>per instance?
>
>
>Murali Vallath
>
>
>
>
>
>
>
>
>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Date: Thu, 20 Mar 2003 17:14:18 -0800
>
>Is there a way in RAC that I can create 2 TEMP tablespaces which can be
>explicitly assigned to separate instances of RAC. Or is there a way that to
>specify the size of the instance-specific temp segments? If I have a TEMP
>tablespace with a size of 10GB, how can i split it between 2 instances with
>5GB each. We're using 9.2.0.2.
>
>Are there any white papers that talk about how TEMP is managed in RAC?
>
>As I understand it, if you have a single TEMP tablespace, there will be 2
>temp segments created under the TEMP tablespace specific to each instance
>and extents will be allocated from those segments to each instance's
>session, but Oracle doesn't allow for TEMP space to be pre-allocated between
>instances. Depending on the instance-specific TEMP segment's requirements,
>it allocates and deallocates automatically between segments.
>
>
>
>
>
>
>_________________________________________________________________
>STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
>http://join.msn.com/?page=features/junkmail
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Murali Vallath
> INET: murali_vallath_at_hotmail.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (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.net
>--
>Author: Balakrishnan, Ashok - VSCM
> INET: Balakrishnan.Ashok_at_vectorscm.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (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).
Received on Sat Mar 22 2003 - 01:30:43 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US