Stripe table with PCTFREE

Sometimes there are tiny tables with small rows that are frequently accessed. An example would be a state code translation table. When this happens, tasks wait to access the single memory block that contains all of the values, resulting in buffer busy waits and other I/O contention as Oracle tasks queue up for their turn to access an Oracle data block.

To stripe a table with the PCTFREE option in Oracle, you can use the following steps:

1. Create the table: Use the CREATE TABLE statement to create the table and specify the PCTFREE option with a value that represents the minimum amount of free space to be left in each block of the table. For example:
   

   CREATE TABLE stripe_table (
      id NUMBER,
      name VARCHAR2(50),
      ...
   ) PCTFREE 10;
   
   

2. Create the tablespaces: Create multiple tablespaces, each on a separate disk drive. For example:

   CREATE TABLESPACE stripe_tbs1 DATAFILE '/path/to/disk1/stripe_tbs1.dbf' SIZE 100M;
   CREATE TABLESPACE stripe_tbs2 DATAFILE '/path/to/disk2/stripe_tbs2.dbf' SIZE 100M;
   ...
   
   

3. Create the table partitions: Use the CREATE TABLE statement with the PARTITION BY option to create partitions for the table, each in a different tablespace. For example:

   CREATE TABLE stripe_table (
      id NUMBER,
      name VARCHAR2(50),
      ...
   ) PCTFREE 10
   PARTITION BY HASH (id)
   PARTITIONS 4
   STORE IN (stripe_tbs1, stripe_tbs2, ...);
   
   

In this example, the table is striped across 4 partitions, each stored in a different tablespace, and PCTFREE is set to 10. This will ensure that 10% of each block of the table will be left free, and the data will be striped across multiple disk drives for improved performance and reliability.

Take the example of the state code translation table. Each pair of state name and state abbreviation is very small, so the entire table can fit onto a single 8K block. With thousands of concurrent tasks reading this block, although it may always be in the Oracle data buffer, you may buffer busy waits. While techniques such as increasing INITTRANS will get around this issue, it is a good idea to stripe the rows in this table onto many data blocks. Instead of having all 50 states on one data block, spread them across 50 data blocks. Here is how it is done.

The PCTFREE parameter removes a data block from the freelist when it has become logically full. By adjusting PCTFREE to a small number, you can ensure that only one row fits onto a data block.