Re: Oracle RAC TEMP / TEMP GROUP tablespace performance question

  • From: Gaja Krishna Vaidyanatha <gajav@xxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 18 Feb 2013 10:20:35 -0800 (PST)

Chris,
To add to Mark's set of questions:

1) how many nodes do you have in your cluster?
2) what does "wonky temp space performance mean" :) ? Could you please quantify 
it :) ?
3) are you seeing significant "DFS lock handle" waits in your trace files for 
queries experiencing "wonky temp space performance"?
4) is the TYPE/MODE for the aforementioned wait event either SS/6 or CI/5 ? 

If your answer is >5 for #1 and YES to #3, check out a very good blog post on 
this subject by Riyaj Shamsudeen -  
http://orainternals.wordpress.com/2012/02/13/temporary-tablespaces-in-rac/

Cheers,

Gaja 

Gaja Krishna Vaidyanatha,
CEO & Founder, DBPerfMan LLC
http://www.dbperfman.com
http://www.dbcloudman.com

Phone - +1-650-743-6060
LinkedIn - http://www.linkedin.com/in/gajakrishnavaidyanatha

Co-author: Oracle Insights:Tales of the Oak Table - 
http://www.apress.com/9781590593875
Primary Author: Oracle Performance Tuning 101 - http://www.amzn.com/0072131454
Enabling Cloud Deployment & Management for Oracle & Big Data


________________________________
 From: Mark W. Farnham <mwf@xxxxxxxx>
To: Christopher.Taylor2@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx 
Sent: Saturday, February 16, 2013 6:06 AM
Subject: RE: Oracle RAC TEMP / TEMP GROUP tablespace performance question
 
Do all your users use the same temp?
Do your users have instance affinity when they log on?
Do you have uniform SAME in one piece or separate independent units of I/O
(each SAME individually) that are used for specific purposes?
Do your tempfiles use storage bandwidth on units of I/O that are also used
for regular tablespaces?
Is it okay with you that heavy usage of temp by one user may degrade the
performance of another user?

These are input parameters (and others might exist) to answering your
question.

mwf

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Christopher.Taylor2@xxxxxxxxxxxx
Sent: Thursday, February 14, 2013 5:02 PM
To: oracle-l@xxxxxxxxxxxxx
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?

Regards,


Chris Taylor
Oracle DBA
Parallon IT&S



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


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


Other related posts: