How to retrieve growth history for oracle tablespaces

A customer recently asked us to identify tablespaces that most contributed to sudden space consumption on ASM. It’s easy enough to find what’s using the most space, but finding what’s been growing is a little less straightforward.

Fortunately, Oracle keeps this information in AWR snapshots, where it’s accessible via: DBA_HIST_TBSPC_SPACE_USAGE:

Name                           Null?    Type
------------------------------ -------- --------------------------------------------
SNAP_ID                                 NUMBER
DBID                           NOT NULL NUMBER
TABLESPACE_ID                           NUMBER
TABLESPACE_SIZE                         NUMBER
TABLESPACE_MAXSIZE                      NUMBER
TABLESPACE_USEDSIZE                     NUMBER
RTIME                                   VARCHAR2(25)
CON_DBID                                NUMBER
CON_ID                                  NUMBER

I’ll confess to not being aware of this table before and I suspect I’m not alone. Though it’s been around since Oracle 10g, I’ve seen plenty of shops where a scheduled script was capturing incremental tablespace and datafile sizes to facilitate capacity planning.

Combining this table with DBA_HIST_SNAPSHOT, V$TABLESPACE, DBA_TABLESPACES, and V$PARAMETER lead me to build a base query for insights into tablespace and database growth:

col ts_mb for 999,999,999,999.90
col max_mb for 999,999,999,999.90
col used_mb for 999,999,999,999.90
col last_mb for 999,999,999,999.90
col incr for 999,999.90

  select * from (
  select v.name
,        v.ts#
,        s.instance_number
,        h.tablespace_size
       * p.value/1024/1024              ts_mb
,        h.tablespace_maxsize
       * p.value/1024/1024              max_mb
,        h.tablespace_usedsize
       * p.value/1024/1024              used_mb
,        to_date(h.rtime, 'MM/DD/YYYY HH24:MI:SS') resize_time
,        lag(h.tablespace_usedsize * p.value/1024/1024, 1, h.tablespace_usedsize * p.value/1024/1024)
         over (partition by v.ts# order by h.snap_id) last_mb
,        (h.tablespace_usedsize * p.value/1024/1024)
       - lag(h.tablespace_usedsize * p.value/1024/1024, 1, h.tablespace_usedsize * p.value/1024/1024)
         over (partition by v.ts# order by h.snap_id) incr
    from dba_hist_tbspc_space_usage     h
,        dba_hist_snapshot              s
,        v$tablespace                   v
,        dba_tablespaces                t
,        v$parameter                    p
   where h.tablespace_id                = v.ts#
     and v.name                         = t.tablespace_name
     and t.contents                not in ('UNDO', 'TEMPORARY')
     and p.name                         = 'db_block_size'
     and h.snap_id                      = s.snap_id
         /* For a specific time */
     and s.begin_interval_time          > sysdate - 1/12
         /* For a specific tablespace */
     and v.ts# = 1
order by v.name, h.snap_id asc)
   where incr > 0;

LAG is an Oracle analytic function for finding the prior value in a list. It can’t be used directly in the WHERE clause of a query. To get only the rows where a change has occurred it’s necessary to look for non-zero values in the result by wrapping it in a SELECT. The join to DBA_TABLESPACES allows exclusion of undo and temporary tablespaces, which are often volatile.

Sizes in DBA_HIST_TBSPC_USAGE_METRICS are reported in database blocks. Translating this to “real” numbers is done by joining to V$PARAMETER to get the database block size.

In the example the result is limited to just the last six hours or activity for tablespace 1:

NAME         TS# INSTANCE_NUMBER               TS_MB              MAX_MB             USED_MB RESIZE_TIME                        LAST        INCR
---------- ----- --------------- ------------------- ------------------- ------------------- ------------------- ------------------- -----------
SYSAUX         1               2           93,852.00          141,319.94           17,182.13 2019-02-25 13:15:32           17,181.06        1.06
SYSAUX         1               1           93,852.00          141,319.94           17,186.13 2019-02-25 13:30:38           17,182.13        4.00
SYSAUX         1               2           93,852.00          141,319.94           17,187.13 2019-02-25 13:45:43           17,186.13        1.00
SYSAUX         1               1           93,852.00          141,319.94           17,189.13 2019-02-25 14:00:47           17,187.13        2.00

4 rows selected.

When used as a base query a summary of tablespace growth history is available for as far back as AWR data is retained:

break on resized
    with ts_history as (
  select * from (
  select v.name
,        v.ts#
,        s.instance_number
,        h.tablespace_size
       * p.value/1024/1024              ts_mb
,        h.tablespace_maxsize
       * p.value/1024/1024              max_mb
,        h.tablespace_usedsize
       * p.value/1024/1024              used_mb
,        to_date(h.rtime, 'MM/DD/YYYY HH24:MI:SS') resize_time
,        lag(h.tablespace_usedsize * p.value/1024/1024, 1, h.tablespace_usedsize * p.value/1024/1024)
         over (partition by v.ts# order by h.snap_id) last
,        (h.tablespace_usedsize * p.value/1024/1024)
       - lag(h.tablespace_usedsize * p.value/1024/1024, 1, h.tablespace_usedsize * p.value/1024/1024)
         over (partition by v.ts# order by h.snap_id) incr
    from dba_hist_tbspc_space_usage     h
,        dba_hist_snapshot              s
,        v$tablespace                   v
,        dba_tablespaces                t
,        v$parameter                    p
   where h.tablespace_id                = v.ts#
     and v.name                         = t.tablespace_name
     and t.contents                not in ('UNDO', 'TEMPORARY')
     and p.name                         = 'db_block_size'
     and h.snap_id                      = s.snap_id
order by v.name, h.snap_id asc)
   where incr > 0)
  select to_char(resize_time, 'YYYY-MM') as resized
,        name
,        sum(incr)                      incr
    from ts_history
group by name
,        to_char(resize_time, 'YYYY-MM')
order by 1, 3 desc;

This shows that the majority of growth occurred in the INDEXES tablespace:

RESIZED NAME                                  INCR
------- ------------------------------ -----------
2019-02 INDEXES                          21,383.50
        DATA                             12,649.00
        SYSAUX                              529.63
        USERS                                 1.06
        SYSTEM                                1.00

5 rows selected.

Showing daily or even hourly growth for an individual tablespace is accomplished by manipulating the date format of RESIZED. This can help users correlate growth in the database to a business operation—a day of high sales, month-end processing, even the introduction of troublesome code.