Oracle database growth trends





Sometimes is useful to have a database growth estimation in order to avoid some issues or to be a proactive DBA, or maybe your boss wants to know how much money is needed to add some space to oracle database during the next year. In such situations is good to know dbms_space.OBJECT_GROWTH_TREND procedure and dba_hist_tbspc_space_usage view.


With dbms_space.OBJECT_GROWTH_TREND you will get an output like that, in this example i´m selecting MYTABLE table of MYOWNER owner grown trends, as you can see QUALITY column is equal to PROJECTED, this means that the value is in the future, and this is a grow prevision. 
SQL> set linesize 190
col TIMEPOINT for a40
select TIMEPOINT,SPACE_USAGE/1024/1024 as SPACE_USAGE_MB, SPACE_ALLOC/1024/1024 as SPACE_ALLOC_MB, QUALITY  from table(dbms_space.OBJECT_GROWTH_TREND ('MYOWNER','MYTABLE','TABLE')); SQL> SQL>

TIMEPOINT                                SPACE_USAGE_MB SPACE_ALLOC_MB QUALITY
---------------------------------------- -------------- -------------- --------------------
13-SEP-17 17.43.03.744031                    11394.6738          12460 INTERPOLATED
14-SEP-17 17.43.03.744031                    11396.2744          12460 GOOD
15-SEP-17 17.43.03.744031                    11438.8162          12460 GOOD
16-SEP-17 17.43.03.744031                    11439.6138          12460 GOOD
17-SEP-17 17.43.03.744031                    11446.3293          12588 GOOD
18-SEP-17 17.43.03.744031                    11446.2662          12588 GOOD
19-SEP-17 17.43.03.744031                    11447.3238          12588 GOOD
20-SEP-17 17.43.03.744031                    11448.2442          12588 GOOD
21-SEP-17 17.43.03.744031                    11448.8322          12588 GOOD
22-SEP-17 17.43.03.744031                    11450.3334          12588 GOOD
23-SEP-17 17.43.03.744031                    11450.0554          12588 GOOD
24-SEP-17 17.43.03.744031                    11449.9482          12588 GOOD
25-SEP-17 17.43.03.744031                    11452.3591          12588 GOOD
26-SEP-17 17.43.03.744031                      11453.85          12588 GOOD
27-SEP-17 17.43.03.744031                    11453.9222          12588 GOOD
28-SEP-17 17.43.03.744031                    11454.5788          12588 GOOD
29-SEP-17 17.43.03.744031                    11458.7554          12588 GOOD
30-SEP-17 17.43.03.744031                    11460.2722          12588 INTERPOLATED
01-OCT-17 17.43.03.744031                    11458.3205          12588 GOOD
02-OCT-17 17.43.03.744031                    11458.8366          12588 GOOD
03-OCT-17 17.43.03.744031                    11461.0524          12588 GOOD
04-OCT-17 17.43.03.744031                    11462.9729          12588 GOOD
05-OCT-17 17.43.03.744031                    11464.4363          12588 GOOD
06-OCT-17 17.43.03.744031                    11465.4451          12588 GOOD
07-OCT-17 17.43.03.744031                    11465.1594          12588 GOOD
08-OCT-17 17.43.03.744031                    11465.1744          12588 GOOD
09-OCT-17 17.43.03.744031                    11465.1517          12588 GOOD
10-OCT-17 17.43.03.744031                    11468.2819          12588 GOOD
11-OCT-17 17.43.03.744031                     11470.483          12588 GOOD
12-OCT-17 17.43.03.744031                    11472.2403          12588 GOOD
13-OCT-17 17.43.03.744031                    11473.4019          12588 GOOD
14-OCT-17 17.43.03.744031                    11489.9381     12663.2516 PROJECTED
15-OCT-17 17.43.03.744031                    11491.5533     12666.0387 PROJECTED
16-OCT-17 17.43.03.744031                    11493.1684     12668.8258 PROJECTED
17-OCT-17 17.43.03.744031                    11494.7835     12671.6129 PROJECTED
18-OCT-17 17.43.03.744031                    11496.3987        12674.4 PROJECTED

36 rows selected.

But this is the basic syntax for dbms_space.OBJECT_GROWTH_TREND, this method only give us 5 prevision days after present time, so if we want a growth forecasts for nexts 90 days we should to add this tree parameters to our function:

start_time-->Statistics generated after this time will be used in generating the growth trend
end_time-->Statistics generated until this time will be used in generating the growth trend
interval-->The interval at which to sample

In this case i setup start_time =>sysdate-30, end_time=>sysdate+90 and interval=>to_dsinterval('7 00:00:00') (1 week)
SQL> set linesize 190
col TIMEPOINT for a40
SQL> SQL> select TIMEPOINT,SPACE_USAGE/1024/1024 as SPACE_USAGE_MB, SPACE_ALLOC/1024/1024 as SPACE_ALLOC_MB, QUALITY  from table(dbms_space.OBJECT_GROWTH_TREND (object_owner=>'MYOWNER',object_name
  2  =>'MYTABLE', object_type=>'TABLE',start_time =>sysdate-30, end_time=>sysdate+90,interval=>to_dsinterval('7 00:00:00') ));

TIMEPOINT                                SPACE_USAGE_MB SPACE_ALLOC_MB QUALITY
---------------------------------------- -------------- -------------- --------------------
13-SEP-17 18.02.50.000000                    11394.7762          12460 GOOD
20-SEP-17 18.02.50.000000                    11448.2497          12588 GOOD
27-SEP-17 18.02.50.000000                    11453.8861          12588 GOOD
04-OCT-17 18.02.50.000000                    11462.9734          12588 GOOD
11-OCT-17 18.02.50.000000                    11470.4158          12588 GOOD
18-OCT-17 18.02.50.000000                    11514.6508        12690.4 PROJECTED
25-OCT-17 18.02.50.000000                    11531.2511          12716 PROJECTED
01-NOV-17 18.02.50.000000                    11547.8514        12741.6 PROJECTED
08-NOV-17 18.02.50.000000                    11564.4517        12767.2 PROJECTED
15-NOV-17 18.02.50.000000                     11581.052        12792.8 PROJECTED
22-NOV-17 18.02.50.000000                    11597.6523        12818.4 PROJECTED
29-NOV-17 18.02.50.000000                    11614.2526          12844 PROJECTED
06-DEC-17 18.02.50.000000                    11630.8529        12869.6 PROJECTED
13-DEC-17 18.02.50.000000                    11647.4532        12895.2 PROJECTED
20-DEC-17 18.02.50.000000                    11664.0534        12920.8 PROJECTED
27-DEC-17 18.02.50.000000                    11680.6537        12946.4 PROJECTED
03-JAN-18 18.02.50.000000                     11697.254          12972 PROJECTED
10-JAN-18 18.02.50.000000                    11713.8543        12997.6 PROJECTED

18 rows selected.

With this method is good for an object or few object estimation, but if you have to estimate all database growth or an entire tablespace growth, i recommend you: dba_hist_tbspc_space_usage method:

At the other hand, we have dba_hist_tbspc_space_usage view... this view contains historical occupation data for tablespaces, so you can user linear regression to estimate future tablespace occupation... you can see a script in this article database growth based on linear

Comments

  1. karthikeyanDBA29 September 2019 at 03:44

    am not able to open the link highlighted ..Could you please share the script /artical

    ReplyDelete
  2. Abhi$hek5 October 2020 at 09:22

    Not able to open the link. could you please share the access to the blog

    ReplyDelete
  3. MoostiQ11 December 2020 at 16:09

    Nice article. Can you give me access to the next one "database growth based on linear" please ?

    ReplyDelete

Post a Comment

Popular posts from this blog

Oracle Historical Session Information with ASH >10g

Image
In Oracle 10.2 and above with ASH is very useful to see the number of sessions active and connected to a database over time: You can use this 2 views to obtain this information: v$sysmetric_history --> This is a sort term view, that contains data of last hour by intervals of minutes dba_hist_sysmetric_summary -->This is a long term view, that contains data of last month by intervals of 1 hour. v$sysmetric_history and dba_hist_sysmetric_summary contains information a lot of different metrics information, so the first thing we need to do is to filter metric type. With this query you can select a list of all metric types: set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history group by METRIC_ID, METRIC_NAME ; for example, if you are looking for metrics related with connected sessions to database, you can do: SQL> set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history where lo
Read more

Purging and archiving Oracle alert.log and listener.log

As i said in " Purging Oracle traces with ADRCI " ADRCI does not work properly for alert.log and listener.log, so in this article i´m going to study who to do this tasks externally, with unix tools. 1-With logrotate Linux-command. I think this is the best option if you have an Oracle linux because it can be setup easily... Put this lines in a config file but modifing your alert.log and listener.log paths # alert log /oracle/db/diag/rdbms/orcl/orcl/trace/alert_orcl.log { monthly rotate 13 notifempty missingok copytruncate nocreate } # listener log /oracle/db/diag/tnslsnr/wcp12cr2/listener/trace/listener.log { weekly rotate 53 notifempty missingok copytruncate nocreate } i put them into alert_logrotate.conf as you can see: [oracle@wcp12cr2 Documents]$ ls -ltr alert_logrotate.conf -rw-r--r--. 1 oracle oinstall 593 Oct 5 03:08 alert_logrotate.conf [oracle@wcp12cr2 Documents]$ cat alert_logrotate.conf # see "man logrotate" for details # rotate log files
Read more

Check sessions and processes limits in Oracle

Oracle database has a gv$resource_limit view to check if any database resource reach his connection limit: set linesize 190 set pages 200 select * from gv$resource_limit; INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE ---------- ------------------------------ ------------------- --------------- --------------- ------------ 1 processes 44 300 300 300 1 sessions 52 215 512 512 1 enqueue_locks 30 295 6480 6480 1 enqueue_resources 24 165 2472 UNLIMITED 1 ges_procs 0 0 0 0 1 ges_ress 0 0
Read more