Infrastructure at your Service

Franck Pachot

Quick history on database growth

By November 2, 2017 Oracle One Comment

By Franck Pachot

.
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.

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist
Oracle ACE Director
Oracle Database OCM 12c certified
AWS Database Specialty certified
Oak Table member

RSS for this blog: feed
Twitter: @FranckPachot
LinkedIn : www.linkedin.com/in/franckpachot
Podcast en français: DBPod