Topic
  • 1 reply
  • Latest Post - ‏2012-09-19T08:31:48Z by SystemAdmin
Tom_Glaser
Tom_Glaser
27 Posts

Pinned topic Calculating Memory requirements for a table

‏2012-08-16T20:11:02Z |
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 in-memory 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 32-bit OS and 8 bytes for 64-bit
OS);
num_cols is the number of columns; and
sum_of(col_sizes) is the sum of the sizes of the columns.
For each in-memory 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
Updated on 2012-09-19T08:31:48Z at 2012-09-19T08:31:48Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    115 Posts

    Re: Calculating Memory requirements for a table

    ‏2012-09-19T08:31:48Z  
    Hi Tom,
    I assume we are talking about in-memory 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 run-time 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 sub-linearly. 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