I have to do some project and need to estimate the DB size.
I'm going to use Oracle in the project because we will have a lot of transactions and data.
But in the "presentation" I need to specify, in 5 years, how much I expect the DB to grow.
So, I have a PDF with the way of calculating the row size and another stuff, but for SQL-Server.
I want to do it for Oracle.
The pdf is an estimation formula – without having the real DB, so I can't do queries to the DB to check the actual size (That's What I've seen in other posts here when I googled).
The formula for SQL-Server row is
Row_Size = Fixed_Data_Size +Variable_Data_Size +Null_Bitmap + 4
This formula works in Oracle too?
Also, "4" is the size of the header of a row (I don't know how to translate the meaning in English)
So, I have to do this in Oracle. What will be the value 4 in Oracle?
The other things I think I think I've solved them.
Best Answer
Ideally, you would create the database, load a bit of sample data, measure the size, and extrapolate. That is, by far, the more accurate method of estimating the size of a database in 5 years.
If you do want to compute the database size, you would generally start by figuring out how many rows will fit in a single block. For simplicity, we'll assume that rows are never deleted and that updates never change the size of the row. We'll also assume no compression is being used. Otherwise, things get a bit (more) complicated.
Calculate the size of the data in the row. For fixed-size data types (i.e.
DATE
,CHAR
), that's just the size of the type. For variable-size data types (i.e.NUMBER
,VARCHAR2
), that's the average size of the data in the column. There are a couple of bytes of additional overhead, but you can pretty safely ignore that-- they're going to be swamped by the errors in estimating the size of your actual data and in the subsequent estimate of the number of rows per block.If you expect that each row will have x bytes of data, the number of rows per block will be
assuming that a row is smaller than
<<database block size>> * <<pctfree of table>>/100
. If the row is larger, then don't add 1 to the result of thefloor
.Once you know the number of rows per block, the estimated size of the table will be
Walking through an example
My database block size is 8k and we'll assume that I'm using the default
PCTFREE
of 10 (meaning 10% of the block is reserved for future updates that increase the size of the row). I'll create a simple two-column tableIf
foo_id
is going to be the primary key with values from 1 to 1 million, eachfoo_id
will consume between 1 and 7 bytes of space. But I also know from doing the test that, on average, it'll take ~6 bytes (actually 5.89 bytes). Of course, the larger thefoo_id
values get, the more space, on average, eachfoo_id
requires. Oracle needs, on average 1.1 bytes per element to store the numbers 1-10, 1.92 bytes to store 1-100, 2.89 bytes to store 1-1,000, 3.89 bytes to store 1-10,000, 4.89 bytes to store 1-100,000, and 5.89 bytes to store 1-1,000,000. So, let's estimate for our example thatfoo_id
will require 6 bytes andfoo_str
will require 50 bytes because the averagefoo_str
is roughly 50 bytes. So we'll estimate a row size of 56 bytes.The number of rows per block
which works out to 132 rows per block. If we want to estimate the size of a 1 million row table,
which works out to 59.19 MB.
Now, let's test our estimate
We'll insert 1 million rows where
foo_id
goes from 1 to 1,000,000 andfoo_str
is a string with a random length between 1 and 100.Our estimate of the average row length was spot on (note that, in reality, you won't be nearly this close-- your estimate of variable column sizes will not be nearly so accurate)
But how big is the actual table? The most common measure is to look at the size of the segment which is 72 MB.
Our guess was off by ~20% and that was when we were perfect on our estimate of the size of a row. That's because Oracle allocates space to tables in chunks called extents that we've ignored. There are different algorithms for this that depend on the setup of the tablespace. Assuming recent Oracle versions where all tables are in locally managed tablespaces, you would be choosing between uniform extent allocation and automatic extent allocation. In my example, the tablespace I'm using is using automatic extent allocation. The exact algorithm for that, in turn, may depend on the version of Oracle you're using. In my case, though, the first 16 extents are 64 kb, the next 63 extents are 1 MB, and the last extent is 8 MB
That means that I probably got a bit unlucky and I had just a bit more data than would fit in 79 extents totaling 64 MB so I had to allocate an 80th extent that was 8 MB in size, for a total of 72 MB. We can use the dbms_space package to get more details about how much space is being used. When we do that, we see that we're actually only using 66.22 MB of the 72 MB that have been allocated. So our actual estimation error is really only ~10%
Is there a shortcut?
If you're using a recent version of Oracle, you can use the
dbms_space.create_table_cost
procedure to estimate the size of a table. There are a couple ways of doing this. The first option is to pass in the size of the row. Using our 56 byte estimate, that yields a table size estimate of 64 MB with 63.52 MB used which is pretty closeYou can also specify the data types of the columns that will be in your table. Since there is no way to specify the average size, though, this tends not to be nearly as accurate as doing it yourself. In our case, though, it's pretty good because our
VARCHAR2
column happened to be populated with strings that were, on average, half the maximum size of the column. In our case, though, it correctly estimated the allocated size of 72 MB with 67.94 MB used.