|Lesson 7|| Striping a table with PCTFREE|
|Objective|| Stripe a table with PCTFREE.|
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.
How do I stripe a table with PCTFREE
To stripe a table with the PCTFREE option in Oracle, you can use the following steps:
- 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 (
) PCTFREE 10;
- 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;
- 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 (
) PCTFREE 10
PARTITION BY HASH (id)
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.
Small, frequently accessed tables
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.
Adjusting PCTFREE to manage data blocks
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.
1) By setting PCTFREE to a value smaller than the amount consumed by each row, the placement of a single row will trigger a freelist unlink, thereby making the block ineligible to receive another row
2) In this case PCTFREE is set to 1, which means that Oracle will take the block off of the free list as soon as it becomes 1% full
3) Now that this block is logically full, Oracle will place the next row onto the next free block. the first row insert will trigger a freelist unlink and this block will also become full
4) As you repeat this, you eventually spread the state table across 50 different data blocks
The next lesson wraps up this module.