Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Manish.

Asked: April 20, 2018 - 4:03 pm UTC

Last updated: May 07, 2018 - 4:36 am UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi,

As part of capacity planning, customer is asking schema table size growth per year for last 10 years. Is there any way to get it?

I tried the below query but through this we are not able to get size per year for last 10 years.

SELECT ds.tablespace_name
,ds.segment_name
,dobj.object_type
,ROUND(SUM(dhss.space_used_delta) / 1024 / 1024 ,2) "Growth (MB)"
FROM dba_hist_snapshot snpshot
,dba_hist_seg_stat dhss
,dba_objects dobj
,dba_segments ds
WHERE begin_interval_time > TRUNC(SYSDATE)-360
AND snpshot.snap_id = dhss.snap_id
AND dobj.object_id = dhss.obj#
AND dobj.owner = ds.owner
AND dobj.object_name = ds.segment_name
AND ds.owner ='&owner' AND ds.SEGMENT_NAME IN ('&table_name') AND dobj.object_type='TABLE'
GROUP BY ds.tablespace_name,ds.segment_name,dobj.object_type
ORDER BY 3 ASC



Regards,
Manish

and Connor said...

You are relying on your AWR settings there for the amount of history kept, and by default, that will be (a lot) smaller than 10 years. For example, on my system

SQL> select  *from DBA_HIST_WR_CONTROL
  2  @pr
==============================
DBID                          : 872342268
SNAP_INTERVAL                 : +00000 01:00:00.0
RETENTION                     : +00008 00:00:00.0 <============
TOPNSQL                       : DEFAULT
CON_ID                        : 0

PL/SQL procedure successfully completed.


SQL> select min(BEGIN_INTERVAL_TIME) from dba_hist_snapshot;

MIN(BEGIN_INTERVAL_TIME)
----------------------------------------------------------------
23-APR-18 02.26.03.000 AM


Unless you have set your retention to 10 years, or have captured it some other way, then it won't be available.

But I would also push back and the tell the customer it is somewhat of a nonsensical request in my opinion. So much changes about businesses etc in 10 years that I don't think it is a useful metric anyway.

For example, looking back 10 years, my starting point for smartphone ownership is approximately zero, with insane growth for about 7 years. But in the last 3 years it has slowed a lot (simply because everyone now has got one).

More data is not necessarily better data.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, May 02, 2018 - 9:10 am UTC

I have created these two tables and launch every day a crontab to insert the number of rows from stats tables.

CREATE TABLE HIST_OWNER_TABLES_ROWS
( FECHA DATE NOT NULL,
OWNER VARCHAR2(30 CHAR) NOT NULL,
TABLE_NAME VARCHAR2(30 CHAR) NOT NULL,
NUM_ROWS NUMBER NOT NULL,
LAST_ANALYZED DATE,
CONSTRAINT HIST_OWNER_TABLES_ROWS_PK PRIMARY KEY (FECHA, OWNER, TABLE_NAME));

CREATE TABLE HIST_OWNER_SEGMENTS_BYTES
( FECHA DATE NOT NULL,
OWNER VARCHAR2(30 CHAR) NOT NULL,
TABLE_NAME VARCHAR2(30 CHAR) NOT NULL,
BYTES NUMBER NOT NULL ,
CONSTRAINT HIST_OWNER_SEGMENT_BYTES_PK PRIMARY KEY (FECHA, OWNER, TABLE_NAME));

---------------------------------------

insert into hist_owner_tables_rows
select sysdate,owner,table_name,decode(num_rows,null,0,num_rows),LAST_ANALYZED
from dba_tables
where last_analyzed is not null;


commit;

insert into hist_owner_segments_bytes
select sysdate,owner, segment_name,sum(bytes) from dba_segments
where segment_type='TABLE'
group by owner, segment_name;

commit;

A reader, May 04, 2018 - 5:14 am UTC

Is following script is right or wrong?

SELECT ds.tablespace_name
,ds.segment_name
,dobj.object_type
,ROUND(SUM(dhss.space_used_delta) / 1024 / 1024 ,2) "Growth (MB)"
FROM dba_hist_snapshot snpshot
,dba_hist_seg_stat dhss
,dba_objects dobj
,dba_segments ds
WHERE begin_interval_time > TRUNC(SYSDATE)-360
AND snpshot.snap_id = dhss.snap_id
AND dobj.object_id = dhss.obj#
AND dobj.owner = ds.owner
AND dobj.object_name = ds.segment_name
AND ds.owner ='&owner' AND ds.SEGMENT_NAME IN ('&table_name') AND dobj.object_type='TABLE'
GROUP BY ds.tablespace_name,ds.segment_name,dobj.object_type
ORDER BY 3 ASC
Connor McDonald
May 07, 2018 - 4:36 am UTC

It is a reasonable approximation.

I say approximation because:

dba_hist_snapshot
dba_hist_seg_stat

are historical view of data, and

dba_objects dobj
dba_segments ds

are current views of data.

History and current are of course, not guaranteed to align

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.