Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of Cassandra, MySQL and PostgreSQL …

  • Likes

    • 138,422
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Scripts: How to accurately calculate Database and Table growth.

Posted by FatDBA on December 26, 2017

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: