RE: Oracle RAC TEMP / TEMP GROUP tablespace performance question
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 19 Feb 2013 12:15:49 -0600
(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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Jonathan Lewis
Sent: Tuesday, February 19, 2013 11:35 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxx<mailto:Christopher.Taylor2@xxxxxxxxxxxx>>
To: <kellyn.potvin@xxxxxxxxx<mailto:kellyn.potvin@xxxxxxxxx>>;
<gajav@xxxxxxxxx<mailto:gajav@xxxxxxxxx>>; <mwf@xxxxxxxx<mailto:mwf@xxxxxxxx>>
Cc: <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Kellyn Pot'vin
| Sent: Tuesday, February 19, 2013 10:45 AM
| To: Taylor Christopher - Nashville; gajav@xxxxxxxxx<mailto:gajav@xxxxxxxxx>;
mwf@xxxxxxxx<mailto:mwf@xxxxxxxx>
| Cc: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
| 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?
|
|
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
