Saturday, January 10, 2015

Database Growth History and Forecast (10g, 11g)

For 10g, 11g tablespace growth history and forecast, you can use TablespaceGrowth History and Forecast (10g, 11g) script.
Starting Oracle 10G, Oracle records tablespaces usage (allocated, used etc.) in AWR which can be retrieved by querying the data dictionary view dba_hist_tbspc_space_usage. Using information in dba_hist_tbspc_space_usage, following script can be used to view the history of database usage and predict the expected growth for the future. Growth forecast is based on daily growth in the past.

Things to note:
1) This script is based on AWR. If your AWR retention period is 7 days, this script can only tell the growth history of last 7 days and predict based on last 7 days growth. I would recommend to change AWR retention to at least 35 days - this will also be more helpful in case of performance tuning situation as you will have a longer window of the past to look into for performance comparisons.
2) This script does not including TEMP and UNDO tablespaces while analyzing and forecasting
3) You may edit this scrip according to your requirement to forecast for a period  which suites your requirements. By default it will predict expected growth for next 30, 60 and 90 days.
4) Save this code in an sql script.
5) Log in as user SYS on SQLPLUS execute the script or copy and paste the following code.

##############################################
set serverout on
set verify off
set lines 200
set pages 2000
DECLARE
v_ts_id number;
not_in_awr EXCEPTION;
v_ts_block_size number;
v_begin_snap_id number;
v_end_snap_id number;
v_begin_snap_date date;
v_end_snap_date date;
v_numdays number;
v_count number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
v_db_begin_size number := 0;
v_db_end_size number := 0;
v_db_begin_allocated_space number := 0;
v_db_end_allocated_space number := 0;
v_db_growth number := 0;
cursor v_cur is select tablespace_name from dba_tablespaces where contents='PERMANENT';

BEGIN
FOR v_rec in v_cur
LOOP
BEGIN
v_ts_begin_allocated_space := 0;
v_ts_end_allocated_space := 0;
v_ts_begin_size := 0;
v_ts_end_size := 0;
SELECT ts# into v_ts_id FROM v$tablespace where name = v_rec.tablespace_name;
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_rec.tablespace_name;
select count(*) into v_count from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
IF v_count=0 THEN
RAISE not_in_awr;
END IF;
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;

IF UPPER(v_rec.tablespace_name)='SYSTEM' THEN
v_numdays := v_end_snap_date - v_begin_snap_date;
END IF;

SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_begin_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_end_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;

v_db_begin_allocated_space := v_db_begin_allocated_space + v_ts_begin_allocated_space;
v_db_end_allocated_space := v_db_end_allocated_space + v_ts_end_allocated_space;
v_db_begin_size := v_db_begin_size + v_ts_begin_size;
v_db_end_size := v_db_end_size + v_ts_end_size;
v_db_growth := v_db_end_size - v_db_begin_size;


END;
END LOOP;

DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_db_end_allocated_space||' MB'||' ('||round(v_db_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_db_end_size||' MB'||' ('||round(v_db_end_size/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||round(v_db_end_size/v_db_end_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space on '||v_begin_snap_date||': '||v_db_begin_allocated_space||' MB'||' ('||round(v_db_begin_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||v_db_end_allocated_space||' MB'||' ('||round(v_db_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||v_db_begin_size||' MB'||' ('||round(v_db_begin_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||v_db_end_size||' MB'||' ('||round(v_db_end_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('5) Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||': '||v_db_growth||' MB'||' ('||round(v_db_growth/1024,2)||' GB)');

IF (v_db_growth <= 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('No data growth was found for the Database');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||round(v_db_growth/v_numdays,2)||' MB'||' ('||round((v_db_growth/v_numdays)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Expected Growth');
DBMS_OUTPUT.PUT_LINE('===============');
DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '|| round((v_db_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_db_growth/v_numdays)*30)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '|| round((v_db_growth/v_numdays)*60,2)||' MB'||' ('||round(((v_db_growth/v_numdays)*60)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '|| round((v_db_growth/v_numdays)*90,2)||' MB'||' ('||round(((v_db_growth/v_numdays)*90)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\');
END IF;

EXCEPTION
WHEN NOT_IN_AWR THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('====================================================================================================================');
DBMS_OUTPUT.PUT_LINE('!!! ONE OR MORE TABLESPACES USAGE INFORMATION NOT FOUND IN AWR !!!');
DBMS_OUTPUT.PUT_LINE('Execute DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT, or wait for next AWR snapshot capture before executing this script');
DBMS_OUTPUT.PUT_LINE('====================================================================================================================');
END;
/

/##############################################

Please give your feedback if you find any difficulty in executing this script or if this script does not work for you.

Sample Output

Summary
========
1) Allocated Space: 42492 MB (41.5 GB)
2) Used Space: 30806.5 MB (30.08 GB)
3) Used Space Percentage: 72.5 %


History
========
1) Allocated Space on 07-DEC-14: 38776 MB (37.87 GB)
2) Current Allocated Space on 11-JAN-15: 42492 MB (41.5 GB)
3) Used Space on 07-DEC-14: 26445.89 MB (25.83 GB)
4) Current Used Space on 11-JAN-15: 30806.5 MB (30.08 GB)
5) Total growth during last 35 days between 07-DEC-14 and 11-JAN-15: 4360.61 MB (4.26 GB)
6) Per day growth during last 35 days: 124.59 MB (.12 GB)


Expected Growth
===============
1) Expected growth for next 30 days: 3737.67 MB (3.65 GB)
2) Expected growth for next 60 days: 7475.33 MB (7.3 GB)
3) Expected growth for next 90 days: 11213 MB (10.95 GB)


/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\

11 comments:

  1. Salman,

    I was going through the script which you have published and I have a requirement similar to same, where I need to fetch out the Daily Growth OF Database. But is it feasible to get a report which tend to reflect the trend of past one month from current day.

    Please respond on this. Incase you can help me with this. Response Awaited.

    BR,
    Sharad

    ReplyDelete
    Replies
    1. Daily Growth numbers -

      SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days

      , ts.tsname

      , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024*1024),2) ) size_GB

      , max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024*1024),2)) usedsize_GB

      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 ts.tsname IN ('&APPS_TS_TX_DATA') -- replace this with what you need

      GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname

      ORDER BY ts.tsname, days;

      Delete
  2. Hi Sharad,
    As I already explained, this script can calculate base don the retention setting of AWR. If your AWR snapshot retention setting is 30 days, the calculation/trend will be based on 30 days.
    To display DB size for each day, you would need to modify the script. I don't have much time right now to modify script for you, please accept my apology.

    ReplyDelete
  3. Hi Salman, I keep on running into " ONE OR MORE TABLESPACES USAGE INFORMATION NOT FOUND IN AWR" when I have checked DBA_HIST_TBSPC_SPACE_USAGE to have historical data. I also have 4 months of AWR data. Can you tell me what I'm missing here?

    ReplyDelete
  4. Hi rhanwel,
    The only reason I have faced for this error while running this script is that DBA_HIST_TBSPC_SPACE_USAGE does not contain data about atleast one tablespace. Can you query "select ts# from v$tablespace order by 1;" and then "select distinct tablespace_id from DBA_HIST_TBSPC_SPACE_USAGE order by 1 " and then match output of both to see if each tablespace id returned by 1st query exists in the output of second query.

    Salman

    ReplyDelete
  5. Hi Salman, it returned all except for two tablespace.

    ReplyDelete
    Replies
    1. Hi Rhanwel, sorry for a very late reply. Can you please email me the output of script when you run including the error message you are facing. Please email me at salmanucit@hotmail.com

      Salman

      Delete
  6. Hi Salman,

    I need a script for 10g/11g like Script for All Tablespace in All containers(Except UNDO and TEMP) in 12c.
    Is it possible? if possible pls provide me. It is very nice of you.

    Regards
    Prabhu

    ReplyDelete
    Replies
    1. Hi Prabhakar,
      I see your comment also on my article http://salmandba.blogspot.com/2015/07/tablespace-growth-history-and-forecast.html, and this article demonstrate 2 scripts. Second script is for 12c, for all tablespaces in all containers (except TEMP and UNDO)
      Salman

      Delete
  7. Hi Salman,

    "I want oracle script that so database growth by month."
    When I run on production database below query it show only till Feb 2017 database growth. I also check snapshot retention but every thing is fine.

    SELECT TO_CHAR(month, 'YYYY Month') AS "Month",
    growth AS "Growth in MB"
    FROM (SELECT TRUNC(creation_time, 'MM') AS month,
    SUM(bytes)/1024/1024 AS growth
    FROM sys.v$datafile
    WHERE creation_time > SYSDATE-365
    GROUP BY TRUNC(creation_time, 'MM')
    ORDER BY 1);

    Please reply ASAP. I need to create report for db growth.

    ReplyDelete
    Replies
    1. You are querying v$datafile that would only return growth since the creation of each datafile. This query is not going to help you. To use my script, you need to have AWR retention period that should be as long as much you want to go back in history. If you want to analyze last one years growth, your AWR retention should be at least 365 days. Follow following article to set your AWR interval and growth

      http://salmandba.blogspot.com/2015/08/changing-awr-snapshot-retention-and.html

      Delete