Re: Oracle RAC TEMP / TEMP GROUP tablespace performance question
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 19 Feb 2013 22:35:15 -0000
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
Other related posts:
