Hi,
Does anyone have a quick method to calculating the memory requirements for a table? For example, the following query will show me the size of the data in the table:
select b.table_name, cardin, size
from SYS_CARDINAL a,
sys_tables b
where a.rel_id = b.id
order by table_name;
Here's what the manual states:
The general formula for an inmemory table is
table_size + sum_of(index_sizes)
table_size =
1.3 x rows x (sum_of(col_sizes) + (3 x word_size) + (2 * num_cols) + 2)
where: rows is the number of rows;
word_size is the machine word size (e.g. 4 bytes for 32bit OS and 8 bytes for 64bit
OS);
num_cols is the number of columns; and
sum_of(col_sizes) is the sum of the sizes of the columns.
For each inmemory index, the index size is
1.3 x rows x ((dist_factor x sum_of(col_sizes + 1)) + (8 x word_size) + 4)
where "dist_factor" is a value between 1.0 and 2.0 that depends upon the
distribution of the key values. If key values are highly dissimilar, then use a value
closer to 2.0. If key values are highly similar, then use a value closer to 1.0.
.
.
Without having to manually calculate above for each table, does anyone have a quick way to do this?
Thanks,
Tom Glaser
Topic

Re: Calculating Memory requirements for a table
20120919T08:31:48ZThis is the accepted answer. This is the accepted answer.Hi Tom,
I assume we are talking about inmemory tables here. SYS_CARDINAL tells the flat size of the table. That is, 'checkpoint' size, which doesn't include indexes and other temporary data structures. In other words, what is missing is the information about runtime overhead of the table.
An estimate of overhead can be get as follows:
 flat size represents the value in SYS_CARDINAL table
 before mem size represents process footprint with empty database
 after mem size represents the process footprint with a database having one table only restored
The overhead size is <after mem size> minus <flat size>.
Overhead factor is <after mem size> divided by <flat size>
This is only an estimate for table exactly the same size you used in calculations. Index size, for example doesn't increase linearly. The leaf leven of index does (i.e. # of keys and their size requirement) but upper levels increase sublinearly. Thus you might want to perform the same calculation for the same table mupltiple times, with differing number of rows to see how process footprint increment relates to row number increment.
Regards
Vilho