Wednesday, November 10, 2004

dbazine: Tuning and Performance

This is a neat trick I learned from one of Rich Niemiec's articles. Rich has written numerous articles and given dozens of presentations on Oracle. If you don't have his latest book on tuning titled, Oracle Performance Tuning Tips & Techniques you need to buy it.
If you are thinking about changing the DB_BLOCK_SIZE parameter to increase database performance, consider increasing the DB_FILE_MULTIBLOCK_READ_COUNT parameter. More blocks will be read during sequential read operations. In some cases, it will provide benefits similar to a larger DB_BLOCK_SIZE parameter and you won't have to rebuild the entire database.
Increasing the DB_FILE_MULTIBLOCK_READ_COUNT may have an impact on access path selection. Full table scans use multiblock reads, so the cost of a full table scan depends on the number of multiblock reads required to read the entire table. The number of multiblock reads required to read the entire table depends on the number of blocks read by a single multiblock read which is specified by the DB_FILE_MULTIBLOCK_READ_COUNT parameter.
For this reason, the optimizer may be more likely to choose a full table scan when the value of this parameter is high.

No comments: