Re: Why does a datafile extend a very large amount at very infrequent intervals?

From: Rich <richa03_at_gmail.com>
Date: Fri, 29 Jun 2012 10:33:06 -0700
Message-ID: <CALgGkeAAcUBf95bKs0Q2055=T-Tm223kzEmjjP69bX7hoy9MzA_at_mail.gmail.com>

Thanks for the response, Niall.
We aren't doing index rebuilds - we don't do bulk loads/deletes - we basically just insert.

We also see this happening with other tablespaces - another example of this is TS RTDATA which contains only "normal" heap tables:

21-APR-12 03.20.33.697 AM RTDATA 21190669 21182464 21-APR-12 03.30.34.273 AM RTDATA 21223437 21213184 21-APR-12 03.40.34.974 AM RTDATA 21227533 21217280 …

16-MAY-12 07.40.57.438 PM RTDATA 21227533 21100557 16-MAY-12 07.50.58.397 PM RTDATA 22163465 21100553 …

25-JUN-12 10.10.48.431 AM RTDATA 22163465 22040585 25-JUN-12 10.20.48.814 AM RTDATA 23146505 22040585 We didn't see this behavior (datafiles extending large amounts infrequently) in 10.2.0.4 (non-ASM) - we saw datafiles increase by ~250MB more frequently.
This is disconcerting as we use these increases to trend growth - once per month is not sufficient for us.

The properties for the tablespaces in these examples are: TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS

MAX_EXTENTS      MAX_SIZE  PCT_INCREASE    MIN_EXTLEN STATUS    CONTENTS
LOGGING   FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION   BIG
PREDICA ENC COMPRESS_FOR
--------------- ------------- -------------- ------------- -------------
------------- ------------- ------------- ------------- --------- ---------

--------- --- ---------- --------- --- ------ -------- ----------- --- ------- --- ------------

RTINDX                   8192       16777216      16777216             1
2147483645                           0      16777216 ONLINE    PERMANENT
LOGGING   NO  LOCAL      UNIFORM   NO  AUTO   DISABLED NOT APPLY   NO
HOST    NO
RTDATA                   8192       16777216      16777216             1
2147483645                           0      16777216 ONLINE    PERMANENT
LOGGING   NO  LOCAL      UNIFORM   NO  AUTO   DISABLED NOT APPLY   NO

HOST NO On Fri, Jun 29, 2012 at 9:51 AM, Niall Litchfield < niall.litchfield_at_gmail.com> wrote:

> Anyone rebuilding those indexes and so creating a temp segment in the ts?
> I imagine this would show up as lots of growth and then a decent amount of
> free space. It would likely show up in the top sql awr report as well.
> On Jun 29, 2012 5:00 PM, "Rich" <richa03_at_gmail.com> wrote:
>
>> Hello List,
>> Why does a datafile extend a very large amount at very infrequent
>> intervals?
>> This is 11.2.0.2.0-5 on RHEL 5.6 x86_64 w/ASM.
>>
>> We are using ASSM.
>>
>> After migration from 10.2.0.4, we see larger than expected disk space
>> allocations at the OS for tablespaces - calling them disk grabs.
>>
>> E.g., a datafile in the TS RTINDX (ts# 14) grows 19.8GB during a single
>> snap time of 10min where there is no growth during the past month with
>> essentially the same load throughout the month - we don't do anything
>> special that I know of at the end of the month.
>>
>> Looking at dba_hist_tbspc_space_usage (updated every 10min - units are
>> blocks), I see:
>> BEGIN_INTERVAL_TIME NAME TABLESPACE_SIZE TABLESPACE_USEDSIZE
>> 26-APR-12 06.20.34.337 AM RTINDX 46445116
>> 46316092
>> 26-APR-12 06.30.34.782 AM RTINDX 48634426
>> 46318138
>> …
>> 29-MAY-12 08.40.24.658 AM RTINDX 48636482
>> 48507458
>> 29-MAY-12 08.50.25.257 AM RTINDX 50938434
>> 48507458
>> …
>> 28-JUN-12 02.40.24.384 PM RTINDX 50938434
>> 50807362
>> 28-JUN-12 02.50.24.939 PM RTINDX 53355074
>> 50809410
>>
>> This view believes TS RTINDX grew by 2545664 blocks during the last
>> interval.
>> The block size for this TS is 8192:
>> select BLOCK_SIZE from dba_tablespaces where TABLESPACE_NAME = 'RTINDX';
>>
>> Thus, this view believes it grew by 20,854,079,488 bytes - substantially
>> correct.
>>
>> We have NEXT_EXTENT for this tablespace set at 16777216.
>> select NEXT_EXTENT from dba_tablespaces where TABLESPACE_NAME = 'RTINDX';
>> 16777216 - I believe this is in bytes and is the default?
>>
>> I would expect this tablespace to extend in increments of 16MB - while I
>> realize there are other implications to this small growth-rate, that's the
>> way I would currently prefer it.
>>
>> This tablespace has 266 indexes in it.
>> All 266 of the indexes have NEXT_EXTENT set at 16777216 (in bytes? -
>> default?).
>> If every one of these objects were to extend (highly unlikely), we would
>> have 4,462,739,456 bytes.
>>
>> I checked dba_hist_seg_stat (also updated every 10min), however, quoting
>> Oracle Reference for 11.2:
>> "This view captures the top segments based on a set of criteria and
>> captures information from V$SEGSTAT. The total value is the value of the
>> statistics since instance startup. The delta value is the value of the
>> statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the
>> DBA_HIST_SNAPSHOT view."
>> Some of the segments are not represented here due to the "set of criteria"
>> - anyone know what that is?
>>
>> This scenario happens for other tablespaces as well and in multiple Oracle
>> instances at this version, but not at 10.2.0.4.
>>
>> Would someone please give me a clue as to potentially why and where to
>> look
>> to prove it?
>>
>> Thanks,
>> Rich
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jun 29 2012 - 12:33:06 CDT