Lesson 7

You should now have a firm grasp of the concepts behind virtual memory and its use in the UNIX environment. During this module, you learned to:

• Module Review 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

If the current resources are not sufficient, a message like the following may be returned:

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.

1. Explain virtual memory
2. Explain UNIX memory management
3. Check the vmstat run queue
4. Place statistics into Oracle tables
5. Reduce memory usage

Glossary

OS-Memory - Quiz