Shulou(Shulou.com)06/01 Report--

Recently encountered a problem that the RAC node hang caused the node to be restarted. Finally, after analysis, it is found that after a period of time, the system memory will be exhausted, the original 256g of memory, and finally only a few hundred megabytes.

1. The TOP output of the problem period shows that the memory is only 7 gigabytes, but it is not enough to analyze memory problems. In general, the SGA and PGA of Database are major memory users, so it is difficult to find out who uses memory the most in TOP.

Unless the memory usage of some processes is particularly obvious

~ Linux OSWbb v7.3.3zzz * * Tue Feb 21 00:00:10 CST 2017top-00:00:12 up 14:16, 10 users, load average: 2.97,2.31, 2.05Tasks: 3087 total, 11 running, 3076 sleeping, 0 stopped, 0 zombieCpu (s): 11.7%us, 2.8%sy, 0.0%ni, 83.7%id, 0.9%wa, 0.0%hi, 0.9%si, 0.0%stMem: 257948M total 250464M used, 7484M free, 113M buffersSwap: 65537M total, 0M used, 65537M free 59868M cachedPID USER PR NI VIRT RES SHR S% CPU% MEM TIME+ COMMAND1156 oracle 4232 568380 R 101 0.0 gzip20019 root RT 01.67 gzip20019 root RT 0308m 89m 57m S 13 0.0 249.96 osysmond.bin1160 oracle 11252 3492 836 R 9 0.00.17 top49793 oracle 200 128g 1.2g S 7 0.536V 00.74 oracle~

two。 Through AWR, you can see that the database is very busy.

3. However, the percentage of physical memory usage of Oracle is only 33%, which is not the host memory exhausted by oracle.

~ Memory Statistics Begin EndHost Mem (MB): 257948.4 257948.4SGA use (MB): 77824.0 77824.0PGA use (MB): 8938.9 6416.3% Host Mem used for SGA+PGA: 33.64 32.66 ~

4. Note: in general, all processes in Oracle, such as smon, pmon,lgwr, etc., will use SGA, PGA, and a small amount of memory for the process itself (this part is usually very small).

So, the 33% here can represent all the physical memory used by Oracle.

Of course, some bug situations, such as LMS abnormal memory usage, are a different matter.

Reference case: RAC: LMS uses huge memory (Doc ID 1954701.1) RAC LMS processes using huge PGA memory:SQL > select pid,spid,program,pga_used_mem,pga_alloc_mem,pga_freeable_mem,pga_max_mem from v$process where program like'% LMS%' PID SPID USER PROGRAM PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM-- -13 23698 oracle@grid06.prod.quova.com (LMS0) 1.0644E+10 1.6525E+10 0 1.6525E+1014 23702 oracle@grid06.prod.quova.com (LMS1) 1.0644E+10 1.6525E+10 0 1.6525E+1015 23706 oracle@grid06.prod.quova.com (LMS2) 1.0407E+10 1.6157E+10 0 1.6157E+1016 23710 oracle@grid06.prod.quova.com (LMS3) 1.0599E+10 1.6455E+10 0 1.6455E+10 involving BUG 16412220-DLM USES EXCEESIVE PGA SEND MBUFS AND NOT RELEASE BACK TO PGA MEMORY POOL

5. In the process of analysis, it is also confirmed that the largest memory ever used by PGA, you can see that the maximum PGA is 10 GB, which is very little corresponding to 256 GB of physical memory. It's not the problem.

Select * from dba_hist_pgastat SNAP_ID DBID INSTANCE_NUMBER NAME VALUE 1054 602741423 1 aggregate PGA target parameter 5.5835E+10 1054 602741423 1 aggregate PGA auto target 4.3041E+10 1054 602741423 1 global memory bound 1073741824 1054 602741423 1 total PGA inuse 8010343424 1054 602741423 1 total PGA allocated 9373099008 1054 602741423 1 maximum PGA allocated 1.0711E+10 1054 602741423 1 total freeable PGA memory 396361728 1054 602741423 1 process count 2232 1054 602741423 1 max processes count 3053 1054 602741423 1 PGA memory freed back to OS 6.3224E+11 1054 602741423 1 maximum PGA used for auto workareas 5028864 1054 602741423 1 maximum PGA used for manual workareas 542720 1054 602741423 1 bytes processed 1036738560 602741423 1 cache hit percentage 1054 602741423 1 recompute count (total) 7478 1055 602741423 2 aggregate PGA target parameter 4.8050E+10 1055 602741423 2 aggregate PGA auto target 3.7282E+10 1055 602741423 2 global memory bound 1073741824 1055 602741423 2 total PGA inuse 6643825664 1055 602741423 2 total PGA allocated 7995835392 1055 602741423 2 maximum PGA allocated 9677304832 1055 602741423 2 total freeable PGA memory 420085760 1055 602741423 2 process count 2107 1055 602741423 2 max processes count 2365 1055 602741423 2 PGA memory freed back to OS 8.2417E+11 1055 602741423 2 maximum PGA used for auto workareas 33622016 1055 602741423 2 maximum PGA used for manual workareas 542720 1055 602741423 2 bytes processed 1.3889E+10 1055 602741423 2 cache hit percentage 1055 602741423 2 recompute count (total) 8519Line 384: 997602741423 2 maximum PGA allocated 1.0699E+10Line 967: 998 602741423 2 maximum PGA allocated 1.0699E+10

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.