RE: Oracle RAC TEMP / TEMP GROUP tablespace performance question
Date: Tue, 19 Feb 2013 17:20:45 -0600
Message-ID: <F05D8DF1FB25F44085DB74CB916678E885823B851A_at_NADCWPMSGCMS10.hca.corpad.net>
(Side question: When you got my reply earlier, was it formatted when it hit your mailbox, because when I get it from the list, it has lost its formatting completely.)
I'm not sure I follow what you mean when you say those ITL waits can't be happening? You think the snapshots are incorrect in some way?
Let me give you the full scenario to see if it helps in any way.
At 23:55 I modify the AWR snapshots to start collecting at 10 minute intervals and I was (STRESS: was) collecting MAXIMUM topnsql - I've discovered that was very bad idea because lately I haven't been able to hardly generate an AWR report in a reasonable amount of time. It had gotten so severe that I've totally cleansed my AWR history/snapshots after taking a backup "just in case" though I seriously doubt I'll do anything with that AWR data that I wiped.
So, beginning at 23:55, I'm still collecting 10 minute snapshots but only top 300 SQL using topnsql.
Starting at midnight we have many sessions that kick off from an application server and get distributed across all 3 nodes. This is a very, very, very immature application we're talking about and the code is imaginably inefficient. We have made many strides since I got here in August but we have a long way to go to make this stuff shine.
So we have approximately 37 data load sessions that kick off, then as they finish we have 37 * 3 new sessions that run calculations (using the GTs) so we could have 111 sessions CONCURRENTLY processing all load data into the GT named object - I understand that each of those sessions gets its own "instantiation" of the GT table but somehow that's where the ITL waits are happening.
Does that help clarify at all?
I'll script that query to run every 10 minutes or so during the batch and capture what the temp usage is during the batch processing.
After tonight's run I should have a good clean AWR set of data to start getting AWR data from again as I couldn't hardly get anything from last week's runs. Got greedy trying to capture as many SQL statements as I could because I have a nice SQL script to query the DBA_HIST_SQLSTAT, DBA_HIST_SQLTEXT views to give me matching SQLs that share the same FORCE_MATCHING_SIGNATURE that helps me consolidate long running SQLs across all 3 nodes.
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 4:35 PM
To: oracle-l_at_freelists.org
Subject: Re: Oracle RAC TEMP / TEMP GROUP tablespace performance question
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-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 20 2013 - 00:20:45 CET