RE: Oracle RAC TEMP / TEMP GROUP tablespace performance question

From: <>
Date: Tue, 19 Feb 2013 09:49:33 -0600
Message-ID: <>

We have a minority of queries using parallelism. This is an OLTP app (with a bit of DW type processing that does occur). I didn't realize that DOP would affect TEMP as sessions run on instances and (I thought) the instances determined where they stored temp data in temp files?

Example a RAC with 3 instances with 1 TEMP space (with 3 tempfiles) - each instance would allocate space as needed in 1 specific tempfile - regardless if those sessions were parallel sessions or not?


From: Kellyn Pot'vin [] Sent: Tuesday, February 19, 2013 9:40 AM To: Taylor Christopher - Nashville;; Cc:
Subject: Re: Oracle RAC TEMP / TEMP GROUP tablespace performance question

Hi Chris,
I think folks understood your goal, but I think we still need answers to our questions. If we don't know how your database uses temp, then it's difficult to offer an answer on how your temp should be configured. Example: If you a majority of queries with DOP of 4 and you have a temp tablespace group of three temp tablespaces, then I would tell you that you need to add a fourth temp tablespace to the group, (each one would then span a separate temp tablespace and performance would be optimized...)

The questions posed to you are so that we are not answering you with "it depends"...:) Thanks!

Kellyn Pot'Vin
Senior Technical Consultant
RMOUG Director of Training Days 2013

~Tombez sept fois, se relever huit!

From: "<>" <<>> To:<>;<>;<> Cc:<> Sent: Tuesday, February 19, 2013 7:11 AM Subject: RE: Oracle RAC TEMP / TEMP GROUP tablespace performance question

I realize now (after receiving a few emails) that my original left something to be desired.

I'm not trying to solve a performance problem really. After reading Riyaj's post (thanks Gaja), I'm seeing similar behavior where there doesn't seem to be a balance between which TEMP tablespace is being used. (Remember, both my TEMP tablespaces make up 1 TEMPGROUP tablespace, and each TEMP tablespace has 5 tempfiles in a 3 node RAC).

To make things more interesting, we have mostly JDBC connections which leave temp segments behind in the TEMP tablespaces (Metalink notes 802897.1, 1384829.1).

My gut 'instinct' says instead of having 2 TEMP tablespaces with 5 tempfiles, I could have 3 (or more TEMP tablespaces with 3 tempfiles each - 3 node RAC) and 1 group (perhaps more than 1 group?) and get better distribution of sorting and spread the IO out across more devices if done properly - though I haven't thought too far along that path yet.

So, I guess my question really is this: Does anyone know (test cases/whitepapers etc) if it makes any difference having multiple temp tablespace with a few tempfiles rather than a few temp tablespaces with many tempfiles?

TEMPGROUP = 2 TEMP tablespaces = 10 largish tempfiles (2x5)

Thinking about:
TEMPGROUP = 4 TEMP tablespaces = 12 smallish tempfiles (4x3) (or could go 5 temp tablespaces with 15 smallish tempfiles and combine with more than 1 tempgroup?)


-----Original Message-----

From:<> [<>] On Behalf Of Kellyn Pot'vin Sent: Monday, February 18, 2013 8:13 PM
To:<>;<> Subject: Re: Oracle RAC TEMP / TEMP GROUP tablespace performance question

I'm curious to know-
 your environment uses parallel and if so, how is it configured? (Par parameters and any resource groups that may degrade DOP) What is you PGA set to? What is your current PGA cache hit and what does your PGA cache hit table look like from awr? Thanks,
Kellyn Pot'Vin
Sir Technical Specialist

-----Original Message-----

From:<> [<>] On Behalf Of<> Sent: Thursday, February 14, 2013 5:02 PM To:<> Subject: Oracle RAC TEMP / TEMP GROUP tablespace performance question

Env: RHEL 5.6 3-node RAC
We have 2 TEMP tablespaces:
TEMP_GRP TEMP1 = 5 tempfiles
TEMP2 = 5 tempfiles

I'm curious if it would make more sense to have multiple TEMP tablepaces, each with 3 datafiles (based on the number of RAC nodes) and 1 TEMP GROUP instead of 2 TEMP tablespaces with multiple tempfiles?

The reason I ask is that I seem to be getting some wonky temp space performance from the current setup and I feel like this could be designed better but I didn't want to start down a path that has been well trod already.

So any of you guys have recommendations on TEMP tablespace setups before I start tearing things apart for testing?

Received on Tue Feb 19 2013 - 16:49:33 CET

Original text of this message