Thursday, April 14, 2011

Tracking Oracle database growth

Keeping track of database space growth in production environments is a must-to-do job of every DBA. Maintaining and reporting information of historical database growth is always a task that involves good amount of planning and scheduling various jobs in the database.

Oracle provides time-series segment statistics for tables and index in the 10g Automated  workload Repository tables (AWR).  These can quickly extract data for Oracle growth reports. In Oracle 10g, total database  growth  reports  are  is  easy because the dba_hist_seg_stat tables  provides   the delta value for every table and index. Starting from 10g, Oracle has provided us with a feature to achieve this without much overhead. Most importantly, we can use this with out incurring any extra license cost. This technique is used by many DBA’s but not much documentation is available over the internet for easy use.

This script is helpful in tracking  the oracle growth

SQLselect b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb , round(AVG(inc_used_size_mb),2)avg_increas_mb  from  ( SELECT a.days,a.tsname , used_size_mb , used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days  ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
from dba_hist_tbspc_space_usage  tsu , dba_hist_tablespace_stat  ts ,dba_hist_snapshot  sp, dba_tablespaces  dt   where tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname 
ORDER BY ts.tsname, days ) a
) b GROUP BY b.tsname ORDER BY b.tsname;
 Output : 

--------------------             -----------------------      ------------------------
ENOM_CM_DATA                          1121.31                            78.71
ENOM_CM_INDEX                         2.38                                  0
ENOM_EL_CATALOG                      4.13                                 .01
ENOM_EL_DATA                           13.63                                0
ENOM_EL_INDEX                          16.06                               .01
ENOM_GS_CATALOG                     1                                      0
ENOM_GS_DATA                          1                                      0
ENOM_LA_DATA                          5                                      0
ENOM_LA_INDEX                         3                                      0
EXAMPLE                                   78.44                                0
SYSAUX                                    614.75                              14.46
SYSTEM                                    692.88                              1.32
UNDOTBS1                                48                                    -.96
USERS                                     644.38                              91.47
14 rows selected.

Enjoy     :-)


Anonymous said...

AWESOME...just what I was looking for!!

Thank you.

Siva said...

Hello Neeraj,

I am a Pl\sql developer and just starting to learn about DBA activities. I have been asked to track the weekly growth of one particular DB schema. Here sql is really helpful. Thankssssss a lot:)

Just curious..wanted to know if we can use SPACE_USED_DELTA in DBA_HIST_SEG_STAT to get the weekly growth. If yes, can you help me in writing that sql.

earslan said...

--Db Size Change
/* Can be useful for identifing total db growth by analyzing all snapshots one by one , and calculating total size change over given period.*/
--Cursor Declaretion
select sum(round((tablespace_usedsize*8*1024)/1024/1024,2)) as "used space",snap_id from DBA_HIST_TBSPC_SPACE_USAGE

where snap_id in
(select snap_id
from dba_hist_snapshot
where to_char(BEGIN_INTERVAL_TIME) between '16/10/2011%18%' and '20/10/2011%18')
group by snap_id order by snap_id asc;
--Variable Declaretion
row_counter number;
db_used_size_1 number;
db_used_size_2 number;
snap_time timestamp;
total_change number;

FOR emp_rec IN c1
if mod(row_counter,2)=0
db_used_size_2:=emp_rec."used space";
db_used_size_1:=emp_rec."used space";
end if;
if row_counter != 1
execute immediate 'select begin_interval_time from dba_hist_snapshot where snap_id ='||emp_rec.snap_id into snap_time;
if db_used_size_2-db_used_size_1 !=0 and db_used_size_1 !=0 and db_used_size_2!=0
if mod(row_counter,2)!=0
dbms_output.put_line(substr(to_char(snap_time),1,instr(snap_time,',')-1)||' -> '
||to_char(db_used_size_1-db_used_size_2)||'MB'||'--> %'||to_char(round(100*(db_used_size_1-db_used_size_2)/db_used_size_1,2)));
dbms_output.put_line(substr(to_char(snap_time),1,instr(snap_time,',')-1)||' -> '||to_char(db_used_size_2-
db_used_size_1)||'MB'|| '--> %'||to_char(round(100*(db_used_size_2-db_used_size_1)/db_used_size_1,2)));
end if;

end if;
end if;
dbms_output.put_line('Total Change ' || to_char(total_change ||' MB' ));

Anonymous said...

oh my god earslan your query amazing it is like finding water in desert after one year , thank you very much i can find anything i want ,my life is easier then before


earslan said...

plsql above can be used with sysdate-"DAYS" with a little change in the following query.
(select snap_id
from dba_hist_snapshot
where to_char(BEGIN_INTERVAL_TIME) between '16/10/2011%18%' and '20/10/2011%18')

..change to..

(select snap_id
from dba_hist_snapshot
where BEGIN_INTERVAL_TIME > sysdate - 5 )

Tom said...

its just says..
PL/SQl procedure sucessfully completed..
How to get the report..?

Anonymous said...

how to get the report?
It just say:
PL/SQl procedure sucessfully completed..


@tom and @Anonymous said ...
i think you have not set the serveroutput variable.
Check as below

serveroutput OFF (by default it is OFF)

serveroutput ON

Now run the above scripts ...
Hope you got it and let me know if there is any doubt...

Good Luck :)


Anonymous said...


Thanks for sharing query to check growth!

Looks like there is an issue with Earslan query ..

select sum(round
**********Should Be******

as "used space",snap_id from DBA_HIST_TBSPC_SPACE_USAGE

Also, why the Neeraj's query - sum of AVG_INCREASE_MB is not matching with Earslans query output for "Total Change MB"?

Thanks in advance!


Anonymous said...

Great Job!!I found this after a huge search. Thanks.

Anonymous said...

useful script - you just need one more join: tsu.snap_id = ts.snap_id

Anonymous said...

That pl/sql is not working for me .It gives me

Error ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 36

I researched by commenting and uncommenting lines and got the error at

"execute immediate 'select begin_interval_time from dba_hist_snapshot where snap_id ='||emp_rec.snap_id into snap_time; "

because when I run cursor C1 in the database it returns a lot of rows .So I guess the error is obvious .

mukesh jha said...

Hi Neeraj, the script provided by u is simply awesome.... i have a question....can u pls tel me that the that the groth rate shown by "AVG_INCREAS_MB" column is yearly, monthly or weekly? I will grateful to have ur reply.... Thanks lot for ur script :)

mukesh jha said...

Hi Earlson.... I tried ur script but the output is something lik below which is i am not able to understand....could u pls suggest me and help me out in this pls...

-> -3.63MB--> %0
-> .19MB--> %0
-> 2.19MB--> %0
-> -11.19MB--> %0
-> 30.44MB--> %.01
-> -27.87MB--> %-.01
-> -.76MB--> %0
-> 2.38MB--> %0
-> -3.81MB--> %0
Total Change -9.43 MB


mukesh jha said...

Hi Earlson...
i tried ur script and got below output which is i am not able to understand...could u pls suggest me and help me out in this pls...
-> -3.63MB--> %0
-> .19MB--> %0
-> 2.19MB--> %0
-> -11.19MB--> %0
-> 30.44MB--> %.01
-> -27.87MB--> %-.01
-> -.76MB--> %0
-> 2.38MB--> %0
-> -3.81MB--> %0
Total Change -9.43 MB


syed said...

Hi, the script provided by u guys is simply awesome.

When i run the script it gives me an output as below.

It will be grate help and highly appreciated if you could please let me understand what exactly the result says.

Thanks & Regards,

syed said...

on giving the interval as

where to_char(BEGIN_INTERVAL_TIME) between '01/01/2014%18%' and '31/01/2014%18')

---- to fix the characters length i paste half of the result.

-> -84.81MB--> %-.01
-> .13MB--> %0
-> -23MB--> %0
-> .19MB--> %0
-> 126.43MB--> %.02
-> 527.69MB--> %.07
-> 1621.56MB--> %.2
-> 113.06MB--> %.01
-> -463.88MB--> %-.06
-> 152.51MB--> %.02
-> 2989.43MB--> %.37
Total Change 392286.49 MB

Anonymous said...

Hi Neeraj,

Do i need to change anything if the database is running under ASM

lee woo said...

Strength and growth come only through continuous effort and struggle. See the link below for more info.


Anonymous said...

Tried this but am getting output from one tablespace with null on the ave_increase