If we detect memory swapping and cannot obtain more memory for the database server, then we can take actions to reduce memory
consumption.
When facing memory swapping issues in Oracle and additional memory is not an option, several strategies can be employed to reduce memory usage. Here are some key solutions and approaches to minimize memory consumption in Oracle:
Optimize SGA and PGA Parameters
Reduce SGA Size: Reduce the size of the System Global Area (SGA), which is Oracle's shared memory area.
DB_CACHE_SIZE: Lower the database cache size if it's larger than necessary for the workload.
SHARED_POOL_SIZE: Reduce the shared pool size, especially if the application does not require a large number of cursors or PL/SQL code.
LARGE_POOL_SIZE: If not using features like Oracle Shared Server or RMAN, this can often be minimized.
JAVA_POOL_SIZE: If you are not using Java stored procedures, this can be minimized.
Adjust PGA_AGGREGATE_TARGET: Reduce the Program Global Area (PGA) aggregate target to limit memory allocated for session-specific data.
Tune Buffer Cache
DB_BLOCK_BUFFERS / DB_CACHE_SIZE: Reduce the size of the buffer cache if it is oversized. This can be done by lowering the DB_BLOCK_BUFFERS or DB_CACHE_SIZE parameter. Monitor the buffer hit ratio to ensure it stays within acceptable limits (> 70% is usually recommended).
Optimize SQL Execution and Avoid Inefficient SQL
Reduce Unnecessary Sorts: Large sort operations can consume a lot of memory. Ensure that indexes are optimized and unnecessary sorts are avoided.
Use Bind Variables: Encourage the use of bind variables instead of literals in SQL to prevent unnecessary hard parsing, which can lead to high memory usage in the shared pool.
Reduce the Library Cache Usage
SHARED_POOL_SIZE: Reduce the shared pool if it’s larger than needed. Ensure SQL code is efficient, and reuse SQL statements with bind variables to minimize library cache consumption.
Optimize the Library Cache Hit Ratio: A high hit ratio may indicate excessive memory usage. If the hit ratio is above 95%, consider lowering the SHARED_POOL_SIZE.
Limit Sessions and Processes
PROCESSES and SESSIONS: Limit the number of concurrent sessions and processes. Having too many sessions and processes can consume a lot of memory.
Tune the Sort Area Size
SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE: Lower these parameters to reduce memory for sorting operations. A large sort area size can consume excessive memory.
MEMORY_TARGET and MEMORY_MAX_TARGET (for AMM) or SGA_TARGET and PGA_AGGREGATE_TARGET (for ASMM): These automatic memory management settings allow Oracle to adjust memory dynamically based on usage. Setting these appropriately can help Oracle optimize memory usage without manual adjustments.
Reduce Large Pool and Java Pool
LARGE_POOL_SIZE: Only needed for parallel execution, RMAN backup, and certain Oracle features. If these are not heavily used, consider reducing it.
JAVA_POOL_SIZE: Reduce this pool if Java stored procedures or applications are not used within the database.
Use Dedicated or Shared Server Mode Appropriately
Shared Server Mode: If you have many concurrent users with intermittent usage, consider using Shared Server mode. This reduces memory usage per session by sharing server processes.
Reduce Data Load and Workload if Possible
Tune Applications: Reduce the workload and data volume handled by the database where possible. Optimize applications to process data more efficiently, reducing memory demand.
Monitor and Tune Background Processes
Background processes like DBWR, LGWR, and ARCH can consume significant memory. Tuning them by adjusting related parameters may help reduce memory usage.
Summary: Reducing memory consumption in Oracle requires careful tuning of parameters related to the SGA and PGA, optimizing SQL execution, limiting sessions and processes, and considering shared server mode. Start with adjusting parameters for buffer cache, shared pool, sort area, and process limits, and use Oracle's memory management features (AMM or ASMM) to manage memory more dynamically. Regular monitoring and profiling of memory usage patterns are essential to identify the areas where memory can be saved without impacting performance significantly.
Examine the following series of images for examples that reduce memory usage.
=====================
BUFFER HIT RATIO
=====================
(should be > 70, else increase db_block_buffers in init.ora)
column "logical_reads" format 99,999,999,999
column "phys_reads" format 999,999,999
column "phy_writes" format 999,999,999
select a.value + b.value "logical reads",
c.value "phys reads",
d.value "phy writes",
round(100 * ((a.value+b.value-c.value) / (a.value+b.value)))
"BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d
where
a.statistic# = 37
and
b.statistic# = 38
and
c.statistic# = 39
and
d.statistic# = 40;
<Output>
Hit Ratio Section
***************************************************************
=====================
BUFFER HIT RATIO
=====================
(should be > 70, else increase db_block_buffers in init.ora)
logical_reads phys_reads phy_writes BUFFER HIT RATIO
94,228,594 511,535 734,784 99
Check the data buffer hit ratio. If it is above 95%, you can re-claim memory by reducing the db_block_buffers’ init.ora parameter.
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.
===========================
LIBRARY CACHE MISS RATIO
===========================
(If > 1 then increase the shared_pool_size in init.ora)
column "LIBRARY CACHE MISS RATIO" format 99.9999
column "executions" format 999,999,999
column "Cache misses while executing" format 999,999,999
select sum(pins) "executions", sum(reloads) "Cache misses
while executing",
((sum(reloads)/sum(pins))) "LIBRARY CACHE MISS RATIO"
from v$librarycache;
<Output>
===========================
LIBRARY CACHE MISS RATIO
===========================
(If > 1 then increase the shared_pool_size in init.ora)
executions Cache misses while executing LIBRARY CACHE MISS RATIO
------------ ----------------------------- -------------------------
19,622,987 24,604 .0013
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.
This transcription includes all SQL commands, output formatting, query results, and notes as shown in the image.
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.
column value format 999,999,999
select name, value from v$sysstat
where name like 'sort%';
spool off;
<< Output >>
--------------------------------------------
NAME VALUE
--------------------------------------------
sorts (memory) 87,101
sorts (disk) 280
sorts (rows) 109,142,433
This transcription includes all SQL commands, output formatting, query results, and notes as shown in the image.
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:
Determine the current value of db_block_buffers:
SHOW PARAMETER db_block_buffers;
Calculate the new desired value for db_block_buffers:
Edit the init.ora file and set the new value for db_block_buffers:
db_block_buffers = new_db_block_buffers
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.
The next lesson wraps up this module.
