- Introduction
- Performance Impact of the Database Cache
- Obtaining Database Cache Size Advice in Oracle9i
- Dynamic SGA Sizing in Oracle9i
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Performance Impact of the Database Cache
In general, you should not wait until a problem occurs in your production system to tune the system. The tuning effort should start from the application architecture and design phase itself. During the design phase, you should consider the table design and index usage in order to determine the best application design as well as the I/O distribution on the available drives. Once the application mix is clearer, the tuning effort should focus on tuning the queries by using a reasonable data set that represents the most likely scenario as well as several not-so-normal situations. After the application has been tuned, the database configuration and memory structures should be revisited.
The database buffer cache is one of the most important components of the SGA that can have a substantial impact on overall performance. Proper sizing of the database cache is therefore essential but is often extremely difficult due to the lack of understanding of the application environment. Prior to Oracle9i, it was impossible to change the cache size without changing the initialization parameters and therefore requiring a database restart.
In general, increasing the size of the database buffer cache will improve the performance of your database environment. Calculating the hit ratio for the buffer cache will allow you to measure the performance impact as you increase the size of the buffer cache. However, it's important to remember that the buffer cache is one component of the SGA and increasing the size of the buffer cache would increase the SGA size as well. The SGA should not be so large that it causes swapping and paging to occur at the operating system level. Swapping and paging should be minimized to avoid degrading system performance.