Setting PCTFREE Values-Database Tables

Setting PCTFREE is sometimes overlooked. On the one hand, you need to use it to avoid too many rows from migrating. On the other hand, you use it to avoid wasting too much space. You need to look at your objects and describe how they will be used, and then you can come up with a logical plan for setting this values. Rules of thumb may very well fail you on this setting; it really needs to be set based on usage.

•\ High PCTFREE: This setting is for when you insert lots of data that will be updated, and the updates will increase the size of the rows frequently. This setting reserves a lot of space on the block after inserts (high PCTFREE).

•\ Low PCTFREE: This setting is for when you tend to only ever INSERT or DELETE from the table, or if you do UPDATE, the UPDATE tends to shrink the row in size.

Again, there are no hard and fast rules as to what is high and low with these parameters. You’ll have to consider the behavior of your application when setting PCTFREE. The PCTFREE value can range from 0 to 99. A high setting of PCTFREE might be something like 70 which means that 70 percent of the block will be reserved for updates. A low value of PCTFREE might be something like 5, meaning you leave little space on the block for future updates (that make the row grow in size). A high setting of PCTFREE might be in the range of 70 to 80. A low setting of PCTFREE would be somewhere around 10.

LOGGING andNOLOGGING

Normally, objects are created in a LOGGING fashion, meaning all operations performed against them that can generate redo will generate it. NOLOGGING allows certain operations to be performed against that object without the generation of redo; we covered this in Chapter 9 in some detail. NOLOGGING affects only a few specific operations, such as the initial creation of the object, direct path loads using SQL*Loader, or rebuilds (see the Oracle Database SQL Language Reference manual for the database object you are working with to see which operations apply).

This option does not disable redo log generation for the object in general—only for very specific operations. For example, if I create a table as SELECT NOLOGGING and then INSERT INTO THAT_TABLE VALUES ( 1 ), the INSERT will be logged, but the table creation might not have been (the DBA can force logging at the database or tablespace level).

INITRANS

Each block in a segment has a block header. Part of this block header is a transaction table. Entries will be made in the transaction table to describe which transactions have what rows/elements on the block locked. The initial size of this transaction table is specified by the INITRANS setting for the object (for tables and indexes, this defaults to two).

Note Be aware that there is a legacy MAXTRANS parameter. This is ignored as all segments have a MAXTRANS of 255.