Oracle Database Administration: The Essential Refe

Memory utilization

It should come as no surprise that memory-based operations are much faster (sometimes by thousands of times) than disk operations. As a result, tremendous performance improvements may be achieved by replacing disk I/O with memory access of data. The three primary ways in which this can be done are described in the following list:

Allocate additional DB_BLOCK_BUFFERS

This is probably the single most effective method of improving overall performance, particularly on queries. Additional database buffers allow more data blocks to remain in memory, so the data contained in these blocks can be accessed at memory speed with no need for disk I/O. Buffers are allocated using the INIT.ORA parameter DB_BLOCK_BUFFERS, and the value is the number of database block buffers to be allocated. So, if the database block size is 8192, each DB_BLOCK_BUFFER will be 8192 bytes. Note that changes to DB_BLOCK_BUFFERS do not take effect until the next time the database is started.

Tip

Be careful not to allocate so many DB_BLOCK_BUFFERS that the operating system begins to page; paging will eliminate any performance gain you may have achieved and will probably have an overall negative effect on performance.

Allocate additional shared pool

The shared pool size is controlled by the INIT.ORA parameter SHARED_POOL_SIZE, which specifies a shared pool size in bytes. The primary contents of the shared pool are the dictionary cache and the shared SQL area. Since the various components of the dictionary ...