Sizing and Configuring Database Objects
Proper sizing and configuration of database objects are critical to achieving maximum database performance. Proper object sizing is an ongoing task; as objects are created and modified, you must continue to examine their characteristics and make changes when necessary. Some sizing-related problems that negatively impact performance are:
- Tablespace fragmentation
This problem, which leaves many unusable small extents scattered about a tablespace, can result when objects are created with inappropriate INITIAL or NEXT extent sizes.
- Row chaining
This problem, which causes the data from a single row to reside in multiple Oracle blocks, typically occurs when an insufficient PCTFREE setting is specified and updates subsequently occur to the table.
- Multiple extents
Multiple extents, which may cause data for a particular object to be spread across one or more datafiles, result when objects are created with improper INITIAL or NEXT extent sizes. This problem may become critical when the MAXEXTENTS parameter is permitted to assume the default value, since an attempt to allocate an extent beyond that number will result in a failure.
- Log waits
Log waits, which cause a process to wait while log buffer records are written to a log file or while a log file switch is occurring, can add significant processing time. These are usually caused by a combination of an insufficient number of log files and log files that are too small.
- Failure to extend a rollback segment
Such failures, which can cause a transaction to roll back, are caused when not enough rollback segments are allocated, or when the rollback segments allocated are not large enough.
The following sections contain specific guidelines and suggestions that may help prevent some of these performance problems.