Lesson 7

You should be familiar with the following concepts in order to tune the memory of your OS:

1. The OS will freely allocate memory to all tasks on the database server.
2. When the Oracle database starts, RAM memory is allocated for the SGA.
3. Dedicated listener processes will also consume memory according to the sort_area_size init.ora parameter.
4. vmstat utility is a great way to monitor memory swapping.
5. The pi column in vmstat shows memory swapping.
6. Savvy DBAs often store vmstat data inside Oracle tables for later analysis.
7. If you experience memory swapping you can reduce the SGA size, reduce sort_area_size, or implement the multi-threaded server.

The sort_area_size parameter in Oracle is part of the init.ora file, which contains initialization parameters for an Oracle database. The primary function of the sort_area_size parameter is to define the amount of memory allocated to each user session for performing in-memory sorts.
The sort_area_size parameter impacts the performance of various SQL operations, such as sorting for ORDER BY clauses, GROUP BY clauses, and creating indexes. When a user session needs to sort data, the session will use the memory allocated by the sort_area_size parameter. If the allocated memory is sufficient, the entire sort operation will be performed in memory, which is faster. If the memory is not enough, the database will use a temporary tablespace on disk to perform the sort operation, which is slower.
Here is an overview of how the sort_area_size parameter works:

1. When a user session starts, the Oracle database assigns an amount of memory for sorting operations, as specified by the sort_area_size parameter.
2. When a sort operation is initiated, the session uses the allocated memory for sorting.
3. If the allocated memory is sufficient, the entire sort operation is performed in memory.
4. If the allocated memory is not enough, Oracle will spill the data to a temporary tablespace on disk and perform the sort operation there.

To set or modify the sort_area_size parameter, you can include it in the init.ora file or use the ALTER SYSTEM command:

ALTER SYSTEM SET sort_area_size = size_in_bytes SCOPE = 
{BOTH | MEMORY | SPFILE};

Keep in mind that setting the sort_area_size too small may lead to increased disk I/O and reduced performance, while setting it too large may lead to inefficient use of memory resources. It's important to monitor the system's performance and tune the sort_area_size accordingly to achieve optimal performance.
Note that in more recent versions of Oracle Database (Oracle 9i and later), the sort_area_size parameter has been replaced by the pga_aggregate_target parameter, which provides more granular control over memory allocation for different SQL operations. If you're working with a more recent version of Oracle, consider using pga_aggregate_target instead.

Because Oracle GoldenGate replicates only committed transactions, it stores the operations of each transaction in a managed virtual-memory pool known as a cache until it receives either a commit or a rollback for that transaction. One global cache operates as a shared resource of an Extract or Replicat process. The Oracle GoldenGate cache manager takes advantage of the memory management functions of the operating system to ensure that Oracle GoldenGate processes work in a sustained and efficient manner. The CACHEMGR parameter controls the amount of virtual memory and temporary disk space that is available for caching uncommitted transaction data that is being processed by Oracle GoldenGate. When a process starts, the cache manager checks the availability of resources for virtual memory, as shown in the following example:

CACHEMGR virtual memory values (may have been adjusted)CACHESIZE:
32GCACHEPAGEOUTSIZE (normal): 8M PROCESS VM AVAIL FROM OS (min): 63.97GCACHESIZEMAX
(strict force to disk): 48G

2013-11-11 14:16:22 WARNING OGG-01842 CACHESIZE PER DYNAMIC DETERMINATION (32G) LESS
THAN RECOMMENDED: 64G (64bit system)vm found: 63.97GCheck swap space.
Recommended swap/extract: 128G (64bit system).

If the system exhibits excessive paging and the performance of critical processes is affected, you can reduce the CACHESIZE option of the CACHEMGR. parameter. You can also control the maximum amount of disk space that can be allocated to the swap directory with the CACHEDIRECTORY option.

Here are terms from this module that may be new to you:

1. memory paging: A operation system function where by the contents of RAM memory are temporarily moved into a "swap" disk device.
2. swap disk: Special disk reserved for memory paging operations.
3. virtual memory: Memory that is in excess of the physical RAM memory capacity of the database server.

The next module explores tuning for CPU usage.

To complete this module, click the Quiz link below to test your knowledge of OS memory.
OS-Memory - Quiz