RE: Oracle RAC TEMP / TEMP GROUP tablespace performance question

From: <Christopher.Taylor2_at_parallon.net>
Date: Tue, 19 Feb 2013 10:50:40 -0600
Message-ID: <F05D8DF1FB25F44085DB74CB916678E885823B7AB5_at_NADCWPMSGCMS10.hca.corpad.net>



Yes have AWR data. Any Temp wait events are really low on the list.

I do have ITL waits on the global temp indexes though due to the way we run sessions concurrently for different facilities. (Think roughly 20 sessions per node, processing different facilities - doing calculations etc, but hitting the same underlying objects for each session. Some objects are partitioned which helps, but the GTemp tables experience concurrency waits)

Chris

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kellyn Pot'vin Sent: Tuesday, February 19, 2013 10:45 AM To: Taylor Christopher - Nashville; gajav_at_yahoo.com; mwf_at_rsiz.com Cc: oracle-l_at_freelists.org
Subject: Re: Oracle RAC TEMP / TEMP GROUP tablespace performance question

Do you have AWR data available to you?  Can you see if you have temp waits for read and writes?  Are the GTT's performance strains for any of these processes that come up in your "top 10" offending processes?

 

Kellyn Pot'Vin
Senior Technical Consultant
Enkitec
DBAKevlar.com
RMOUG Director of Training Days 2013

~Tombez sept fois, se relever huit!



 From: "Christopher.Taylor2_at_parallon.net" <Christopher.Taylor2_at_parallon.net> To: kellyn.potvin_at_ymail.com; gajav_at_yahoo.com; mwf_at_rsiz.com Cc: oracle-l_at_freelists.org
Sent: Tuesday, February 19, 2013 9:42 AM Subject: RE: Oracle RAC TEMP / TEMP GROUP tablespace performance question  

There are a lot of global temp tables that get used by consecutive sessions.
 

The PGA size is 18GB.
 

I have tons (er, lots) of LOB_DATA/LOB_INDEX objects in my v$sort_usage view with an occasional SORT object and small % of DATA/INDEX segments (which I assume or the global temp table segments and indexes).
 

Chris
 
 

From:Kellyn Pot'vin [mailto:kellyn.potvin_at_ymail.com] Sent: Tuesday, February 19, 2013 10:25 AM To: Taylor Christopher - Nashville; gajav_at_yahoo.com; mwf_at_rsiz.com Cc: oracle-l_at_freelists.org
Subject: Re: Oracle RAC TEMP / TEMP GROUP tablespace performance question
 

Now we need to know how your temp is being utilized-  Is it just temp tables?  Are you bypassing your PGA for any hashing and sorting, then going to temp?  This is more in the line of a non-OLTP system, but it's important to know these things, otherwise we are just guessing... :) Thanks,
 
 

Kellyn Pot'Vin
Senior Technical Consultant
Enkitec
DBAKevlar.com
RMOUG Director of Training Days 2013

~Tombez sept fois, se relever huit!


From:"Christopher.Taylor2_at_parallon.net" <Christopher.Taylor2_at_parallon.net> To: kellyn.potvin_at_ymail.com; gajav_at_yahoo.com; mwf_at_rsiz.com Cc: oracle-l_at_freelists.org
Sent: Tuesday, February 19, 2013 8:49 AM Subject: RE: Oracle RAC TEMP / TEMP GROUP tablespace performance question

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?

Chris

From: Kellyn Pot'vin [mailto:kellyn.potvin_at_ymail.com] Sent: Tuesday, February 19, 2013 9:40 AM To: Taylor Christopher - Nashville; gajav_at_yahoo.com; mwf_at_rsiz.com Cc: oracle-l_at_freelists.org
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
Enkitec
DBAKevlar.com
RMOUG Director of Training Days 2013

~Tombez sept fois, se relever huit!



From: "Christopher.Taylor2_at_parallon.net<mailto:Christopher.Taylor2_at_parallon.net>" <Christopher.Taylor2_at_parallon.net<mailto:Christopher.Taylor2_at_parallon.net>> To: kellyn.potvin_at_ymail.com<mailto:kellyn.potvin_at_ymail.com>; gajav_at_yahoo.com<mailto:gajav_at_yahoo.com>; mwf_at_rsiz.com<mailto:mwf_at_rsiz.com> Cc: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> 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 http://orainternals.wordpress.com/2012/02/13/temporary-tablespaces-in-rac/ (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?

Current:
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?)

Chris

-----Original Message-----
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] On Behalf Of Kellyn Pot'vin Sent: Monday, February 18, 2013 8:13 PM
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>; gajav_at_yahoo.com<mailto:gajav_at_yahoo.com> 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
Enkitec

-----Original Message-----
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] On Behalf Of Christopher.Taylor2_at_parallon.net<mailto:Christopher.Taylor2_at_parallon.net> Sent: Thursday, February 14, 2013 5:02 PM To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: Oracle RAC TEMP / TEMP GROUP tablespace performance question

Env: 10.2.0.4 RHEL 5.6 3-node RAC
We have 2 TEMP tablespaces:
TEMP1
TEMP2 We have 1 TEMP GROUP:
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?

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

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


--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 19 2013 - 17:50:40 CET

Original text of this message