TABLESPACE CAPACITY PLANNING REPORT

    Oct 21, 2017 4:18:00 PM by JP Vijaykumar

    I need to run capacity planning report in my project.

    Requirements:

    1. Predict db growth for 3 months, 6 months and one year.
    2. Display message, if the available free space in each tablespace is less than it's projected 3 months growth.
    3. Email the generated report to the dba group for further action.

    Issues:

    1. You need to increase the snapshot retention in the db to atleast 3 months.
    2. If the snapshot retention is too low, the predictions may have huge variations to expected actual growth of db.
    3. If the available data in the dba_hist_tbsp_space_usage is scanty, the projections may not be near accurate.
    4. If for some reason, the snapshots generation stopped, this report may not work as expected.

    Usage:

    Pl run this report in a lower environment and make necessary changes as applicable in your case. We are running Oracle 11G on linux OS. Pls modify the email command as applicable to your OS. This script work for Oracle rdbms, and was tested in version 11G. If this script is to be used in a different version of Oracle rdbms, pls make necessary modifications to run. As per you convenience/requirement, the flagged message "ADD SPACE" can be changed to display a meaningful message.

    Owing to many limitations, there will be a fair chance of variation in the projected growth of the db. And yet, I will have a starting point as to how much diskspace, I need to procure for my db's future growth. Few deviations this way that way are ok, as I may not be caught off the guard with huge diskspace requirements.

    connect system/veeksha
    set linesize 200 pagesize 100 colsep "," echo off feedback off timing off
    column tablespace_name format a20
    column avg_growth_per_day_gb format 9999.99 heading "AVG GROWTH|PER DAY GB"
    column projected_growth_for_3mths_gb format 9999.99 heading "PROJECTED|GROWTH|FOR 3 MONTHS|GB"
    column projected_growth_for_6mths_gb format 9999.99 heading "PROJECTED|GROWTH|FOR 6 MONTHS|GB"
    column projected_growth_for_1yr_gb format 9999.99 heading "PROJECTED|GROWTH|FOR ONE YEAR|GB"
    column msg format a15 heading "ACTION|TO BE TAKEN"
    with t1 as (
    select ss.run_time,ts.name,round(su.tablespace_size*dt.block_size/1024/1024/1024,2) alloc_size_gb,
    round(su.tablespace_usedsize*dt.block_size/1024/1024/1024,2) used_size_gb
    from
    dba_hist_tbspc_space_usage su,
    (select trunc(BEGIN_INTERVAL_TIME) run_time,max(snap_id) snap_id from dba_hist_snapshot
    group by trunc(BEGIN_INTERVAL_TIME) ) ss,
    v$tablespace ts,
    dba_tablespaces dt
    where su.snap_id = ss.snap_id
    and su.tablespace_id = ts.ts#
    and ts.name NOT LIKE '%TEMP%'
    and ts.name NOT LIKE '%UNDO%'
    and ts.name = dt.tablespace_name order by 2,1),
    t2 as (
    select e.run_time,e.name,e.used_size_gb,e.used_size_gb - b.used_size_gb growth
    from t1 e, t1 b
    where e.name = b.name and e.run_time = b.run_time +1),
    t3 as (
    select --fre.tablespace_name, fre.alloc,fre.used,
    tsz.name,
    tsz.alloc_size_gb,tsz.used_size_gb,ave.avg_growth_per_day_gb,ave.avg_growth_per_day_gb*90 projected_growth_for_3mths_gb
    from
    (select name,max(alloc_size_gb) alloc_size_gb, max(used_size_gb) used_size_gb from t1 group by name) tsz,
    (select name,round(avg(growth),2) avg_growth_per_day_gb from t2 group by name) ave
    where tsz.name = ave.name)
    select t4.tablespace_name,t4.alloc alloc_sz_gb,t4.used used_sz_gb,
    --t3.alloc_size_gb,t3.used_size_gb,
    t3.avg_growth_per_day_gb,t3.projected_growth_for_3mths_gb,t4.free free_sz_gb,
    case when t4.free < nvl(projected_growth_for_3mths_gb,0) then 'ADD SPACE' end MSG,
    projected_growth_for_3mths_gb*2 projected_growth_for_6mths_gb , projected_growth_for_3mths_gb*4 projected_growth_for_1yr_gb
    from t3,
    (select a.tablespace_name,
    round(a.bytes/1024/1024/1024,2) alloc,
    round(b.bytes/1024/1024/1024,2) used,
    round(c.bytes/1024/1024/1024,2) free
    from sys.sm$ts_avail a,
    sys.sm$ts_used b,
    sys.sm$ts_free c
    where a.tablespace_name = b.tablespace_name(+)
    and a.tablespace_name = c.tablespace_name(+)) t4
    where t4.tablespace_name = t3.name(+)
    order by 1;
    spool capacity_planning.csv
    /
    spool off
    prompt "!ls -1tr *csv|tail -1|awk '{print "echo|mail -s #"$1" from $ORACLE_SID# -a "$1" #orcldba@orclprod.net#"}'|sed 's/#/"/g'|ksh"

    Happy Scripting.

    References:
    http://www.toadworld.com/platforms/oracle/w/wiki/10837.tablespace-growth-report.aspx
    http://www.databasejournal.com/features/oracle/article.php/3673616

    Modified Nov 22nd 2017

    Tags: Oracle

    JP Vijaykumar

    Written by JP Vijaykumar