RE: Oracle RAC TEMP / TEMP GROUP tablespace performance question
Date: Tue, 19 Feb 2013 12:15:49 -0600
Message-ID: <F05D8DF1FB25F44085DB74CB916678E885823B7D1A_at_NADCWPMSGCMS10.hca.corpad.net>
(apologies to the list if this is not formatted correctly - Email server strips emails down to plain text going to/from Oracle-L for me) Jonathan,
Well, I'm not sure what to say :) I was going to bump up the PCTFREE or INITTRANS for the GT indexes a while ago but you can't modify GT indexes in that way.
Snapshot is for 1 hour - 12am to 1am
Segments by ITL Waits
% of Capture shows % of ITL waits for each top segment compared with total ITL waits for all segments captured by the Snapshot
Owner Tablespace Name Object Name Subobject Name Obj. Type ITL Waits % of Capture CONCUITY TEMP2 GT_CALC_CHG_DTL_IDX01 INDEX 26 14.44 CONCUITY TEMP2 GT_CALC_CHG_DTL_PERF2 INDEX 18 10.00 CONCUITY TEMP2 GT_CALC_CHG_DTL_PERF1 INDEX 16 8.89
Good point about the version differences. I figured the tempfile behavior was common to both 10.2 and 11.2 but you're right, that should be an assumption that I examine/question.
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn direct path write temp 26,913 0.00 59 2 0.31 direct path read temp 1,938 0.00 5 3 0.02 Statistic Total per Second per Trans physical reads direct temporary tablespace 48,735 13.51 0.56 physical writes direct temporary tablespace 398,179 110.41 4.60 Tablespace Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms) TEMP1 172,709 48 3.55 13.73 15,746 4 1,042 3.12 TEMP2 157,938 44 3.38 12.81 27,276 8 831 3.47
I cannot tell from the AWR anything about TEMP lobs, other than a LATCH entry under LATCH ACTIVITY:
Latch Name Get Requests Pct Get Miss Avg Slps /Miss Wait Time (s) NoWait Requests Pct NoWait Miss temp lob duration state obj allocation 43 0.00 0 0
select inst_id, file_id, extents_cached, extents_used from gv$temp_extent_pool order by 1,2 /
INST_ID FILE_ID EXTENTS_CACHED EXTENTS_USED ------- ------- -------------- ------------
1 1 24297 1 2 4373 1 3 15763 1 4 25593 0 5 14112 5 6 25599 7 7 21076 1 8 10514 6 9 14490 3 10 19782 1 2 1 1 1 2 10925 3 3 9836 13 4 4 3 5 11483 12 6 0 0 7 12 2 8 12908 48 9 11108 4 10 5812 3 3 1 1204 12 2 10274 14 3 0 0 4 2 2 5 4 4 6 0 0 7 4511 28 8 2079 7 9 1 1 10 5 3
Regards,
Chris
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Tuesday, February 19, 2013 11:35 AM
To: oracle-l_at_freelists.org
Subject: 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<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 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>
[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<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>
| 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 -- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 19 2013 - 19:15:49 CET