Memory Tuning in Oracle Database
4 min readWhat is Memory Tuning?
Memory tuning involves redistributing or reallocating free memory to Oracle memory components, primarily aimed at optimizing the performance of Oracle queries. As database performance continues to be a significant concern for many clients, this blog delves into the intricacies of memory tuning within databases, highlighting its pivotal role in achieving high performance.
Outlined below are the different methods of memory tuning available, including OS-level process tuning, CPU tuning, RAM tuning, and database tuning. However, the focus of this blog will be specifically on memory tuning within databases.
Database Memory Tuning:
Memory tuning for databases primarily involves optimizing the System Global Area (SGA), which encompasses various components crucial for database performance. Let’s delve into each of these components in detail:
- Keep Pool
- Default Pool, Recycle Pool, Stream Pool
- Database Buffer Cache, including free buffers, modified buffers, and pinned buffers
- Shared Pool
Each of these components plays a critical role in SGA tuning and contributes to the overall efficiency and performance of the database system.
Keep Pool:
To enhance performance, critical packages are retained in the System Global Area (SGA). A procedure facilitates the inclusion of these packages in the keep pool. However, if SGA space is deallocated and reallocated to RAM, the kept package may be flushed. Upon database restart, the package remains in the keep pool until shutdown, thus reducing I/O operations.
Hit ratios are employed to ensure that parse code execution plans remain in the library cache. It is imperative that the hit ratio for the library cache exceeds 85% to optimize performance.
Stream Pool:
The stream pool serves as a buffer for the data pump, while certain small tables utilized daily can enhance performance when stored in the System Global Area (SGA) keep pool. This approach minimizes disk fetching by accessing data directly from memory. Conversely, large tables find placement in the recycling pool.
When selecting small tables, data is fetched into the data buffer cache, typically stored in the default pool. However, this pool may be flushed to accommodate new tables if buffer space is limited. To optimize performance, it’s recommended to allocate small tables to the keep pool rather than the default pool. Additionally, the hit ratio for the data buffer cache should surpass 95%. Should this ratio fall below 95%, resizing the database buffer cache becomes necessary.
The crux of memory tuning lies in optimizing the Database buffer cache and shared pool to maintain ample data in memory. Familiarity with basic Database buffer cache operations is essential for effective SGA tuning. To conclude, sample SQL query examples will be provided at the end of the blog to calculate hit ratios for memory tuning.
Free Buffers:
Modified data utilizes fetched blocks, which are then copied into the data buffer cache before altering the data image. These buffers are referred to as Free Buffers.
Modified Buffers:
Modified buffers are indeed known as Dirty Buffers. When the image in the disk and the image in the data buffer cache have been altered but not yet written to the disk, these buffers are termed Dirty Buffers.
Pinned Buffers:
The data within the DB buffer cache changes constantly. Server processes select these modified data for further transactions. This selected data is referred to as pinned data or buffers.
Physical reads: Oracle data blocks that Oracle reads from the disk by performing I/O are referred to as physical reads.
Logical reads: If Oracle can fulfill a request by retrieving data from the database buffer cache directly, it qualifies as a logical read.
DB block Gets: When Oracle locates the necessary data in the database buffer cache, it verifies whether the data is committed. If it is indeed committed, Oracle retrieves it from the buffers. These buffers are also referred to as DB buffer gets.
Consistent Reads: In the database buffer cache, if blocks are present but have been modified without being committed, the data must be fetched from the undo datafile. This ensures that any changes made to the data that have not yet been finalized (committed) are properly managed and can be reverted if necessary.
The aim of the db_buffer_cache is to boost the number of logical reads performed.
Soft Parsing: When an execution plan is already present in the Library Cache, the system avoids accessing the disk and instead generates a parse plan (parse code) based on the existing execution plan. This process is known as Soft Parse.
Hard Parsing: When generating a parse code, Oracle first checks for an available execution plan. If one is found, it proceeds with a soft parse. However, if no execution plan is available, Oracle conducts a hard parse, necessitating access to the disk.
Shared Pool: The Shared Pool comprises both the Library Cache and the Data Dictionary Cache.
Library Cache: The aim of the Library Cache is to enhance soft parsing efficiency.
Methods to achieve goal:
Methods for improving soft parsing efficiency include:
- Utilizing bind variables
- Employing stored procedures
- Monitoring and optimizing hit ratio (aiming for > 85%; if lower, consider increasing shared pool size)
- Leveraging the DBMS_Shared_pool package for management.
Following are the Sample queries to calculate the hit ratio:
The hit ratio is a pivotal factor in memory tuning through SQL queries.
select (sum(pinhits)/sum(pins))*100 as lchitratio from v$librarycache;
select namespace, pins, pinhits, reloads from v$librarycache order by namespace;
select namespace, kept, locks, executions from v$db_object_cache where type like '%PROC%';
To get the package of dbms_Shared_pool please run `@$ORACLE_HOME/rdbms/admin/dbmspool.sql‘
Data Dictionary Cache hit ratio:
select (sum(gets-getmisses-fixed))/sun(gets) from v$rowcache;
Redo log Buffer Cache:
select name, value from 'v$sysstat' where name like 'redo%';
