|
|
This was first published on https://blog.dbi-services.com/quick-history-on-database-growth (2017-11-02)
Republishing here for new followers. The content is related to the the versions available at the publication date
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.