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 cache are automatically allocated by Oracle, any increase in the size of the shared pool results in additional memory for both the dictionary cache and the shared SQL area.
The shared SQL area contains copies of the most recently executed SQL statements, along with associated information like their execution plans. With a larger shared pool, it is more likely that a particular SQL statement has already been parsed and is resident in the shared SQL area, thereby saving the time required to reprocess the statement. This can be of particular value in a transaction processing system, where the same SQL statements are executed multiple times and where speed is a requirement.
- Allocate additional log buffer space
The log buffer is used to hold data to be written to the online redo log file. The size of the log buffer is controlled by the INIT.ORA parameter LOG_BUFFER, and the value is expressed in bytes. By allocating additional memory to the log buffer, disk I/O will be reduced, especially when transactions are long or numerous.