RE: Why does a datafile extend a very large amount at very infrequent intervals?
Date: Fri, 29 Jun 2012 13:43:03 -0400
Message-ID: <9287D00721407A4CBDC9925C789DBB670211A23A09_at_nam-wil-exc-l03.newsamerica.com>
What is the INCREMENT_BY value in DBA_DATA_FILES? Autoextend is a DB file level attribute.
Iordan Iotzov
http://iiotzov.wordpress.com/
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich
Sent: Friday, June 29, 2012 1:33 PM
To: Niall Litchfield
Cc: Oracle-L Freelists
Subject: Re: Why does a datafile extend a very large amount at very infrequent intervals?
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 NOHOST 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 This message and its attachments may contain legally privileged or confidential information. It is intended solely for the named addressee. If you are not the addressee indicated in this message (or responsible for delivery of the message to the addressee), you may not copy or deliver this message or its attachments to anyone. Rather, you should permanently delete this message and its attachments and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of News America Incorporated or its subsidiaries must be taken not to have been sent or endorsed by any of them. No warranty is made that the e-mail or attachment(s) are free from computer virus or other defect. -- http://www.freelists.org/webpage/oracle-lReceived on Fri Jun 29 2012 - 12:43:03 CDT