0476-0477

Previous Table of Contents Next

Page 476

The MINEXTENTS parameter specifies the number of extents allocated when the segment is created. The default is 1 for all segments except rollback segments, for which the default is 2. The MAXEXTENTS parameter specifies the maximum number of extents the database object can ever allocate. If this number is reached, an error message is generated and the operation that caused the segment to grab another extent fails. It is unusual, however, to enable database segments ”except rollback segments ”to use more than 10 extents.

You can modify the NEXT, MAXEXTENTS, and PCTINCREASE parameters after a table is created by using the ALTER TABLE SQL statement.

You should perform a sizing estimate so that the storage parameters are set correctly for the database object, thereby minimizing the system overhead of allocating additional extents.

You can use the STORAGE parameter in the CREATE statement for database segments ”tables, indexes, clusters, rollback segments, and so on. You can specify this parameter as part of the CREATE TABLESPACE SQL statement to set the default storage parameter for objects created in that tablespace. The tablespace default storage parameter is used only when the storage parameter has not been specified as part of the segment creation command.

Using Other Storage Parameters

In addition to the storage parameters you can specify in the storage clause, you can specify the TABLESPACE, PCTFREE, and PCTUSED parameters for each database segment.

The TABLESPACE parameter controls the tablespace in which the database segment is created. This parameter assumes that the user has quota privileges to create database objects in the tablespace. Users who want to create database segments must have resource privileges to do so. For each user, you can specify the default tablespace ”the default location in which the user 's database segments are created if the TABLESPACE parameter is not specified at the end of the CREATE command for the segment.

When you create a database segment (tables, indexes, clusters, and rollback segments), you should use the TABLESPACE parameter as part of the CREATE statement so that it is explicit where storage is used for the object.

TIP

As a simple performance-tuning measure, put the tables and indexes into separate tablespaces and ensure that the files for the tablespaces are located on separate disk drives . This method reduces the I/O bottleneck of having to read and write both table data and index data from the same disk drive.

The PCTFREE parameter for a table controls how much of each Oracle block remains free to enable the rows to expand. By default, the PCTFREE parameter is 10 percent; that way, 10 percent

Page 477

of every Oracle block for the table is left empty so that the block's rows can expand into it. This area is used only when you expand rows with the UPDATE statement to increase the actual values being stored or to give values to columns that previously were null.

Oracle stores only the bytes required to store a data value. No padding out occurs. The only exception is a column that has been declared as CHAR, in which case padding with spaces occurs. CHAR declarations are not used often, however. Oracle attempts to fit as many rows as possible into an Oracle data block until the PCTFREE limit is breached. Further rows are stored in the next available block for the table. If no more blocks have enough storage free to accept a new row, another extent is allocated to the table.

To specify a meaningful setting for the PCTFREE parameter, you need to know something about the behavior of the table. (Ideally, this information comes from the database designers.) Suppose that rows are inserted into the table and many columns are left with null or small values. If the table will later be updated to give the columns values where they were previously null or to increase the actual amount of data held, you should set the PCTFREE parameter higher than 10 percent. The value depends on how much you expect the rows to grow.

In another table, the rows might not increase much after they are inserted. In that case, leaving the PCTFREE parameter at 10 percent wastes space that rarely will be used. Reducing the value of PCTFREE ”but not to zero ” improves storage utilization. Performance is improved because each Oracle block read from disk has more useful data in it.

PCTUSED is another parameter you can set for a table. This parameter sets a watermark level below which the amount of space used in a block must fall before new rows are accepted into the block. Suppose that a table has a PCTUSED value set at 40 percent. As rows are deleted from the block ”or even updated where the amount of storage used by the row is reduced ”the freed storage is reused until at least 60 percent of the block is empty. The amount of storage used must fall below 40 percent; in other words, more than 60 percent of the block is free. This parameter attempts to reduce the overhead of managing blocks that will accept new rows. For a static table in which not many rows are deleted or not much space is freed within the block, you can set the PCTUSED parameter fairly high ”to as much as 80 percent. That way, storage is reused as soon as it becomes available in a block. For a busy transaction table in which many rows are inserted and deleted, you should set the PCTUSED figure lower ”to as little as 20 percent. That way, when a block can accept new rows, you know that it is fairly empty.

TIP

It is quite common to come across a situation in which many rows are deleted from a table and the table continues to allocate additional extents. This is usually the case when the amount of storage used in a block does not fall below the PCTUSED value. Even though rows have been deleted from the table, the block does not accept new rows. Reduce the PCTUSED value so that more of the blocks are used for the new rows.

Previous Table of Contents Next