Re: Oracle RAC TEMP / TEMP GROUP tablespace performance question
- From: Kellyn Pot'vin <kellyn.potvin@xxxxxxxxx>
- To: "Christopher.Taylor2@xxxxxxxxxxxx" <Christopher.Taylor2@xxxxxxxxxxxx>, "gajav@xxxxxxxxx" <gajav@xxxxxxxxx>, "mwf@xxxxxxxx" <mwf@xxxxxxxx>
- Date: Tue, 19 Feb 2013 08:45:01 -0800 (PST)
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@xxxxxxxxxxxx" <Christopher.Taylor2@xxxxxxxxxxxx>
To: kellyn.potvin@xxxxxxxxx; gajav@xxxxxxxxx; mwf@xxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx]
Sent: Tuesday, February 19, 2013 10:25 AM
To: Taylor Christopher - Nashville; gajav@xxxxxxxxx; mwf@xxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxx" <Christopher.Taylor2@xxxxxxxxxxxx>
To: kellyn.potvin@xxxxxxxxx; gajav@xxxxxxxxx; mwf@xxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx]
Sent: Tuesday, February 19, 2013 9:40 AM
To: Taylor Christopher - Nashville; gajav@xxxxxxxxx; mwf@xxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxx<mailto:Christopher.Taylor2@xxxxxxxxxxxx>"
<Christopher.Taylor2@xxxxxxxxxxxx<mailto:Christopher.Taylor2@xxxxxxxxxxxx>>
To: kellyn.potvin@xxxxxxxxx<mailto:kellyn.potvin@xxxxxxxxx>;
gajav@xxxxxxxxx<mailto:gajav@xxxxxxxxx>; mwf@xxxxxxxx<mailto:mwf@xxxxxxxx>
Cc: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] On
Behalf Of Kellyn Pot'vin
Sent: Monday, February 18, 2013 8:13 PM
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>;
gajav@xxxxxxxxx<mailto:gajav@xxxxxxxxx>
Subject: Re: Oracle RAC TEMP / TEMP GROUP tablespace performance question
I'm curious to know-
If 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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>]
On Behalf Of
Christopher.Taylor2@xxxxxxxxxxxx<mailto:Christopher.Taylor2@xxxxxxxxxxxx>
Sent: Thursday, February 14, 2013 5:02 PM
To: oracle-l@xxxxxxxxxxxxx<mailto: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?
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
