Data Blocks  «Prev  Next»
Lesson 9

Oracle Database Engine Conclusion

As you can see, the interaction between Oracle data blocks and the database engine is fairly complex.
We have covered some very important information in this module relating to Oracle data blocks and their effects on database performance.
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:
  1. Oracle databases generally perform faster with large blocksizes, especially index accesses.
  2. Large blocks mean that more RAM storage must be available in the db_block_buffers.
  3. Space is reserved on blocks by using the PCTFREE table parameter.
  4. Only one table or index type may inhabit a data block.
  5. The first data block of a table or index is called the segment header.
  6. The segment header contains control information about free blocks, extents, and high water marks.
  7. The high water mark for a table gives the highest blocks that have been used to store rows.
  8. The high water mark can only be lowered by a table reorganization.
  9. Full-table scans will always read up to the high water mark, even if the space is completely empty.
  10. The PCTUSED parameter should be set to a low value for high performance.
  11. The PCTFREE parameter should be set so that chained rows occur infrequently.
  12. 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

Now that you have completed this module, you should be able to:
  1. Determine appropriate data block size
  2. Describe the functions of the segment header parameters
  3. Set PCTUSED and PCTFREE parameters for optimal performance
  4. Size table and index extents
  5. Monitor and detect poorly performing indexes

Oracle Tuning Concepts - Terms

Here are terms from this module that may be new to you:
  1. db_block_size: This is a parameter to determine data block size.
  2. data block header: Contains general block information, such as the segment type [data, index or rollback] and the block address.
  3. high water mark: The boundary between used and unused space in a segment.
  4. freelist: A freelist is a list of the data blocks that are available for inserts; the data blocks on the free list have free space greater than PCTFREE, and have been set aside for that segment's extents.
  5. PCTFREE: PCTFREE sets the minimum percentage of a data block to be set aside as free space for possible updates to rows that already exist in that block.
Now that we know how to manage data blocks for optimal database performance, let's move on to look at tuning with Oracle data structures.

Specifying Database Block Sizes

The DB_BLOCK_SIZE initialization parameter specifies the standard block size for the database. This block size is used for the SYSTEM tablespace and by default in other tablespaces. Oracle Database can support up to four additional nonstandard block sizes.

DB_BLOCK_SIZE Initialization Parameter

The most commonly used block size should be picked as the standard block size. In many cases, this is the only block size that you need to specify. Typically, DB_BLOCK_SIZE is set to either 4K or 8K. If you do not set a value for this parameter, the default data block size is operating system specific, which is generally adequate. You cannot change the block size after database creation except by re-creating the database. If the database block size is different from the operating system block size, ensure that the database block size is a multiple of the operating system block size. For example, if your operating system block size is 2K (2048 bytes), the following setting for the DB_BLOCK_SIZE initialization parameter is valid: 

DB_BLOCK_SIZE=4096
A larger data block size provides greater efficiency in disk and memory I/O (access and storage of data). Therefore, consider specifying a block size larger than your operating system block size if the following conditions exist:
  1. Oracle Database is on a large computer system with a large amount of memory and fast disk drives. For example, databases controlled by mainframe computers with vast hardware resources typically use a data block size of 4K or greater.
  2. The operating system that runs Oracle Database uses a small operating system block size. For example, if the operating system block size is 1K and the default data block size matches this, the database may be performing an excessive amount of disk I/O during normal operation. For best performance in this case, a database block should consist of multiple operating system blocks.

Nonstandard Block Sizes

Tablespaces of nonstandard block sizes can be created using the CREATE TABLESPACE statement and specifying the BLOCKSIZE clause. These nonstandard block sizes can have any of the following power-of-two values: 2K, 4K, 8K, 16K or 32K. Platform-specific restrictions regarding the maximum block size apply, so some of these sizes may not be allowed on some platforms.

Oracle Data Blocks - Quiz

To complete this module, click the Quiz link below to test your knowledge of data blocks and performance.
Oracle Data Blocks - Quiz