Quick history on database growth

AWR collects segment statistics, and this can be used to quickly understand an abnormal database growth. Here is a script I use to get, from the AWR history, the segments that have grown by more than 1% of the database size, in one hour.

First I must mention that this uses only the part of AWR which is not subject to additional option. This even works in Standard Edition:
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string NONE

So here is the query, easy to modify with different threshold:
set echo on pagesize 1000
set sqlformat ansiconsole
select * from (
select
round(sum(SPACE_ALLOCATED_DELTA)/1024/1024/1024) GBYTE_ALLOCATED
,trunc(max(end_interval_time),'hh24') snap_time
,round(sum(SPACE_ALLOCATED_DELTA)/1024/1024/1024*24*(cast(max(end_interval_time) as date)-cast(min(begin_interval_time) as date))) "GB/hour"
,owner,object_name,subobject_name,object_type
from DBA_HIST_SEG_STAT join DBA_HIST_SEG_STAT_OBJ using (dbid,ts#,obj#,dataobj#) join dba_hist_snapshot using(dbid,snap_id)
group by trunc(end_interval_time,'hh24'),owner,object_name,subobject_name,object_type
) where "GB/hour" > (select sum(bytes)/1024/1024/1024/1e2 "one percent of database size" from dba_data_files)
order by snap_time
;

and the sample output, showing only the snapshots and segments where more than 1% of the database size has been allocated within one hour:

GBYTE_ALLOCATED SNAP_TIME GB/hour OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE
--------------- --------- ------- ----- ----------- -------------- -----------
4 25-OCT-2017 19:00:00 4 BIGDATA SYS_LOB0000047762C00006$$ LOB
9 25-OCT-2017 20:00:00 9 BIGDATA SYS_LOB0000047762C00006$$ LOB
9 25-OCT-2017 21:00:00 9 BIGDATA SYS_LOB0000047762C00006$$ LOB
3 25-OCT-2017 22:00:00 3 BIGDATA SYS_LOB0000047762C00006$$ LOB
5 26-OCT-2017 00:00:00 5 BIGDATA SYS_LOB0000047762C00006$$ LOB
6 26-OCT-2017 01:00:00 6 BIGDATA SYS_LOB0000047762C00006$$ LOB
7 26-OCT-2017 02:00:00 7 BIGDATA SYS_LOB0000047762C00006$$ LOB
7 26-OCT-2017 03:00:00 7 BIGDATA SYS_LOB0000047762C00006$$ LOB
7 26-OCT-2017 04:00:00 7 BIGDATA SYS_LOB0000047762C00006$$ LOB
5 26-OCT-2017 05:00:00 5 BIGDATA SYS_LOB0000047762C00006$$ LOB
2 26-OCT-2017 06:00:00 2 BIGDATA SYS_LOB0000047719C00008$$ LOB
2 26-OCT-2017 06:00:00 2 BIGDATA SYS_LOB0000047710C00006$$ LOB

With this, it is easier to ask to the application owners if this growth is normal or not.

 

Cet article Quick history on database growth est apparu en premier sur Blog dbi services.