Sudden jump in tablespace size
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-lReceived on Thu May 24 2012 - 18:35:54 CDT