Sudden jump in tablespace size

From: C Zhou <czhou.dba_at_gmail.com>
Date: Thu, 24 May 2012 16:35:54 -0700
Message-ID: <CAHBvy36JeUkz-12fy1AenL1WHANC6uruY9cp=rKkfqOxag7PtQ_at_mail.gmail.com>



Hi,
I am having a hard time to try to drill down into this issue and wonder if anyone has come across the same issue.

We monitor tablespace sizes by watching the (maxbytes-bytes) as extensible space in dba_data_files.
One index tablespace in a 11.2.0.2 database (with ASM, on RHEL 5.6)usually grew about 0.5G a day, then one day it grew 18G (and it stopped growing and its extensible space has not changed for a month now). The activities on the database was normal for that day (no big load).

Here is the tablespace:

select BLOCK_SIZE, NEXT_EXTENT, PCT_INCREASE, MIN_EXTLEN, EXTENT_MANAGEMENT, ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT, ENCRYPTED, COMPRESS_FOR
from DBA_TABLESPACES where TABLESPACE_NAME = '....';

      BLOCK_SIZE NEXT_EXTENT PCT_INCREASE MIN_EXTLEN EXTENT_MAN ALLOCATIO SEGMEN ENC COMPRESS_FOR

---------------- ---------------- ---------------- ----------------
---------- --------- ------ --- ------------
           8,192       16,777,216                0       16,777,216
LOCAL      UNIFORM   AUTO   NO

We are puzzled by its growth and tried to use dba_hist_seg_stat and dba_hist_snapshot to check the SPACE_USED_DELTA and SPACE_ALLOCATED_DELTA for the
indexes in that tablespace. The sum of the SPACE_USED_DELTA for that day is 63.5MB and sum of SPACE_ALLOCATED_DELTA is only 838MB.

When we opened a SR with Oracle, the support said that dba_hist_seg_stat is not accurate and suggested we use DBMS_SPACE.object_growth_trend. I did a 10046 trace on object_growth_trend, the trace file show that it queries WRM$_SNAPSHOT, WRH$_SEG_STAT_OBJ and WRH$_SEG_STAT, same as DBA_HIST_SEG_STAT.
I asked if object_growth_trend is going to be accurate even though it is based on the same tables as dba_hist_seg_stat and have not heard a direct answer back from Support since.

So I have 2 questions:
(1) Has anyone had the same experience with a (INDEX) tablespace's sudden
growth?
(2) Is there a good way to investigate where the growth goes into?

Thank you,

Chen

--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 24 2012 - 18:35:54 CDT

Original text of this message