Cry about...
Oracle How To ...
Reduce thrashing or poor system performance
or how to un-tune Oracle!?
For High Performance Systems
If database performance is highly important (and you do not have a disk thrashing problem) then ignore these notes. These notes concern how to reclaim memory from an Oracle database - which in some ways can be regarded as "un-tuning" Oracle!
If database performance is highly important, the database is otherwise appears tuned (see notes on Oracle Database Tuning) but there is plenty of disk thrashing, then increase the amount of physical RAM.
For Performance Insensitive Systems
If database performance is not important then releasing memory from Oracle back to the operating system can improve caching for other applications and improve overall system performance - albeit at the expense of Oracle.
Reducing the SGA
The following table provides a checklist of configuration parameters to consider reducing. The figure under "Savage" is a recommended setting if you want a reasonable minimum memory usage. The "trim" is a percentage reduction to the current setting to free up some memory.
| Parameter | Savage | Trim | Absolute Minimum |
|---|---|---|---|
| DB_BLOCK_BUFFERS (Oracle 8 and prior) | 200 | -10% | 50 |
| DB_CACHE_SIZE (Oracle 9 onwards) | 2M | -10% | |
| JAVA_POOL_SIZE | 8M | -10% | |
| LARGE_POOL_SIZE | |||
| LOG_BUFFER | 65536 | -10% | 8192 |
| SHARED_POOL_SIZE | 100K | -10% | |
| SORT_AREA_SIZE |
The "trim" reduction is more applicable if performance is still important. When "trimming" memory it is recommended that you tune the database after a suitable period of usage - consider reducing further those parameters which tuning shows do not need to be increased.
Some of the "Savage" values might result in values too small for the environment within which Oracle is being used, in which case adjust accordingly.
Other Oracle Services
If you do not need the Oracle HTTP Server (Apache) then ensure that the service 'OracleOraHome90HTTPServer' is not set to automatically start.
These notes have been tested against Oracle 9.0.1 and may apply to other versions as well.
This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.