The PCTFREE parameter is used to reserve space on each data block for the future expansion of row values (via the SQL UPDATE command). Table columns may be defined as allowing null values that do not consume any space within the row, or with varchar data types. A varchar data type specifies the maximum allowable length for the column instance, but the acceptable range of values may be anywhere from 4 bytes (the size of the length holder) to the size of the field plus 4 bytes. Hence, a varchar(2000) may range in size from 4 bytes to 2,004 bytes.

If an application initially stores rows with empty values and later fills in the values, the PCTFREE parameter can dramatically reduce I/O contention. If a block of storage is filled by the addition of a row, subsequent updates to that row to fill in column values will cause the row to fragment—usually onto the next available contiguous block.

Block Sizing

It is very ironic that the Oracle developer must choose a block size when the database is initially created—a time when knowledge of system performance is so limited. While it is possible to use the Oracle import/export utilities to change block sizes, too little attention is given to the proper sizing of database blocks. The physical block size is set with the DB_BLOCK_SIZE parameter in the init.ora file. While the default is to have 4 K block sizes, many Oracle developers choose at least 8 K block sizes for large, distributed databases. Some DBAs believe that 16 K is the best block size, even for OLTP systems that seldom perform full-table scans. Depending upon the host platform and operating system, Oracle block sizes may be set from 2 K up to 32 K. The Oracle OS manual will provide the acceptable ranges for your operating system.

When determining the required space for your database, subtract the block header and PCTFREE from your calculations. Oracle block headers are a function of the DB_BLOCK_SIZE (in init.ora) and the value of INITTRANS (in tablespace definition).

The space (in bytes) required by the data block header is the result of the following formula:

Block Header size

= (BLOCK_SIZE - KCBH - UB4 - KTBBH - (INITRANS - 1)) * (KTBIT - KDBH)

= (DB_BLOCK_SIZE - 20 - 4 - 48 - (INITRANS - 1)) * (24 - 14)

= (DB_BLOCK_SIZE - 72 - (INITRANS - 1)) * 10

Where

KCBH (block common header) = 20

UB4 (unsigned byte 4) = 4

KTBBH (transaction fixed header) = 48

INITRANS (initial number of transaction entries)

KTBIT (transaction variable header) = 24

KDBH (data header) = 14

The size (constants) of KCBH, UB4, KTBBH, KTBIT, and KDBH can also be found in the fixed view V$TYPE_SIZE.

The principle behind block sizing is simple. I/O is the single most expensive and time-consuming operation within a database. As such, the more data that can be read in a single I/O, the faster the performance of the Oracle database. This principle is especially true for databases that have many reports that read the entire contents of a table. For systems that read random single rows from the database, block size is not as important—especially with database clusters. An Oracle cluster is a mechanism whereby an owner row will reside on the same database block as its subordinate rows in other tables. For example, if we cluster order rows on the same block as their customer owners, Oracle will only need to perform a single I/O to retrieve the customer and all of the order rows. Of course, in a distributed database where joins take place across different Oracle instances, clustering cannot be used. The additional I/O will be required to read the rows individually.

Bear in mind that increasing the block size of an Oracle database will also affect the number of blocks that can be cached in the buffer pool. For example, if we set the db_block_buffers init.ora parameter to 8 MB, Oracle will be able to cache 1,000 4 K blocks, but only 500 8 K blocks.

Increasing the Oracle block size also increases the risk of concurrency bottlenecks, especially when the INITTRANS and MAXTRANS values are set too low. INITTRANS and MAXTRANS are Oracle tablespace creation parameters that determine the amount of space to be reserved on a block for concurrency locks. The maximum values for INITTRANS and MAXTRANS is 255, meaning that no more than 255 transactions may simultaneously access a specific database block.

If you suspect that your values are too low, a query of the V$LOCK table will reveal all tasks that are waiting for access to a page. Listing 7.2 shows an SQL script to check for lock contention.