Home » RDBMS Server » Server Administration » Database space requirement calculation for projects
| Database space requirement calculation for projects [message #55079] |
Thu, 02 January 2003 03:04  |
Manu Gupta
Messages: 44
Registered: January 2002
|
Member |
|
|
Hello All,
Is there a away to calculate approximate space required per 1000 records.
I have designed database structures for the project and client wants to know how much space he should make available for the project. He also wants to know space requirement per 1000 records.
Is there a way or formula to calculate approximate space.
Thank you
Manu
|
|
|
|
| Re: Database space requirement calculation for projects [message #55087 is a reply to message #55079] |
Thu, 02 January 2003 13:04  |
sharan alva
Messages: 57
Registered: November 1999
|
Member |
|
|
Hi,
Here is a note that I had obtained from metalink some time back. This note was valid for version 7 to 8i could be the same for 9i. However, I have not gone throught this in detail nor have I tested it. So do not ask me any questions on this..
thanks
Sharan
Table size increases proportionally with the number of rows in the table assuming average row length remains constant. While the DBA Guide provides a formula for calculating table size, it may not be as accurate as the following formula when calculating space for tables with a large number of records.
Below is a formula that will provide more accurate results for both small and large tables.
Note that this procedure estimates only the initial amount of space required for the table. Space used by transaction entries and deleted entries does not
become free immediately after the commit due to delayed cleanout. Trailing nulls are not stored in the database.
Below are methods for calculating space for tables. These are approximations, so it is a good idea to round up. Also making the initial extent size 10 to
20 percent larger than the value from these calculations may avoid allocating additional extents right away. Some values may be operating system dependent.
Knowing the type of transactions that will affect the data in the tables is also very important. This will help you size pctfree and pctused accordingly.
For example, as the rows lengthen in a block, row chaining may result if pctfree is not set sufficiently high. Also deletes and updates may empty out
blocks so that the table needs more blocks even though the number of rows in the table is static. For details on pctfree and pctused, please refer to the Oracle RDBMS Database Administrator's Guide for Version 7/ Version 8i.
First, calculate the total block header size:
block header = fixed header + variable transaction header +
table directory + row directory
where:
fixed header = 57 bytes (the 4 bytes at the end of the block have
already been taken account of in the 24 bytes
for the cache header)
variable transaction header = 23 * i where i is the value of INITRANS
for the table, or can grow
dynamically up to MAXTRANS.
table directory = 4 * n where n is the number of tables.
n = 1 for non-clustered tables.
row directory = 2 * x where x is the number of rows in the block.
The variable transaction header in the block header contains 23 byte-structures that hold information about transactions accessing the block. By default,
space is reserved for only one transaction (INITRANS = 1) in the block header.
As more transactions access the block, space for additional transaction
entries is allocated out of the free space in the block or an old entry is
reused. As rows are inserted, the 2 bytes for each row in the row directory
are also allocated out of the free space. Once allocated, this space becomes
part of the block header. So, once the two bytes have been allocated in the
row directory, this space is not reclaimed when the row is deleted. For example,
a block that is currently empty but had up to 50 rows at one time will have 100
bytes allocated in the header for the row directory. This space will be reused
as new rows are inserted in the block.
Using the above formula, the initial block header size for a non-clustered
table with INITRANS = 1 is:
block header = 57 + 23 + 4 + 2x = (84 + 2x) bytes
The space reserved for data within the block, as specified by PCTFREE, is
calculated as a percentage of the block size minus the block header.
available data space = (block size - total block header) -
((block size - total block header) * (PCTFREE/100))
For example, with PCTFREE = 10 and a block size of 2048, the total space for
new data in a block is:
available data space = (2048 - (84 + 2x)) - ((2048 - (84 + 2x)) * (10/100))
= (1964 - 2x) - ((2048 - 84 - 2x) * (10/100))
= (1964 - 2x) - (1964 - 2x) * 0.1
= (1964 - 2x - 196 + 0.2x) bytes
= (1768 - 1.8x) bytes
Now, calculate the combined data space required for an average row. Calculating
this depends on the following:
1. The number of columns in the table definition.
2. The datatypes used for each column.
3. The average value size for variable length columns.
A test database similar to the production database will be helpful here. To
calculate the combined data space for an average row in a table, use the
following query:
SELECT AVG(NVL(VSIZE(col1), 1)) +
AVG(NVL(VSIZE(col2), 1)) +
... +
AVG(NVL(VSIZE(coln), 1)) "SPACE OF AVERAGE ROW"
FROM table_name;
col1, col2, ... , coln are the column names of the table and table_name is the
table being evaluated.
Note: This formula assumes that columns containing nulls are not trailing
columns. A column length of 1 is assumed (column length of a null in a
trailing column is 0).
For example, a test table created with the following statement:
CREATE TABLE test (
A CHAR(10),
B DATE,
C NUMBER(10, 2));
can have the space determined by the average row in the table with the query:
SELECT AVG(NVL(VSIZE(A), 1)) +
AVG(NVL(VSIZE(B), 1)) +
AVG(NVL(VSIZE(C), 1)) "SPACE OF AVERAGE ROW"
FROM test;
Alternatively, if a test database is not available, you can estimate the
combined data space for an average row in a table. This is done by examining
the datatype of each column in a table. If the column is of a fixed length
datatype, the value for the column in each row of the table is fixed. For
variable length columns, you must determine the average column value and
determine how much space is required to hold this average value.
For example, using the above table test, the column lengths of the first two
columns are of fixed lengths. So, column A requires 10 bytes (assuming a one
character set is being used) and column B requires 7 bytes. Column C can vary
for each row in the table because the NUMBER datatype is a variable length
datatype. By estimating your proposed data, you can determine the typical value
for this column. For NUMBER datatypes, a typical value may require approximately:
average length column C = (average_precision/2 + 1) bytes
= (8/2 + 1) bytes (estimating an average_precision of 8)
= 5 bytes
Calculating the average space required for the columns in an average row for
table test:
average row = (A + B + C)
= (10 + 7 + 5) bytes
= 22 bytes
Note: negative numbers consume one additional byte for the sign.
Using the results from above, we can calculate the total average row size (or
the minimum amount of space required by a row) in a non-clustered table with
the formula:
average row size = row header + F + V + D (bytes per row)
where:
row header = 3 bytes per row of a non-clustered table
F = total length bytes of all columns with 1 byte column
lengths (CHAR, NUMBER, DATE, and ROWID types)
V = total length bytes of all columns with 3 byte column
lengths (VARCHAR2, LONG, RAW, LONG RAW datatypes)
D = combined data space of all columns in average row
(from above)
For example, the total average row size for table test is:
average row size = (3 + (3 * 1) + (3 * 0) + 22) bytes
= 28 bytes
Note: The minimum row size for a non-clustered table is 9 bytes. Therefore, if
the calculated value for an average row size is less than this absolute
minimum row size, use the minimum value as the average row size.
After the average row size is determined, you can calculate the average number
of rows that can fit into a database block (the values of PCTFREE and PCTUSED
will also determine the amount of space used):
average number of rows per block =
floor(available data space / average row size)
where:
available data space and average row size are calculated above.
Using the information for table test from above:
average number
of rows per block = x = (1768 - 1.8x)/28 bytes
28x = 1768 - 1.8x
29.8x = 1768
x ~ 59 = average number of rows per block
Make sure you round x or the average number of rows per block DOWN. Once you
know the number of rows that can fit inside the available space of a database
block, you can calculate the number of blocks required to hold the proposed
table:
number of blocks
for the table = number of rows / average number of rows per block
Using 10,000 rows for table test:
number of blocks
for table test = 10000 rows / 59 rows per block
~ 169 blocks
To get this value in bytes, multiply by the database block size. And again,
round the number of blocks from the above DOWN.
|
|
|
|
Goto Forum:
Current Time: Sat Dec 28 06:24:14 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
