Operating System - HP-UX
1748015 Members
4801 Online
108757 Solutions
New Discussion юеВ

Re: database storage capacity planning

 
Edgar_8
Regular Advisor

database storage capacity planning

We are in the process doing a capacity planning forcast for our datawarehouse. Has anyone a formula or
automated process to calculate table storage requirements/sizes? We have reviewed the Oracle Note:10640.1
but the results do not give the correct values.

Thanks in advance!
18 REPLIES 18
Eric Antunes
Honored Contributor

Re: database storage capacity planning

Hi Edgar,

Here are some simple rules I remember now:

- A different tablespace for tables and indexes. Also consider a pair of tablespaces for each different module.

- The size of the datafiles you should use depends on several factors and it is very difficult to estimate. But since you are talking about a datawharouse, you should size each datafile with 2Gb for starting...

- About tables, the important aspect to keep few large extents. I have a little script that helps me to control this for all tables:

select s.owner, s.segment_name, s.extents, s.max_extents
from dba_segments s
where s.extents >= 10 and
s.tablespace_name != 'SYSTEM' and
s.owner != 'SYS' and
s.segment_type = 'TABLE' and
not exists (select 1 from dba_tblexts e
where nvl( e.extents,0) + nvl( e.extents_tratados,0) + 2 >= nvl( s.extents,0) and
s.segment_name = e.segment_name and
s.owner = e.owner);

I've created this dba_tblexts table to know how many extents I've verified and considered normal for each table: if an extent for a table is created each month it may be normal but if this happens each week, it may be because the "next extent" size in the table storage definitions is too small and you should increase it...

Anyway, if you have table with many extents, you cannot recreate it but you can always export and import it with the "merge" option to create a single extent at import time.

For indexes, it is much more easy because you can recreate them: I normally recreate indexed with more than 10 extents.

That's what I remember for now. Hope this'll help you!

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Edgar_8
Regular Advisor

Re: database storage capacity planning

Hi Eric,

We need to establish storage capacity for tables and therefore require a formula to determine a tables size in MB/GB.

Thanks in advance!
Eric Antunes
Honored Contributor

Re: database storage capacity planning

Ok, it is a more simple question than I was thinking:

select segment_name, sum(bytes) ebytes
from dba_extents
where owner not in ('SYS', 'SYSTEM') and
segment_type = 'TABLE'
group by segment_name
order by sum(bytes) desc

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Eric Antunes
Honored Contributor

Re: database storage capacity planning

Here is the same script in Mb:

select segment_name "Table Name", round( sum(bytes) / 1024 / 1024) "Table Size (Mb)"
from dba_extents
where owner not in ('SYS', 'SYSTEM') and
segment_type = 'TABLE'
group by segment_name
order by round( sum(bytes) / 1024 / 1024) desc

Eric Antunes
Each and every day is a good day to learn.
Edgar_8
Regular Advisor

Re: database storage capacity planning

Hi Eric,

Thanks for the feedback. We already know how to determine the current size a table is taking. What we are attempting to determine is to project what a tables size would be say in 2006 given that say the number of records in that table is "x". Is there know way of projecting a tables size using the number of expected rows;average row length/size; and db block size?

Thanks in advance!
Hein van den Heuvel
Honored Contributor

Re: database storage capacity planning


Hmmm, I would simply assume a linear relation of row-count and used-space.
x rows now, taking y GB? Then for 2x rows anticipate 2y GB.

For datawarehouse applications I would go much more aggressive on the file sizes then Eric suggest. How about starting at 20GB?

Also, be sure to try to have Oracle manage as much as possible: reduce the number of tablespaces, reduce the number of device, Stripe And Mirror Everything (SAME).

KISS: if a whole set of disks / storage quantity is going to be dedicated for the Oracle DB, then just hand it over. Don;t try to help to much by dividing it up in morcels.

I assume you are not yet running 10g, but you may want to look ahead at 10g new features to understand which direction Oracle is going. (which I'm tempted to summarize as 'give oracle the keys to the castle and it will take over from there' :-).

Just an opinion,
hth,
Hein.
Eric Antunes
Honored Contributor

Re: database storage capacity planning

If you know the expected number of rows per year for example, you can estimate for each table what will be the size of this table in the future.

For example, I've a table A with 1097651 rows sizing 264 Mb. This gives 4158 rows per Mb.

I know the average rows per year with the following query:

select max(trunc( creation_date)) - min(trunc( creation_date)) "Table Days",
count(*) "N.├В┬║ Rows",
round( count(*) / (max( trunc( creation_date)) - min(trunc( creation_date))) * 365) "Avg. Rows / Year"
from A

For table A, I get the following result:

Table Days N.├В┬║ Rows Avg. Rows / Year
1753 1097651 228547

So, the table will increase: 228547 (Row/Year) / 4158 (Row/Mb) = 55Mb!!

Eric Antunes
Each and every day is a good day to learn.
Yogeeraj_1
Honored Contributor

Re: database storage capacity planning

hi edgar!

just use estimates based on your experience and forecast the appropriate size or each tables! Don't be shocked!

Then create your Locally Managed Tablespaces.

when it comes to tables, then I would suggest you LEAVE THE STORAGE PARAMETERS OFF. If you are going with a simple "index tablespace" and "data tablespace" -- you might just as well go with "a single tablespace" and put everything in it (no performance gains to be
had by separating the two structures -- none). Then just use auto-allocation and stripe the tablespace across as many devices as possible.

You can also use tools like TOAD to generate estimates based on your own inputs.

Again, sizing tables of your own table is not an exact science.

good luck!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Edgar_8
Regular Advisor

Re: database storage capacity planning

Hi Eric/Yogi,

Thanks for the feedback. Unfortunately when requesting additional storage space one needs to provide solid proof our how one came to the requested storage amount. Therefore we need a solid formula to forecast storage capacity growth.

Thanks in advance!