Re: Oracle RAC TEMP / TEMP GROUP tablespace performance question
Date: Mon, 18 Feb 2013 10:20:35 -0800 (PST)
Message-ID: <1361211635.13607.YahooMailNeo_at_web180904.mail.ne1.yahoo.com>
Chris,
To add to Mark's set of questions:
- how many nodes do you have in your cluster?
- what does "wonky temp space performance mean" :) ? Could you please quantify it :) ?
- are you seeing significant "DFS lock handle" waits in your trace files for queries experiencing "wonky temp space performance"?
- 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_at_rsiz.com> To: Christopher.Taylor2_at_parallon.net; oracle-l_at_freelists.org 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Christopher.Taylor2_at_parallon.net
Sent: Thursday, February 14, 2013 5:02 PM
To: 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?
Regards,
Chris Taylor
Oracle DBA
Parallon IT&S
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 18 2013 - 19:20:35 CET