Re: Oracle RAC TEMP / TEMP GROUP tablespace performance question
Date: Tue, 19 Feb 2013 22:35:15 -0000
Message-ID: <588AF655EEC2412F874E9A1B747E1E00_at_Primary>
I don't think you can do parallel DML on GTTs so, bugs aside, those ITL
waits can't be happening (unless TEMP2 isn't a temporary tablespace, and
the indexes aren't on GTTs).
Maybe there's some anomaly where one session can report an ITL wait if it's
trying to instantiate a new index segment in the space that another session
is dropping - but I'm just waving my hands and clutching at straws when I
say that.
Given that you have virtually no used extents, but large numbers of cached extents, and that Oracle normally allocates extents from files in a round-robin fashion, and given that Oracle changed the "extent stealing" code between 10.2 and 11.2 (per Riyaj), I'd say that the numbers you show for extents and instances show a relatively small number of fairly large operations have happened, spread across all three instances, but possibly the number of operations is so small that the distribution across instances hasn't had time to appear randomly distributed. I think I'd probably not worry too much about the numbers, but I might look at the history of the pga aggregate histogram report to see if there are a few very large operations spread unevenly across the instances, with variations of spread across time.
One of the interesting things about the figures you posted is that the number of blocks read from the temporary tablespace by direct path is so much smaller than the number written direct - which fits in with your comments about GTTs, of course, but could also point in the direction of subquery factoring. If that's typical across all the instances all the time then maybe there is something a little funny going on with space allocation for GTTs that is introducing your skew.
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: <jonathan_at_jlcomp.demon.co.uk>; <oracle-l_at_freelists.org> Cc: <kellyn.potvin_at_ymail.com>; <gajav_at_yahoo.com>; <mwf_at_rsiz.com>; <oracle-l_at_freelists.org> Sent: Tuesday, February 19, 2013 6:15 PM Subject: RE: Oracle RAC TEMP / TEMP GROUP tablespace performance question
(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
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 19 2013 - 23:35:15 CET