Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have to do some project, and need to estimate the DB size.
I'm gonna to use Oracle in the project, because will have a lot of transactions and size.
But in the "presentation" I need to specify, in 5 years, how I expect the DB to grow up.

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 a estimation formula - without having the real DB, so I can't do querys in the DB to see the size actually occupy (That's What I've seen in another posts here when I google).

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, the 4 is the size of the header of row (I don't know how to translate the meaning from Spanish)

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.

Not able to get what you are asking for!
–
Satish PandeyAug 25 '12 at 18:40

Want to know how to know the db-size, in a estimation - I don't have a database to query the design. I've that formula, but it's for MSSQL, and I will use Oracle. Is the same ? Also, that "4" value is the size of header of the row (as far as I undestood the pdf I'm using for homework). That value is the same in Oracle ?
–
Gonzalo.-Aug 25 '12 at 18:44

2 Answers
2

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

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 table

If foo_id is going to be the primary key with values from 1 to 1 million, each foo_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 the foo_id values get, the more space, on average, each foo_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 that foo_id will require 6 bytes and foo_str will require 50 bytes because the average foo_str is roughly 50 bytes. So we'll estimate a row size of 56 bytes.

The number of rows per block

<<rows per block>> =
floor( 8192 *
(1 - 10/100) /
56 ) + 1

which works out to 132 rows per block. If we want to estimate the size of a 1 million row table,

<<size of table>> =
ceil( 1000000 / 132 ) *
8192

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 and foo_str is a string with a random length between 1 and 100.

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%

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 close

You 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.

There is such a formula. You can find it in an appendix of the Oracle 7 manual. There is also a note on Oracle Metalink that covers the same topic: Extent and Block Space Calculation and Usage in Oracle Databases (ID 10640.1).