how to know the % table size growth/reduce in teradata

Database
Enthusiast

how to know the % table size growth/reduce in teradata

Hi,

I am new to TERADATA technology. Can anyone let me know how to know the % change in table size in teradata. Is there any specific query to identify the % change in table size ???

Or else is there any table (system/DBC) from which we will get the required result.

We need to find the % change in table size for every week/15days/month.

Kindly help me to get the required data.

Thanks in adavance.

Regards,

Deepak Dhabade

5 REPLIES
Enthusiast

Re: how to know the % table size growth/reduce in teradata

As such if you have the PDCR or SYS_MGMT enabled to hold the historical query log related data, you will see a table tablespace_hst which gives the associated information at a day level for all the tables in all the databases.

Fan

Re: how to know the % table size growth/reduce in teradata

Hi Deepak,

I am unsure about the tablespace_hst option mentioned above, but the following query should give you some direction towards the entire approach:

select

databasename

,tablename

,sum (currentperm)/(1024*1024*1024) as current_GB

from dbc.tablesize

where tablename = 'FLOWN_COUPON'

and databasename = 'TEST_MOSAIC_DB'

group by 1,2

order by 1,2

I am not sure if there are dbc tables which store the % change in table size, hence, you will need to create a table of your own with a date field to store the results of the query above and then calculate the % change on your own.

Hope this helps!

Regards,

Shreya

Enthusiast

Re: how to know the % table size growth/reduce in teradata

If you don't have a tool, then you can resort to using scripts(linux, perl, python etc) which will do computation.Then you can schedule your scripts as per your requirement using schedulers.

Sample query to see space:

select sum(currentperm) actualspace, max(currentperm)*(hashamp()+1) effective_space from dbc.tablesize

where databasename = 'yourdb' and tablename = 'yourtblname';

Cheers,

Enthusiast

Re: how to know the % table size growth/reduce in teradata

Thanks a lot for your valuable suggestions :-)

According to my knowledge if the change in table size is 10 % (It may be increase or decrease) then stats would be stale.

So I wanted to know the % change in tables, If the % change is =< 10 % then stats will stale and I wanted to work on that stats stale.

Please help me to come up from this situation.

Regards,

Deepak Dhabade

Teradata Employee

Re: how to know the % table size growth/reduce in teradata

If you only want to achieve this knowledge of stale-stats from this .... why dont you just create COLLECT STATS script and schedule it with your batch?

If in some tables the data volume is huge i.e. collecting stats may take alot of time, you can schedule that seperately over the weekend or non-business critical hours.