Database Module summary
This module discussed how to store data in data blocks for best access by setting the right number of freelists,
and proper values for PCTFREE and PCTUSED. The main points of this module include:
- Oracle databases generally perform faster with large blocksizes, especially index accesses.
- Large blocks mean that more RAM storage must be available in the db_block_buffers.
- Space is reserved on blocks by using the PCTFREE table parameter.
- Only one table or index type may inhabit a data block.
- The first data block of a table or index is called the segment header.
- The segment header contains control information about free blocks, extents, and high water marks.
- The high water mark for a table gives the highest blocks that have been used to store rows.
- The high water mark can only be lowered by a table reorganization.
- Full-table scans will always read up to the high water mark, even if the space is completely empty.
- The PCTUSED parameter should be set to a low value for high performance.
- The PCTFREE parameter should be set so that chained rows occur infrequently.
- An index may warrant rebuilding if it has spawned to more than 4 levels, if the block gets per access is greater than 5, of where there are many deleted leaf rows.
Test with Large Volumes of Data
As described earlier in this module, objects within the database function differently after they have been used for some time. For example, a table's pctfree and pctused settings may make it likely that
blocks will be only half-used or rows will be chained. Each of these scenarios causes performance problems that will only be seen after the application has been used for some time.
A further problem with data volume concerns indexes. As a B-tree index grows in size, it may split internally, the level of entries within the index increases. As a result, you can picture the new level as being an index within the index.
The additional level in the index increases the negative effect of the index on data load rates. You will not see this impact until after the index is split. Applications that work acceptably for the first week or two in production only to suddenly
falter after the data volume reaches critical levels do not support the business needs. In testing, there is no substitute for production data loaded at production rates while the tables already contain a substantial amount of data