Lesson 1

Another very important component of the external environment when tuning Oracle system is the memory consumption within your Oracle database server. Many DBAs falsely assume that the only memory they need to worry about is the memory that is allocated to their SGA when they create the Oracle instance. However, an experienced DBA understands that there are additional memory demands upon the database server, even one that is totally dedicated to the Oracle database. We have to deal with the demands of PGA memory for all of the connected users as well as dealing with memory demands for other components of the Oracle database, including the database listener, multithreaded server, and all of the other components that are running within your database server environment. In this module were going to be looking at the generic tool called vmstat that will actually measured the amount of paging and swapping all OS memory within your Oracle instance.

In this module, you will delve into managing the RAM memory in the OS environment.
Memory has a huge impact on performance. Because of this, Oracle allocates a region of RAM memory, called the SGA, at database startup time. Each connected user process causes the database server to experience Oracle-related memory demands. Therefore, you must understand a range of memory management techniques. By the end of this module, you should be able to:

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

The next lesson is an overview of OS memory usage.

vmstat is a Unix/Linux utility that provides information about the system's virtual memory and CPU usage. It can be used to measure the amount of paging and swapping of operating system (OS) memory within an Oracle instance.
To measure paging and swapping, you can use the following fields in the vmstat output:

1. pi (page in): This field shows the number of pages swapped in from disk per second. A high value for pi indicates that the system is swapping a lot of memory to disk, which can lead to performance degradation.
2. po (page out): This field shows the number of pages swapped out to disk per second. A high value for po indicates that the system is paging out frequently, which can also lead to performance degradation.
3. swpd (swapped memory): This field shows the amount of virtual memory used in kilobytes. A high value for swpd indicates that the system is using a lot of virtual memory and may be swapping a significant amount of memory to disk.
4. free: This field shows the amount of free memory available in kilobytes. A low value for free indicates that the system is running low on memory and may be paging or swapping frequently.

By analyzing the values of these fields over time, you can get a sense of how much paging and swapping is occurring in your Oracle instance. If you see high values for pi, po, or swpd, it may be necessary to add more physical memory to your system to reduce the amount of paging and swapping that is occurring.

When running an Oracle 19c database, there are several aspects of memory consumption to monitor to ensure optimal performance. These aspects include both system memory (RAM) and Oracle's memory components. Below are the key areas to consider:

1. System Global Area (SGA): The SGA is a group of shared memory structures that contain data and control information for the Oracle instance. It is the primary memory area used by the database. It is essential to monitor the following components:
   • Database Buffer Cache: Stores copies of data blocks retrieved from the database. Larger buffer caches can improve performance by reducing physical I/O operations.
   • Shared Pool: Holds parsed SQL statements, PL/SQL code, and data dictionary information. An under-allocated shared pool can lead to higher parsing times and slower queries.
   • Large Pool: Used for large memory allocations, such as backup and recovery operations, shared server processes, and parallel execution messages.
   • Java Pool: Used for Java-based objects if your database applications use Java in the database.
   • Streams Pool: Used if you are using Oracle Streams or GoldenGate replication.
   
   Proper sizing of the SGA Target is critical, and Oracle's Automatic Memory Management (AMM) can be enabled to help manage and dynamically adjust the SGA size.
2. Program Global Area (PGA): The PGA is a non-shared memory area used by each individual server process. It stores session-specific information, such as:
   • Private SQL Areas: Memory areas for executing SQL statements and storing execution plans.
   • Sort Space: Memory used for sorting operations or hash joins.
   
   The total size of the PGA is controlled by the PGA_AGGREGATE_TARGET parameter, and Oracle can dynamically allocate memory to it.
3. Memory Target and Memory Max Target: If Automatic Memory Management (AMM) is enabled, the parameters MEMORY_TARGET and MEMORY_MAX_TARGET control the overall memory allocation for both SGA and PGA. Oracle will automatically balance memory between SGA and PGA depending on system needs.
4. Operating System Memory: Besides Oracle's internal memory management, it's crucial to monitor the overall system memory usage:
   • Ensure enough free physical memory to avoid excessive paging or swapping, which can severely degrade database performance.
   • Oracle recommends having at least 8 GB of physical RAM for production installations, but actual needs depend on the workload.
   • Check memory allocation to other services running on the same machine to avoid contention for memory resources.
5. Memory Advisors: Oracle provides memory advisors like the SGA Advisor and PGA Advisor to help monitor and optimize memory allocation. These tools can assist in identifying under- or over-allocated memory areas.
6. Real Application Clusters (RAC): If you are using Oracle RAC, memory consumption will increase as each instance in the cluster needs its own SGA and PGA. Additionally, the Global Cache Service (GCS) and Global Enqueue Service (GES) will require memory to manage inter-instance communication.
7. Process Memory Consumption: Each user session and background process consumes memory. For large multi-user databases, the memory consumed by each Oracle process can add up, and it's necessary to monitor the number of concurrent sessions.

Summary of Key Parameters:

• SGA_TARGET: Controls the total size of the SGA.
• PGA_AGGREGATE_TARGET: Controls the total size of the PGA.
• MEMORY_TARGET and MEMORY_MAX_TARGET: Control the total memory allocated to both SGA and PGA when using AMM.
• Use Oracle's memory advisors to optimize memory allocation dynamically.

Monitoring these components and tuning them as needed can help ensure that Oracle 19c performs well without overconsuming system resources.