Predicting Oracle Table Growth

Question

How can I predict the future size / growth of an Oracle table?

Assuming:

• linear growth of the number of rows
• known columns of basic datatypes (char, number, and date)
  • ignore the variability of varchar2
  • basic understanding of the space required to store them (e.g. number)
• basic understanding of blocks, extents, segments, and block overhead

I'm looking for something more proactive than "measure now, wait, measure again."

Answer 1

I suspect that the estimate will depend 100% on the problem domain. Your proposed method seems as good a general procedure as is possible.

Answer 2

1. Estimate the average row size based on your data types.
2. Estimate the available space in a block. This will be the block size, minus the block header size, minus the space left over by PCTFREE. For example, if your block header size is 100 bytes, your PCTFREE is 10, and your block size is 8192 bytes, then the free space in a given block is (8192 - 100) * 0.9 = 7282.
3. Estimate how many rows will fit in that space. If your average row size is 1 kB, then roughly 7 rows will fit in an 8 kB block.
4. Estimate your rate of growth, in rows per time unit. For example, if you anticipate a million rows per year, your table will grow by roughly 1 GB annually given 7 rows per 8 kB block.

Answer 3

Given your assumptions, "measure, wait, measure again" is perfectly predictive. In 10g+ Oracle even does the "measure, wait, measure again" for you. http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3165.htm#I1023436