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 : 

TABLESPACE_NAME                CUR_USED_SIZE_MB           AVG_INCREAS_MB
--------------------             -----------------------      ------------------------
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     :-)


22 comments:

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.*/
declare
--Cursor Declaretion
CURSOR c1
IS
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;


begin
row_counter:=0;
total_change:=0;
db_used_size_2:=0;
db_used_size_1:=0;
FOR emp_rec IN c1
LOOP
row_counter:=row_counter+1;
if mod(row_counter,2)=0
then
db_used_size_2:=emp_rec."used space";
else
db_used_size_1:=emp_rec."used space";
end if;
if row_counter != 1
then
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
then
if mod(row_counter,2)!=0
then
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)));
total_change:=total_change+(db_used_size_1-db_used_size_2);
else
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)));
total_change:=total_change+(db_used_size_2-db_used_size_1);
end if;

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

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

defaultDBA

earslan said...

plsql above can be used with sysdate-"DAYS" with a little change in the following query.
Ex:
(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..

NEERAJ VISHEN said...

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

SQL> show SERVEROUTPUT
serveroutput OFF (by default it is OFF)

SQL> set SERVEROUTPUT on
SQL> show SERVEROUTPUT
serveroutput ON

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

Good Luck :)


--Neeraj

Anonymous said...

Hi,

Thanks for sharing query to check growth!

Looks like there is an issue with Earslan query ..


select sum(round
*********issue*********
((tablespace_usedsize*8*1024)/1024/1024,2))
**********Should Be******
((tablespace_usedsize*8)/1024/1024,2))

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!

Manish

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


Regards,
Mukesh

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


Regards,
Mukesh

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

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.


#growth
www.ufgop.org

Antonio Morales said...

hi.

The query have too much cost. Perhaps is possible replace the dba_hist_tablespace_stat for v$tablespace

select b.name 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.name,
used_size_mb,
used_size_mb - LAG (used_size_mb,5) OVER ( PARTITION BY a.name ORDER BY a.name,a.days) inc_used_size_mb
from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days,
ts.name ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
from dba_hist_snapshot sp,
dba_hist_tbspc_space_usage tsu,
v$tablespace ts,
dba_tablespaces dt
where (sp.begin_interval_time > sysdate-30 and dt.contents='PERMANENT')
and tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.name = dt.tablespace_name
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'),
ts.name
ORDER BY ts.name, days ) a
) b GROUP BY b.name ORDER BY b.name
/

Regards

siva said...

Hi Neeraj,

Thanks for your post...it was really useful for me.

TABLESPACE_NAME CUR_USED_SIZE_MB AVG_INCREAS_MB
SYSAUX 9,486 168
SYSTEM 8,491 9
TBLSPC_ARCH 1,537 0
TBLSPC_DATA1 1,18,407 -4,408
TBLSPC_DATA2 3,259 0
TBLSPC_DATA3 31,475 0
TBLSPC_DSSDEV 3,708 0
TBLSPC_EVENT 3,719 0
TBLSPC_INDX1 15,194 16
UNDO1 7 0
UNDOTBS1 1,023 0
USERS 1 0

I have one doubt above output what is meaning of -4,408(negative values)...can u reply me pls...

Anonymous said...

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