Why does a datafile extend a very large amount at very infrequent intervals?
- From: Rich <richa03@xxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 29 Jun 2012 08:58:29 -0700
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
