首页 » ORACLE » 再续PGA 和max_map_count、_realfree_heap_pagesize

再续PGA 和max_map_count、_realfree_heap_pagesize

之前分享过《PGA 一点认识》《Know more about PGA_AGGREGATE_LIMIT 12c 19c》 看到限制PGA的参数有_PGA_MAX_SIZE、 _SMM_(PX)_MAX_SIZE、PGA_AGGREGATE_TARGET、PGA_AGGREGATE_LIMIT ,为了提高SQL性能手动增加_PGA_MAX_SIZE参数值,如在数据仓库环境可能更明显,最近看到一个案例在执行存储过程时,当把参数_PGA_MAX_SIZE 调到4GB以上时(default 2GB),依旧遇到了ora-4030 错误, trace 文件中显示上限达4GB的上限,Process Map Dump 达65537行。

在MOS中查找不难发现PLSQL Procedure Causing ORA-04030: (pga heap,control file i/o buffer) And ORA-04030: (koh-kghu sessi,pmuccst: adt/record) or ORA-04030: (koh-kghucall ,pmucalm coll) Errors (Doc ID 1325100.1) 匹配,提到了解决方案有2种
1,增加操作系统级page map的个数

more /proc/sys/vm/max_map_count
sysctl -w vm.max_map_count=262144 (for example)   -- add /etc/sysctl.conf
-- or --

2, 增加数据库级realfree heap pagesize大小,需要重启数据库
For versions 11.2.0.4 and lower:

_use_realfree_heap=TRUE
_realfree_heap_pagesize_hint = 262144

For 12.1 and higher:

_use_realfree_heap=TRUE
_realfree_heap_pagesize = 262144

检查默认配置

[oracle@oel7db1 ~]$ sysctl -a|grep max_map
vm.max_map_count = 65530

SQL> @pd realfree
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX NAME                                                     VALUE                          DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
       192    C0 _realfree_heap_max_size                                  32768                          minimum max total heap size, in Kbytes
       193    C1 _realfree_heap_pagesize                                  65536                          hint for real-free page size in bytes
       194    C2 _realfree_pq_heap_pagesize                               65536                          hint for pq real-free page size in bytes
       195    C3 _realfree_heap_mode                                      0                              mode flags for real-free heap
       196    C4 _use_realfree_heap                                       TRUE                           use real-free based allocator for PGA memory

[oracle@oel7db1 ~]$ echo 65536*65530/1024/1024|bc
4095

默认数据库realfree allocator pagesize 是 64 kB (65536), 操作系统vm.max_map_count默认是65530, 64*65530=4GB. 如果把realfree heap pagesize增加到256kB (262144) 上限就成了16GB.对于多租户修改CDB级参数即可,PDB继承。

解决问题一方面增加单个进程PGA上限,另一方面要检查为什么单个进程PGA使用这么高?是否是合理?当发现有些进程使用PGA或内存使用较高时,如11g, 12, 19c都存在的bug ASM的dia0/diag 进程内存较大时,检查方法:

column name format a25
column pname format a12
column "MegaBytes" format a10
set numwidth 6

select ss.sid, p.pid, p.pname, sn.name, round(ss.value/(1024 *1024))||'Mb' "MegaBytes"
from v$sesstat ss, v$statname sn, v$session s, v$process p
where s.paddr = p.addr
and sn.statistic# = ss.statistic#
and s.sid = ss.sid
and sn.name in ('session pga memory' , 'session pga memory max')
and p.pname='DIA0'
order by ss.value
/

oradebug setorapid xxx
oradebug unlimit
oradebug dump heapdump 1
oradebug tracefile_name

-- format trace file using heap.awk

查找内存使用相关的SQL和heap名称,查询是否有bug? 如上面提到的bug  diag/dia进程可以kill 自动重启进程释放内存。

在Linux系统中可以使用pmap查看Process Map 记录数,但是当我在RAC on  RHEL 7环境使用oracle用户用pmap查看server process(LOCAL=NO),提示无权限。如果使用strace跟踪是在read  /proc/PID/maps文件时. 查看文件系统权限时:

$ ls -l /proc/NNNN/
-r--r--r-- 1 root root 0 Aug  9 16:06 maps
-rw------- 1 root root 0 Aug  9 16:06 mem
...
-r--r--r-- 1 root root 0 Aug  9 16:06 smaps
-r--r--r-- 1 root root 0 Aug  9 16:06 stack
-r--r--r-- 1 root root 0 Aug  9 16:06 stat
-r--r--r-- 1 root root 0 Aug  9 16:06 statm
-r--r--r-- 1 root root 0 Aug  9 16:06 status

注意是-r-r-r的权限,单从文件权限看other是有read权限的,使用oracle用户cat maps文件无权限,如果尝cat status文件是可以,后来从官方《Permission denied when reading /proc/$PID/* owned by the user》 和stackoverflow 找到了答案。

It’s because the file permissions are not the only protection you’re encountering.

Those aren’t just regular text files on a file system, procfs is a window into process internals and you have to get past both the file permissions plus whatever other protections are in place.

The maps show potentially dangerous information about memory usage and where executable code is located within the process space. If you look into ASLR, you’ll see this was a method of preventing potential attackers from knowing where code was loaded and it wouldn’t make sense to reveal it in a world-readable entry in procfs.

This protection was added way back in 2007:

This change implements a check using “ptrace_may_attach” before allowing access to read the maps contents. To control this protection, the new knob /proc/sys/kernel/maps_protect has been added, with corresponding updates to the procfs documentation.

Within ptrace_may_attach() (actually within one of the functions it calls) lies the following code:

if (((current->uid != task->euid) ||
(current->uid != task->suid) ||
(current->uid != task->uid) ||
(current->gid != task->egid) ||
(current->gid != task->sgid) ||
(current->gid != task->gid)) && !capable(CAP_SYS_PTRACE))
return -EPERM;
so that, unless you have the same real user/group ID, saved user/group ID and effective user/group ID (i.e., no sneaky setuid stuff) and they’re the same as the user/group ID that owns the process, you’re not allowed to see inside that “file” (unless your process has the CAP_SYS_PTRACE capability of course).

文件权限只是控制文件读写的一方面,对于/proc文件系统中文件,出于安全原因,从其他进程内存读取有额外的权限检查, /proc/$PID/maps显示了有关内存使用情况,以及可执行代码在进程空间中的位置的潜在危险信息, 操作系统在内核级使用CAP(Capabilities)机制控制,对于内核调用时可以分类分别控制,如果文件没有相同的UID/GID,还要确保使用 $PID 的进程对核心转储功能没有限制,可以临时启用审计规则auditctl 调测。简而言之就是使用pmap跟踪程序时,尝试read  /proc/PID的文件时需要CAP_SYS_PTRACE Capabilities, oracle读取root owner的maps文件,可以通过以下方式授权:

su - root
# setcap cap_sys_ptrace=eip /usr/bin/pmap
打赏

, ,

对不起,这篇文章暂时关闭评论。