How to allocate memory to Oracle databases?

Once upon a time, the Network passed a rule to allocate memory to Oracle databases: allocate 80% of memory to Oracle, and allocate 80% of memory to Oracle's SGA, the remaining 20% is allocated to the PGA of Oracle. I remember that Tom once said something like this: if the setting of a parameter is the best for Oracle, Oracle will automatically set it to the default value. Obviously, Oracle's initial settings do not follow this rule in terms of memory allocation. That is to say, it proves that this rule has problems in some aspects.

Of course, most DBAs do not set memory parameters like this, but there are still many users who are not considering the memory allocation in Oracle.

First, let's see if 20% of the available memory is suitable for the operating system. For servers with 2 GB of memory (in reality there are many such machines), 20% means 400 M, and generally M is not enough for the operating system. For hosts with a particularly large memory, 20% is too large. For example, the following example shows the memory of a P595 instance:

View plaincopy to clipboardprint?

1. ========================================================== ==============|==============================
2. Memory overview | pages | megabytes
3. -------------------------------------------------- | ---------- | -----------
4. Total memory in system | 45875200 | 179200.00
5. Total memory in use | 34789026 | 135894.63
6. Free memory | 11086174 | 43305.36
7. ========================================================== ==============|==============================
8. Segment Overview | Pages | Megabytes
9. -------------------------------------------------- | --------- | -----------
10. Total segment id mempgs | 32618956 | 127417.79
11. Total fork tree segment pages | 2074 | 8.10
12. Total kernel segment id mempgs | 3594452 | 14040.82

====================================================|==========|===========Memory Overview                                     |    Pages |  Megabytes ----------------------------------------------------|----------|-----------Total memory in system                              | 45875200 |  179200.00     Total memory in use                             | 34789026 |  135894.63     Free memory                                     | 11086174 |   43305.36 ====================================================|==========|===========Segment Overview                                    |    Pages |  Megabytes ----------------------------------------------------|---------|-----------Total segment id mempgs                             | 32618956 |  127417.79     Total fork tree segment pages                   |     2074 |       8.10     Total kernel segment id mempgs                  |  3594452 |   14040.82 

This host has a total of 135 GB physical memory, GB, of which the kernel occupies 14 GB. The memory occupied by the kernel is less than 10% of the total memory.

The above data and descriptions only express this point of view. The reserved memory of the operating system needs to be pre-considered according to the actual situation, which includes the setting of Kernel Parameters of the operating system. For example, in the default settings under AIX, client and perm memory can occupy much more than 20% of the memory, and the default settings under the HP-UX, file Cache and Buf Cache may also occupy much more than 20% of the memory. Therefore, you must adjust the OS Kernel Parameters for databases in these environments. It is also a safe practice to retain at least 20% of OS memory usage.

In addition to the operating system, when allocating memory to Oracle, pay attention to the following important points, which are often ignored:

• Note the memory usage during peak business hours: the number of connections in a system I maintain is usually around 8000-, and the maximum number of connections reaches, that is, the connection limit is reached. Therefore, we need to reserve enough memory for peak business hours.
• For RAC databases, you need to consider the memory consumption when the connection and pressure are transferred to the nodes that continue to work when other nodes fail or are shut down for maintenance.
• Some people only consider the PGA memory used by the process during connection. There is a big misunderstanding here that a connection only uses the PGA memory. But there is also a very important memory usage, that is, the operating system memory occupied by the process itself, in addition to the PGA memory. The process itself has Code (which is usually shared in OS), stack, heap, and kernel memory usage. PGA is only part of the memory used by the process, and is even a small part in most cases. Tested in Oracle 10.2.0.4 for Aix, a idle connection is a connection that does nothing. The PGA occupies about KB, while the server process occupies 4-5 MB of space. During the test, the Library was just started and there was no load. In fact, it is observed that on a set of databases that have been running for a long time, the server process occupies between 9-10 MB. Of course, different systems, different configurations, Oracle process memory usage is different, interested friends can measure the Oracle process in the HP-UX and Linux memory usage.