Once upon a time, on the network to allocate memory to the Oracle database is one of the law: give Oracle USES 80% of the memory allocation, and the 80% 80% of the memory allocation to Oracle SGA, the remaining 20% for the Oracle of the PGA, remember that Tom had said something like that: if a parameter setting is best for Oracle, Oracle will automatically set to its default value, obviously, on this matter on memory allocation, Oracle's initial setup is not according to this rule, so from one hand to prove the law problems,
, of course, most of the DBA do not set the memory parameters, but also have a lot of people in the memory allocation in Oracle thoughtless,
Experts answer
First of all, let's look at the available memory 20% reserved for operating system, whether it's appropriate for server 2 gb of memory (in reality such a machine), 20% means that 400 m, usually 400 m for the operating system is not enough to use, and for the host memory much more special, 20% is too much, such as the following is a memory from a P595:
====================================================|==========|===========
The Memory the Overview | Pages | Megabytes
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - | -- -- -- -- -- -- -- -- -- - | -- -- -- -- -- -- -- -- -- -- --
The Total memory in the system | 45875200 |
179200.00The Total memory in use | 34789026 |
135894.63Free the memory | 11086174 |
43305.36====================================================|==========|===========
Segment the Overview | Pages | Megabytes
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - | -- -- -- -- -- -- -- -- - | -- -- -- -- -- -- -- -- -- -- --
Total segment id mempgs | 32618956 |
127417.79Total fork tree segment pages | 2074 |
8.10Total kernel segment id mempgs | 3594452 |
14040.82This host for a total of 179 gb of physical memory, has been used 135 g, in which the kernel takes up 14 g, kernel memory less than 10% of the total memory,
Data and instructions for the above, just express such a viewpoint, for reserved memory operating system, the need to consider in advance according to actual condition, this includes the operating system kernel parameter Settings, such as the default Settings under AIX, client and perm memory can occupy more than 20% of the memory, and HP - UX under the default Settings, File Cache and Buf Cache may also take up far more than 20% of the memory, so for these environmental database, must pay attention to adjust the OS kernel parameter, for memory use OS, keep at least 20% also can yet be regarded as a kind of safe practices,
In addition to the operating system, the block to Oracle when allocating memory, also need to note the following important points, these points often being ignored:
Note business peak memory usage: I maintain a set of system, at ordinary times the number of connections is usually around 5000-5500, and the highest number of connections has reached $8000, which is arrived at the connection of the ceiling to forget about it, therefore, we need to have enough memory for business peak,
For RAC database, need to consider to other node failure or downtime, connection and pressure is transferred to continue working node memory consumption,
Some people only consider process is used to connect the PGA memory, there is a big misunderstanding, is that a connection, can only use PGA memory, but there is a very important memory usage, that is the operating system process itself takes up memory, in addition to the PGA memory, process itself has a code (this is usually a share in the OS), stack, heap, must have the kernel memory footprint, PGA just part of the memory process USES, even in most cases is only a small part, in Oracle 10.2.0.4 for tested under AIX, a free connection, a connection is nothing nothing, PGA occupies about 500 k, and the server process between process load within 4 to 5 m, the library has just started, when testing without any load, in fact, according to the observation in a long time on the base of operation, the server process takes up within the existing between 9 to 10 m, of course, different systems, different configuration, Oracle processes are different memory, interested friends can measure the Oracle process under the HP - UX and LINUX memory footprint,
For SGA components within the segment and PGA size Settings, online a lot of related articles for reference, this article is no longer involved, but my personal view is that the adjustment of the parameters is not a one-off things, need according to the system is running on the analysis of performance data to adjust, until to reach optimization,
CodePudding user response:
80% of the memory allocated to the Oracle usingThis ratio is high, the setting of 50% to 60% more see,
but my personal view is that the adjustment of the parameters is not a one-off things, need according to the system is running on the analysis of performance data to adjust, until to reach optimization, this praise!
CodePudding user response:
10 g SGA_TARGET is automatically assigned, temporary distribution system online reference, according to an online business,