How to predict Oracle table sizes?

Question

I am trying to make a growth prediction for some tables that I have, and for this I need to do some calculations based on my row sizes, the number of rows that I generate per day, and good. Mathematics.

I calculate the average size of each row in my table as the sum of the average size of each field. So basically:

SELECT 'COL1' , avg(vsize(COL1)) FROM TABLE union SELECT 'COL2' , avg(vsize(COL2)) FROM TABLE 

Sum it up, multiply by the number of entries per day, and make predictions from there.

It turns out that for one of the tables I looked at, the resulting size is much smaller than I thought, and this made me wonder if my method was right.

In addition, I did not take into account the size of the indices for my forecasts - and, of course, I should.

My questions:

• Is this method reliable?

• Predictions tips for indexes?

I did my search on Google, but the methods that I find relate to segments and extensions or calculations based on the whole table. I will need a step with the actual row of my table to make predictions (I need to analyze the data in the table to determine the number of records per day).

And finally, this is an approximation. I know that I am missing a few bytes here and there with overhead and stuff. I just want to make sure I'm missing bytes, not gigas :)

Answer 1

1) Your method sounds to calculate the average line size. (Although keep in mind that if your column contains null, you should use avg(nvl(vsize(col1), 0)) instead of avg(vsize(COL1)) ). However, it does not take into account the physical arrangement of the strings.

First of all, it does not account for header information (from both blocks and rows): you cannot put 8k data in 8k blocks. See the documentation for the data block format for more information.

Then the lines are not always kept neatly packed. Oracle allows some space in each block so that rows can grow when they are updated (determined by the pctfree parameter). Also, when rows are deleted, empty space is not immediately restored (if you do not use ASSM with locally managed table spaces, the amount of free space required to return a block to the list of available blocks depends on pctused ).

If you already have some representative data in your table, you can estimate the amount of extra space that you will need by comparing the physically used space ( all_tables.blocks*block_size after collecting statistics) to the average row length.

By the way, Oracle can easily give you a good estimate of the average row length: collect statistics from the table and query all_tables.avg_row_len .

2) In most cases (read: if there is an error or you fall into atypical use of the index), the index will be proportional to the increase in the number of rows.

If you have representative data, you can well estimate its future size by multiplying its actual size by the relative increase in the number of rows.

Answer 2

The last time Oracle published its formulas for estimating the size of schema objects was in Oracle 8.0, which means the linked document is ten years of date. However, I do not expect much to change in how Oracle reserves the header segment, block header, or row header information.