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

From: Rich <richa03_at_gmail.com>
Date: Fri, 29 Jun 2012 08:58:29 -0700
Message-ID: <CALgGkeBGSAaGwy=jN_RvadRj8F5+KYnaBdTBUCRS8A_5gq8yEw_at_mail.gmail.com>

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

Received on Fri Jun 29 2012 - 10:58:29 CDT