| Lesson 6 | Remedies for memory consumption |
| Objective | Reduce memory usage. |
Reduce memory usage Consumption Remedies
If we detect memory swapping and cannot obtain more memory for the database server, then we can take actions to reduce memory
consumption. Examine the following Slide Show for examples that reduce memory usage.
1) Check the data buffer hit ratio.
If it is better than 95%, you can re-claim by reducing the db_block_buffers' init.ora parameter.
2) Check the library cache hit ratio. If you have good hit ratios, you can safely reduce shared_pool_size in the init.ora file.
3) Check for disk_sorts in the v$sysstat view. If you do not have excessive disk sorts and you are not using the MTS, you can safely reduce the sort_area_size init.ora parameter.
How can an Oracle DBA re-claim memory by reducing the db_block_buffers init.ora parameter?
An Oracle DBA can reclaim memory by reducing the db_block_buffers init.ora parameter by following these steps:
1. Determine the current value of db_block_buffers:
2. Calculate the new desired value for db_block_buffers:
3. Shut down the database instance:
4. Edit the init.ora file and set the new value for db_block_buffers:
5. Start the database instance:
Note: Reducing the db_block_buffers parameter may have an impact on the performance of the database. It's recommended to monitor the performance and adjust the value of db_block_buffers as necessary.
The next lesson wraps up this module.
1. Determine the current value of db_block_buffers:
SHOW PARAMETER db_block_buffers;
2. Calculate the new desired value for db_block_buffers:
new_db_block_buffers = current_db_block_buffers - memory_to_reclaim/db_block_size;
3. Shut down the database instance:
SHUTDOWN IMMEDIATE;
4. Edit the init.ora file and set the new value for db_block_buffers:
db_block_buffers = new_db_block_buffers
5. Start the database instance:
STARTUP;
Note: Reducing the db_block_buffers parameter may have an impact on the performance of the database. It's recommended to monitor the performance and adjust the value of db_block_buffers as necessary.
