Tales From A Lazy Fat DBA

Hi Pals,

Many of the times while doing sizing, capacity planning and requirement calculations you might have come across a point where you have to share database or table level growth stats to your customer for estimations. So – What should be the right and the accurate way or the method to track the database or table level growth in your databases ?

Today i will discuss about the ways to precisely calculate or track growth of your database we will surely come up with solutions like
– Do a join on V$DATAFILE, V$TEMPFILE, V$LOG and some help from DBA_FREE_SPACE.
– Else a different approach is using DBA_HIST views (DBA_HIST_SEG_STAT) in conjunction with dba_segments view.

As far as both of the approaches, second one (Using DBA_HIST views) is the most reliable,trusted and by far accurate way of tracking sizing information of the database But most of the DBA_HIST tables are part of Diagnostics Pack and should be accessed only if you have the license. So i advocate here another way which is manual method using customized PL/SQL code to monitor the growth trends of both your database and tables.

Here i would like to thank one of the best PL/SQL coder i know – Mr. Komal Chand Lilhare (Master Coder 🙂 ) who is the original author behind these scripts and i have only made few additions and customization’s as per my need.

DB GROWTH JOB:

Step 1. Create table to capture DB growth

create table db_growth
as select *
from (
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE% "
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
);

Step2. Create view to automate the capture process through DB jobs

create view v_db_growth
as select *
from
(
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
);

Step3. Create DB Job to capture the growth on regular interval

declare
jobno number;
begin
dbms_job.submit(
jobno, 'begin insert into db_growth select * from v_db_growth;commit;end;', sysdate, 'trunc(SYSDATE)+1', TRUE);
commit;
end;
/

Step4. Data analysis. It’s all your SQL skills how you want to see and present the data trend.

set linesize 130;
col what for a30;
col log_user for a12;
col interval for a30;

select job,what,log_user, to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') "Sysdate", 
to_char(next_date,'dd-mon-yyyy hh24:mi:ss') "Next Date",
interval from user_jobs;


       JOB WHAT                           LOG_USER     Sysdate              Next Date            INTERVAL
---------- ------------------------------ ------------ -------------------- -------------------- ------------------------------
        23 begin insert into db_growth se SYS          26-dec-2017 23:18:18 17-dec-2017 00:00:00 trunc(SYSDATE)+1
           lect * from v_db_growth;commit
           ;end;

        24 begin insert into table_growth SYS          26-dec-2017 23:18:18 27-dec-2017 00:00:00 trunc(SYSDATE)+1
           _MB select * from v_table_grow
           th_MB;commit;end;




select * from db_growth order by 1;

SYSDATE   TOTAL_SIZE FREE_SPACE     FREE%
--------- ---------- ---------- ----------
15-DEC-17 167935.125 36946.0625 22.0001995
16-DEC-17 167935.125 36944.9375 21.9995296


select trunc("SYSDATE") Dt,round("TOTAL_SIZE") "TOTAL_SIZE(M)", round("TOTAL_SIZE"- "FREE_SPACE")"USED_SPACE(M)" , 
round("FREE_SPACE") "FREE_SPACE(M)"  
from sys.db_growth order by 1;

DT        TOTAL_SIZE(M) USED_SPACE(M) FREE_SPACE(M)
--------- ------------- ------------- -------------
15-DEC-17        167935        130989         36946
16-DEC-17        167935        130990         36945

TABLE GROWTH (IN MBs) SCRIPT:

Step 1. Check if table table_growth_MB is present in the database;

Step 2. Create a table and view which captures database tables which are more then 100MB in size, you can change it as per need.

create table table_growth_MB as 
SELECT trunc(sysdate) DT, owner,table_name, TRUNC(sum(bytes)/1024/1024) size_MB , 0 row_count
FROM (SELECT segment_name table_name, owner, bytes 
FROM dba_segments WHERE segment_type = 'TABLE' 
UNION ALL 
SELECT i.table_name, i.owner, s.bytes FROM dba_indexes i, dba_segments s WHERE s.segment_name = i.index_name 
AND   s.owner = i.owner AND   s.segment_type = 'INDEX' 
UNION ALL 
SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.segment_name 
AND   s.owner = l.owner AND   s.segment_type = 'LOBSEGMENT' 
UNION ALL 
SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.index_name 
AND   s.owner = l.owner AND   s.segment_type = 'LOBINDEX')
where table_name = 'H304'
GROUP BY table_name, owner, trunc(sysdate)
HAVING SUM(bytes)/1024/1024 > 100 ;

Step 3.

create or replace view v_table_growth_MB as 
SELECT trunc(sysdate) DT, owner,table_name, TRUNC(sum(bytes)/1024/1024)  size_MB, 0 row_count
FROM (SELECT segment_name table_name, owner, bytes 
FROM dba_segments WHERE segment_type = 'TABLE' 
UNION ALL 
SELECT i.table_name, i.owner, s.bytes FROM dba_indexes i, dba_segments s WHERE s.segment_name = i.index_name 
AND   s.owner = i.owner AND   s.segment_type = 'INDEX' 
UNION ALL 
SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.segment_name 
AND   s.owner = l.owner AND   s.segment_type = 'LOBSEGMENT' 
UNION ALL 
SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.index_name 
AND   s.owner = l.owner AND   s.segment_type = 'LOBINDEX')
where table_name = 'T312'
GROUP BY table_name, owner, trunc(sysdate) 
HAVING SUM(bytes)/1024/1024 > 100 ;

Step 4. Create a job which will run daily and capture the table growth.

declare
jobno number;
begin
dbms_job.submit(
jobno, 'begin insert into table_growth_MB select * from v_table_growth_MB;commit;end;', sysdate, 'trunc(SYSDATE)+1', TRUE);
commit;
end;
/

Step 5. Analyze the data, it’s all your SQL skill how you want to see and present the trend analysis.

– Job Details:

set linesize 130;
col what for a30;
col log_user for a12;
col interval for a30;

select job,what,log_user, to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') "Sysdate", 
to_char(next_date,'dd-mon-yyyy hh24:mi:ss') "Next Date",
interval from user_jobs;


ALTER SESSION SET NLS_DATE_FORMAT='dd-Month-yyyy';

set pages 0
set lines 120
col DT for a20 
col owner for a20 
col table_name format a30
col size_MB format 999999.99
col row_count for 999999999
select trunc(DT), owner,table_name, size_MB , row_count from table_growth_MB 
where table_name like '%SAMPLE%'
order by 1;


select TABLE_NAME, SIZE_MB , DT from table_growth_MB  where trunc(dt) = trunc(sysdate-45) 
and  table_name in (select TABLE_NAME  from table_growth_MB T1 where dt> sysdate-2
and exists (select 1 from table_growth_MB T2 where trunc(T2.dt) = trunc(sysdate-45) and T2.table_name= T1.table_name and T1.SIZE_MB>T2.SIZE_MB+400))
union all
select TABLE_NAME, SIZE_MB , DT from table_growth_MB T1 where dt> sysdate-1
and exists (select 1 from table_growth_MB T2 where trunc(T2.dt) = trunc(sysdate-45) and T2.table_name= T1.table_name and T1.SIZE_MB>T2.SIZE_MB+400)
order by  TABLE_NAME, DT;

Hope It Helps
Prashant Dixit