Re: Oracle RAC TEMP / TEMP GROUP tablespace performance question

If you want to query event wait information from AWR, the "awr_evclasstrends.sql" script downloadable from "http://evdbt.com/scripts/" might be helpful. It starts from a high-level, then prompts you to focus on an "event class" after showing you the relative stats amongst event classes, then shows time-series data for individual events within the class summarized by day, by hour, then by snapshot. If your AWR snapshots are hourly, the last two sections of the report are redundant, of course. My guess is that you'd be interested in event class "User I/O" and the events "direct path read temp" and "direct path write temp".

The related "awr_evtrends.sql" script is very similar, just starting directly from a list of event names, instead of allowing drill-down from event class as the "awr_evclasstrends.sql" script does.

Both of these reports show three groups of columns. The first group of columns shows the number of waits, the second group the total elapsed time of the waits, and the third group shows average time per wait. Each of these metrics can be interpreted differently for I/O events. Seeing variations in the number of waits indicates changes either in application workload or execution plans, variations in average time per wait can indicate issues in the underlying I/O subsystem, and having the report sorted by total elapsed time of the waits shows relative importance of each event. Variations over time in total elapsed time of waits can then be explained by variations in either numbers of waits or average wait times.

Each of these interpretations can help lead toward a root cause, but certainly cannot prove a root cause by itself, as the cited explanations above are just a partial list of explanations.

Hope this helps...

Thanks!

-Tim

On 2/19/2013 9:45 AM, Kellyn Pot'vin wrote:
> 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-
> 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_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