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 10:21:10 -0800 (PST)

Have you looked at how the temp is being used then?  I'm a "temp nazi" so I 
have a tendency to look at this as one of the first inspections of a database 
environment.  As I work in mostly large databases, dss/olap environments, 
large temp usage often means someone is working hard and not working smart.  
I am known for putting in monitoring scripts for temp usage that have a 
threshold to notify me, (and others if desired...) when temp usage by any 
process or individual goes beyond a certain level and inspecting temp usage is 
common when performance is in question.
So at a normal processing time of the day, what does this query return to you?

select vst.sql_text, swa.sql_id, swa.sid, swa.tablespace
, swa.operation_type
, trunc(swa.work_area_size/1024/1024) "PGA MB"
, trunc(swa.max_mem_used/1024/1024)"Mem MB"
, trunc(swa.tempseg_size/1024/1024)"Temp MB"
from v$sql_workarea_active swa, v$session vs, v$sqltext vst
where swa.sid=vs.sid
and swa.sql_id=vs.sql_id
and vs.sql_id=vst.sql_id
and vst.piece=0
order by swa.sql_id; 


 
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 10:22 AM
Subject: RE: Oracle RAC TEMP / TEMP GROUP tablespace performance question
 

Like I said, I'm not trying to solve a problem :)
 
It's basically an intellectual discussion – sure temp is not "bad" as is, but 
can it be better?  That's the goal of this discussion – when RAC shares 
tempfiles between the instances, then when is it better to have multiple temp 
tablespaces with a few small tempfiles versus a couple of temp tablespaces with 
largish tempfiles?  That is the question I'm curious about.
 
Chris
 
(From earlier email: 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.
)
From:Kellyn Pot'vin [mailto:kellyn.potvin@xxxxxxxxx] 
Sent: Tuesday, February 19, 2013 11:11 AM
To: Taylor Christopher - Nashville; gajav@xxxxxxxxx; mwf@xxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Oracle RAC TEMP / TEMP GROUP tablespace performance question
 
So if temp waits are WAY down on the list, why do you think temp is the issue 
and not the design or use of your GTT's?
I know, I ask those annoying questions... :)
 
 
KellynPot'Vin
Senior Technical Consultant
Enkitec
DBAKevlar.com
RMOUG Director of Training Days 2013

________________________________
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
--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 02-2013