Re: Oracle RAC TEMP / TEMP GROUP tablespace performance question

ITL waits on indexes on GTTs shouldn't be possible. GTTs are private to sessions, ITL waits on indexes should require multiple sessions modifying the same block.

Bear in mind that you are using 10.2.0.4, and Riyaz' note is about 11.2.and mentions changes made to deal with RAC-specific options - any advice you get may not be appropriate for your version, and some of the anomalies you see may only be relevant to your version.

I note that you've got temporary LOBs hitting the system - what do your Instance Activity stats look like (in a typical snapshot) for physical read and write to temp, and does your version further split these down to show temp LOBs separately ? Also, if you ran Riyaz' query for cached and used extents, what does it look like ?

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• Original Message ----- From: <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 4:50 PM Subject: RE: Oracle RAC TEMP / TEMP GROUP tablespace performance question

| Yes have AWR data. Any Temp wait events are really low on the list.
|
| I do have ITL waits on the global temp indexes though due to the way we
run sessions concurrently for different facilities. (Think roughly 20 sessions per node, processing different facilities - doing calculations etc, but hitting the same underlying objects for each session. Some objects are partitioned which helps, but the GTemp tables experience concurrency waits)
|
| Chris
|
| -----Original Message-----
| From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kellyn Pot'vin
| Sent: Tuesday, February 19, 2013 10:45 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
|
| 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?
|
|

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