Sunday, February 9, 2014

Tablespace Growth History and Forecast for 10g and 11g

Finding space usage of tablespaces and database is what many DBAs want to find. In this article I will explain how to find out space usage history and forecasting future growth of tablespaces. For 10g and 11g database growth history and forecast see script Database Growth History and Forecast for 10g and 11g.
For 12c and above; Tablespace and Database growth history and forecast, please use following scripts
Tablespace Growth History and Forecast for 12c and Above.
Database Growth History and Forecast for 12c and Above.
For segments space usage history and forecast, see this document
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. Following scripts can be used to view the history of tablespace(s) 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 from the past to look into for performance comparisons.
2) 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.
3) Save this code in an sql script.
4) Log in as user SYS on SQLPLUS and execute the script or copy and paste the following code. You will be prompted for the tablespace name

Script for Single Tablespace

##############################################
set serverout on
set verify off
set lines 200
set pages 2000
DECLARE
v_ts_id number;
not_in_awr EXCEPTION;
v_ts_name varchar2(200) := UPPER('&Tablespace_Name');
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_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_count number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
BEGIN
SELECT ts# into v_ts_id FROM v$tablespace where name = v_ts_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 block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_ts_name;
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;
v_numdays := v_end_snap_date - v_begin_snap_date;

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_ts_growth := v_ts_end_size - v_ts_begin_size;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||round(v_ts_end_size/v_ts_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_ts_begin_allocated_space||' MB'||' ('||round(v_ts_begin_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||v_ts_begin_size||' MB'||' ('||round(v_ts_begin_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||v_ts_end_size||' MB'||' ('||round(v_ts_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_ts_growth||' MB'||' ('||round(v_ts_growth/1024,2)||' GB)');
IF (v_ts_growth <= 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! NO DATA GROWTH WAS FOUND FOR TABLESPCE '||V_TS_NAME||' !!!');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||round(v_ts_growth/v_numdays,2)||' MB'||' ('||round((v_ts_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_ts_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '|| round((v_ts_growth/v_numdays)*60,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*60)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '|| round((v_ts_growth/v_numdays)*90,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*90)/1024,2)||' GB)');
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! TABLESPACE DOES NOT EXIST !!!');
WHEN NOT_IN_AWR THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! TABLESPACE USAGE INFORMATION NOT FOUND IN AWR !!!');

END;
/
##############################################

Sample Output

Enter value for tablespace_name: TEST


Tablespace Block Size: 8192
---------------------------


Summary
========
1) Allocated Space: 2048 MB (2 GB)
2) Used Space: 1558.44 MB (1.52 GB)
3) Used Space Percentage: 76.1 %


History
========
1) Allocated Space on 06-DEC-14: 2048 MB (2 GB)
2) Current Allocated Space on 10-JAN-15: 2048 MB (2 GB)
3) Used Space on 06-DEC-14: 1273 MB (1.24 GB)
4) Current Used Space on 10-JAN-15: 1558.44 MB (1.52 GB)
5) Total growth during last 35 days between 06-DEC-14 and 10-JAN-15: 285.44 MB (.28 GB)
6) Per day growth during last 35 days: 8.16 MB (.01 GB)


Expected Growth
===============
1) Expected growth for next 30 days: 244.66 MB (.24 GB)
2) Expected growth for next 60 days: 489.33 MB (.48 GB)
3) Expected growth for next 90 days: 733.99 MB (.72 GB)

PL/SQL procedure successfully completed.


/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/

Script for All Tablespace (Except UNDO and TEMP)
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_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_count number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
cursor v_cur is select tablespace_name from dba_tablespaces where contents='PERMANENT';

BEGIN
FOR v_rec in v_cur
LOOP
BEGIN
SELECT ts# into v_ts_id FROM v$tablespace where 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 block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_rec.tablespace_name;
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;
v_numdays := v_end_snap_date - v_begin_snap_date;

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_ts_growth := v_ts_end_size - v_ts_begin_size;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(v_rec.tablespace_name||' Tablespace');
DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||round(v_ts_end_size/v_ts_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_ts_begin_allocated_space||' MB'||' ('||round(v_ts_begin_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||v_ts_begin_size||' MB'||' ('||round(v_ts_begin_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||v_ts_end_size||' MB'||' ('||round(v_ts_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_ts_growth||' MB'||' ('||round(v_ts_growth/1024,2)||' GB)');
IF (v_ts_growth <= 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! NO DATA GROWTH WAS FOUND FOR TABLESPACE '||v_rec.tablespace_name||' !!!');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||round(v_ts_growth/v_numdays,2)||' MB'||' ('||round((v_ts_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_ts_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '|| round((v_ts_growth/v_numdays)*60,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*60)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '|| round((v_ts_growth/v_numdays)*90,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*90)/1024,2)||' GB)');
END IF;
DBMS_OUTPUT.PUT_LINE('/\/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\/\');

EXCEPTION
WHEN NOT_IN_AWR THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(v_rec.tablespace_name||' Tablespace');
DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! TABLESPACE USAGE INFORMATION NOT FOUND IN AWR !!!');
DBMS_OUTPUT.PUT_LINE('/\/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\/\');
NULL;
END;
END LOOP;
END;
/

Please give your feedback if you face any difficulty in executing these scripts or if these scripts do not work for you.

36 comments:

  1. Hi Salman

    How do you change this to do all tablespace rather than just the one ?

    Thanks

    ReplyDelete
  2. Hi Joel,
    Thanks for asking and my apology for late reply. I have updated my post above to add another script which would show you growth history for all tablespaces except UNDO and TEMP.

    ReplyDelete
  3. Hi,

    Is it possible to compare tablespace growth on daily basis and compare with existing data.

    Let's say sometime all of sudden some heavy workload came and tablespace increased abnormally then we should come to know.

    Regards,
    Moiz

    ReplyDelete
    Replies
    1. Hi Moiz,
      What you are actually asking for is an alert mechanism. I would recommend you to setup OEM Cloud/Grid/database control for alerting you if tablespace reaches warning or critical thresholds.
      Comparison of size of a tablespace between 2 given days is a special requirement which even OEM won't do for you. I believe you can write a simple PL/SQL procedure to compare size of tablespace on a given date with another given date and send you an email and you can call this PL/SQL procedure through a scheduler job.

      Delete
  4. Hi Salman,

    I am getting below error. can you please help ?
    +++++++++++++++++++++++++
    DECLARE
    *
    ERROR at line 1:
    ORA-01422: exact fetch returns more than requested number of rows
    ORA-06512: at line 22
    ++++++++++++++++++++++++++++++
    Thanks in advance.

    Regards,
    J

    ReplyDelete
  5. Are you talking about first script or second script? If first script, is it returning error for all of your tablespaces?
    For first script, for SELECT statement 4 and 5, can you replace round(tablespace_size*v_ts_block_size/1024/1024,2) with round(max(tablespace_size)*v_ts_block_size/1024/1024,2)
    And for SELECT 6 and 7, replace round(tablespace_usedsize*v_ts_block_size/1024/1024,2) with round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2)

    ReplyDelete
    Replies
    1. Thanks. Its for the first script. Max did the trick. its working now. Very handy script. Appreciate your time and help on this.

      Thanks,
      J

      Delete
  6. Hi Jothi,
    I am glad it worked for you. I will update my sctipts also. Thanks for your feedback.

    ReplyDelete
    Replies
    1. hello sir, can you please tell me what changes needs to be done to check the growth for last 30 days. here it is giving for last 8 days. sridharkumar.sahu@yahoo.com

      Delete
    2. Hi Sridhar,
      As I have mentioned in my post that this script extracts information from AWR which retains snapshots history of 8 days by default and that is the reason that it will show you information based on last 8 days only. If you want to check growth for 30 days, first you would need to change your retention policy of AWR snapshot. Please see following post for how to do this.
      http://salmandba.blogspot.sg/2015/08/changing-awr-snapshot-retention-and.html

      Delete
    3. Hello Salman,

      Script is saying that, nothing found in AWR report.

      Will you please help on this.

      Regards,
      Rohith

      Delete
  7. it's really helpful , grate works

    ReplyDelete
  8. So awesome, thank you so much Salman. Very helpful.

    ReplyDelete
  9. Hi Salman, Very Nice script. Thanks a lot for sharing it.

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. Thanks a lot for useful query. I have one question:- Can we extract table space data history by daily basis by this query.

    ReplyDelete
    Replies
    1. Hi Sonu,
      Use following query by manually providing tablespace ID and correct block size in calculation (I used 8192 block size size). This query should list the maximum size/used size of your tablespace for each day.


      select max(TABLESPACE_SIZE)*8192/1024/1024,max(TABLESPACE_USEDSIZE)*8192/1024/1024, trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')) from dba_hist_tbspc_space_usage where tablespace_id=1 group by trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')) order by 3;

      Delete
  12. Hi Salman,

    can you create a script like this but with an HTML output?

    Thanks,
    neljan

    ReplyDelete
  13. Hi Salman,

    can you create a script like this but with an HTML output?

    Thanks,
    neljan

    ReplyDelete
    Replies
    1. Hi,
      It would need a lot of time and effort :). For now, can you use "SET MARKUP HTML ON" on SQLPLUs, and see if it helps.

      Delete
  14. Its a very good script Salman . Appreciate your work . Do you have any script to monitor Full database growth ?

    ReplyDelete
    Replies
    1. Hi Siddharths,
      Please see following for full database growth
      http://salmandba.blogspot.sg/2015/01/database-growth-history-and-forecast.html

      Salman

      Delete
    2. Thanks Salman . For this tablespace report , I need to get top 10 tablespaces with the highest growth prediction. Is there anything you prepared or can you ?

      Thanks
      Sid

      Delete
    3. Hi Siddhartha,
      Unfortunately I don't have any script that could fulfill your requirement, but I think you can just slightly modify this script. create a global temporary table with fields ,tablespace_name, growth_30days, growth_60days, growth_90 days. Then insert into this global temporary table the calculated growth "DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '|| round((v_ts_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB)');" for each period (30 days, 60 days, 90 days).
      Then after "END LOOP;", you write a "SELECT INTO" statement to query global temporary table (order by growth rate) and insert into pl/sql variables, and then user dbms_output to display.

      Delete
  15. Hi Salman,

    The space allocated shown here is based on the current allocated size, can you change the script so that it can take the auto-extend on into consideration.

    vijay

    ReplyDelete
    Replies
    1. Hi Vijay,
      This script is related to finding out space usage history, and doing forecast for future, based on this historical usage trend found in the AWR repository. Autoextend for a datafile is not related to either of these.

      Salman

      Delete
  16. Excellent script.
    Thanks for sharing.

    ReplyDelete
  17. Hello sir!
    I want that report in a table format. what should i do?

    ReplyDelete
    Replies
    1. Hello Abhijit,
      Sorry for late reply. You would need to modify the script. Create a table use "CREATE TABLE TAB_NAME AS " add select statement that is part of above script to get the tablespace info. Then instead of DBMS_OUTPUT, use INSERT INTO TAB_NAME SELECT statement to insert the fetch data directly into the table. It is not as simple as I explained, but you can give it a try.

      Delete
  18. Hi Salman,

    I wanted total db growth for 3 months, 6 months, 1 year..I need to sum each table space growth for 3 months, 6 months and 1 year. I don't want even history also. I am adding every day table spaces for each db. I have almost 10 to 15 dbs. it is very difficult for me. if you can give this output, it is very nice of you.

    Hope you are clear with my reqmt. if not pls do reply

    Vasista

    ReplyDelete
  19. Hi Prabhakar,
    Please implement OEM in your environment to maintain history, or use manual method to store this information into tables. This script is based on AWR and can only manipulate data available in AWR.

    ReplyDelete
  20. I dont want prompt of tablespace_name , i need to include ins a script like tbsgrowth.sh and input tablespace _name based on output of tablespace_name of other script , it is possible can you help me

    ReplyDelete
    Replies
    1. You can see above that there a separate code it listed where you don't need to supply name of the tablespace, and code will provide output for all tablespaces.
      In order to use as a shell script, use following format. After that you should be able to execute code in a shell script


      sqlplus /nolog << EOF
      CONNECT system/PASSWORD



      EXIT;
      EOF

      Delete

Popular Posts - All Times