How to retrieve growth history for oracle tablespaces
Tags:
SQL Server,
Microsoft Sql Server,
Technical Track,
Database,
Ssis Catalog,
Ssisdb,
Database Migrations,
Database Consulting,
Migrating Ssis Catalog
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.
