RE: Oracle RAC TEMP / TEMP GROUP tablespace performance question

From: <Christopher.Taylor2_at_parallon.net>
Date: Tue, 19 Feb 2013 12:15:49 -0600
Message-ID: <F05D8DF1FB25F44085DB74CB916678E885823B7D1A_at_NADCWPMSGCMS10.hca.corpad.net>



(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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Tuesday, February 19, 2013 11:35 AM To: oracle-l_at_freelists.org
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_at_parallon.net<mailto:Christopher.Taylor2_at_parallon.net>> To: <kellyn.potvin_at_ymail.com<mailto:kellyn.potvin_at_ymail.com>>; <gajav_at_yahoo.com<mailto:gajav_at_yahoo.com>>; <mwf_at_rsiz.com<mailto:mwf_at_rsiz.com>> Cc: <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> 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_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kellyn Pot'vin
| Sent: Tuesday, February 19, 2013 10:45 AM
| To: Taylor Christopher - Nashville; gajav_at_yahoo.com<mailto:gajav_at_yahoo.com>; mwf_at_rsiz.com<mailto:mwf_at_rsiz.com>
| Cc: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
| 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? |
|
--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 19 2013 - 19:15:49 CET

Original text of this message