RE: Oracle RAC TEMP / TEMP GROUP tablespace performance question
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Tue, 19 Feb 2013 17:20:45 -0600
(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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Jonathan Lewis
Sent: Tuesday, February 19, 2013 4:35 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxx>
To: <jonathan@xxxxxxxxxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
Cc: <kellyn.potvin@xxxxxxxxx>; <gajav@xxxxxxxxx>; <mwf@xxxxxxxx>;
<oracle-l@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
