4.5 How do I… Determine a table’s initial size parameters?Problem
I would like to know how large to make a table before I create one. I also know that the table will eventually grow in the coming months and want to plan accordingly so that I do not use too much or too little storage space. How do I determine a table’s initial size parameters?
Technique
By using the formulas provided by this How-To, you can calculate the INITAL, NEXT, PCTFREE, and PCTUSED values before creating a table. These values can then be used by the CREATE TABLE command to create a table with properly sized parameters.
Steps
Determining how large a table must be will depend on several factors. Ideally, the entire table should fit within one extent. An extent is a collection of database blocks that comprise part or all of a table. When a table is created, the size of the INITIAL extent is defined. When data is added to the table, it creates the records within the INITIAL extent. If the INITIAL extent gets filled with data, Oracle allocates another extent, whose size is based upon the NEXT extent storage clause. For performance reasons, it is generally best to be sure that all database blocks for the table are contained within one extent. Thus, the INITAL storage clause should be large enough to contain every record, along with all overhead space.
1. Determine how large a block is set to for your Oracle instance. This is set at database creation time and cannot be changed without re-creating the entire database. CHP4_17.SQL contains a sample script to determine the database block size, by querying the V$PARAMETER view. Connect to SQL*Plus as the WAITE account, and run CHP4_17.SQL.
SQL> GET CHP4_17.sql
1 SELECT VALUE
2 FROM V$PARAMETER
3 WHERE NAME = ‘db_block_size’
SQL> /
VALUE
---------------------------------
4096
SQL>
2. Oracle inserts records into a block until it runs out of available space. The available space is determined by subtracting overhead and from the blocksize, and multiplying by the PCTFREE percentage. Follow the formula in Figure 4.16.
For example, if a block is 4096 bytes, there are 190 overhead bytes, and PCTFREE is 90; then the total available space for Oracle records is (4096-190)*.9 (about 3515 bytes).
3. Now that the available space for records within each block has been determined, you must find out the size in bytes of an average record. There is no clear method to determine this before the table has data. However, DESCRIBE the table and try to deduce the total bytes by looking at the number and types of columns. For example, the EMPLOYEE04 table is described in Figure 4.17.
For an example, the EMPLOYEE_NAME column can average 20 bytes, the SALARY 2 bytes (number field = 2 bytes), JOB_DESCRIPTION 40 bytes, DATE_EMPLOYED 7 (date field = 7 bytes). The total is 69. Each column will need an additional byte for an internal separator. Also, add 5 bytes overhead for each record. Thus, the total will be 78 bytes for each record, on average.
4. Now that both the available space within a block and the average size of a block is known, you can determine how many records fit within a block. This is determined by the formula in Figure 4.18.
For example, assume there are 3515 available bytes within a block for record storage, and a record averages 78 bytes. There will be 3515/78, or about 45 records, that will fit within a block.
5. Now, you can easily determine the INITIAL storage parameter. The table will be the total size of all blocks. This is determined by knowing the total number of records you expect for the table (including growth over several months), the block size, and the number of records that will fit within a block. Figure 4.19 shows the formula for determining this.
In our ongoing example, we will assume one million records. Thus, the INITIAL extent should be (4096 ¥ 1000000) / 45, or 91022222 bytes. When issuing the CREATE TABLE command, you may specify 91022222, 88889K, or 87M. The NEXT extent determines how large an extent Oracle will obtain if the INITIAL extent ever is filled. This is an arbitrary number and will depend on how large you want to incrementally expand your table. If you do not know this, set the NEXT parameter to ten percent of the INITIAL storage parameter.
6. The PCTFREE parameter determines how much free space within each block will be reserved for expansion of existing records. If you do not anticipate much inserts, updates, or deletes, set the PCTFREE to a low value such as 10. If you anticipate your records to grow or shrink in size, such as when adding text to a text field, set the PCTFREE higher, such as 30 or 40.
7. The PCTUSED parameter determines how much space must be freed up by deleting records in a block before Oracle inserts into it. This parameter is used by Oracle to determine which blocks have space to insert. The larger this is set, the more records can fit within a block. However, this increases the chances of row chaining, which occurs when a record is updated and the new record size cannot fit into the remaining space of the block. Oracle would then have to “chain” the record, storing part of the record within one block and part of the record within another. This will adversely affect the performance of the table. If you do not know what you want to set PCTUSED to, start by subtracting PCTFREE from 100. So, if your PCTFREE is 10, set PCTUSED to 90.
How It Works
Step 1 determines how large the block size is. Step 2 determines the average space available within each block. Step 3 determines the average record size. Step 4 determines the number of records that will fit within a block. Step 5 brings all previous steps together to determine the INITIAL storage parameter. Step 6 shows how to determine the PCTFREE clause. This is important to prevent row chaining. Step 7 shows how to determine the PCTUSED parameter, also useful in preventing row chaining.
You should strive to fit all records within the INITIAL extent. Be sure to predict how large your table will grow within six or twelve months, and size accordingly. If a table grows into several extents, Oracle will be encumbered with overhead, and performance on the table will degrade. This is especially true for tables with over five extents.
The overhead of a block is determined by adding 57 to the product of 23 and the INITRANS parameter. By default, the INITRANS is one. So, the general overhead of a block is roughly 80 bytes. Due to other factors, the overhead for each block in reality reaches about 90–100 bytes.
Comments
After creating the table and populating it with data, you can see if the table was sized properly. Look to see how many extents the table has by issuing SELECT SEGMENT_NAME, EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME = ‘table_name’;. If the EXTENTS column is greater than one, you sized the INITIAL storage clause too small.
You cannot specify PCTFREE and PCTUSED so that they add up to more than 100.
To see if the table was sized too small, analyze the table and look at the number of blocks used versus blocks free. If too many blocks are free, and you do not expect your table to grow, the INITIAL storage clause was sized too large. How-To 4.6 describes how to use the ANALYZE command to gather these statistics.