玉面飞龙的BLOG

_use_realfree_heap

December 21st, 2008 | Categories: Boring | Tags: Oracle, Oracle interal

这个9i就有的改进现在才注意到。

NAME                          　　　　　　　 DESCRIPTION

——————————————————————
_realfree_heap_free_threshold 　　 threshold for performing real-free, in Kbytes
_realfree_heap_max_size       　　　 minimum max total heap size, in Kbytes
_realfree_heap_mode              　　     mode flags for real-free heap
_use_realfree_heap use real-free based allocator for PGA memory

Oracle8i的时候,Dedicate模式中Session的UGA和CGA在PGA Heap中通过brk调用作为subheap分配.Session因为执行复杂操作而占用的UGA和CGA内存部分，除非PGA Heap被释放，否则不能够释放给OS。在过去，经常看到这些言论，说什么UGA占用的内存不能释放给OS，只能在系统内存紧张的时候被swap out出去 —- 已经是过去时了。

Oracle9i做了改进，引进_use_realfree_heap参数。当9i使用pga_aggregate_target=true的时候_use_realfree_heap也为TRUE.10g中该参数默认为True.

差别就是原先一个进程只有一个PGA heap,包括了uga,cga等subheap。Enable这个参数后，一个进程地址空间内有多个heap,包括PGA Heap, UGA Heap, CGA heap.这样当user call执行完毕后，该user call所占用的CGA heap就可以返回给OS. 具体的pga heap dump例子可以看这里。

可以测试一下，看对进程占用内存的影响。

_use_realfree_heap=FALSE

Session执行以下占用内存比较多的SQL.

SQL> select count(*) from dual connect by level <=10000000;

可以看到free memory (solaris8+ vmstat “free” includes: freelist + cachelist)逐渐减少

/export/home/oracle > vmstat -p 1
memory           page          executable      anonymous      filesystem
swap free re mf fr de sr epi epo epf api apo apf fpi fpo fpf
15476088 2147488 57 193 40 0   2   31    0    0    0    1    1   23   39   39
12975208 4048640 0 0   0   0   0    0    0    0    0    0    0    0    0    0
12872544 3945888 0 0   0   0   0    0    0    0    0    0    0    0    0    0
12872544 3845896 0 0   0   0   0    0    0    0    0    0    0    0    0    0
12749584 3722864 0 0   0   0   0    0    0    0    0    0    0    0    0    0
12747720 3621000 0 0   0   0   0    0    0    0    0    0    0    0    0    0
12526592 3596424 414 421 0 0   0    0    0    0    0    0    0    0    0    0
12526592 3599640 0 1   0   0   0    0    0    0    0    0    0    0    0    0

然后Cancel掉

^Cselect count(*) from dual connect by level <=10000000
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

可以看到vmstat.free无变化,虽然我们cancel了SQL,但session的cga仍然没有释放给OS.

memory           page          executable      anonymous      filesystem
swap free re mf fr de sr epi epo epf api apo apf fpi fpo fpf
15476384 2147264 57 193 40 0   2   31    0    0    0    1    1   23   39   39
12526592 3596424 414 421 0 0   0    0    0    0    0    0    0    0    0    0
12526592 3599640 0 1   0   0   0    0    0    0    0    0    0    0    0    0

然后session 退出

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

可用内存增加,为进程退出将pga heap(包括uga&cga subheap)释放给OS。

memory           page          executable      anonymous      filesystem
swap free re mf fr de sr epi epo epf api apo apf fpi fpo fpf
15476384 2147264 57 193 40 0   2   31    0    0    0    1    1   23   39   39
12526592 3596424 414 421 0 0   0    0    0    0    0    0    0    0    0    0
12526592 3599640 0 1   0   0   0    0    0    0    0    0    0    0    0    0
12528448 3601496 1 0   7   0   0    0    0    0    0    0    0    0    7    7
12528448 3601496 0 2   0   0   0    0    0    0    0    0    0    0    0    0
12983840 4054784 0 1   0   0   0    0    0    0    0    0    0    0    0    0
12976224 4045968 870 4450 0 0 0    0    0    0    0    0    0    0    0    0
12978928 4048960 688 3529 0 0 0    0    0    0    0    0    0    0    0    0
12983840 4054776 0 0   0   0   0    0    0    0    0    0    0    0    0    0

将参数设置为TRUE

SQL> alter system set “_use_realfree_heap”=true;
System altered.

Then exit and re-connect

SQL> select count(*) from dual connect by level <=10000000;
^C select count(*) from dual connect by level <=10000000
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

可以看到SQL取消后,进程还未退出，系统free memory内存马上回升。因为Oracle在user call结束后将CGA heap释放给OS了。

memory           page          executable      anonymous      filesystem
swap free re mf fr de sr epi epo epf api apo apf fpi fpo fpf
12848600 3922840 0 4371 0 0   0    0    0    0    0    0    0    0    0    0
12796376 3870592 0 4361 0 0   0    0    0    0    0    0    0    0    0    0
12744856 3819128 0 4314 0 0   0    0    0    0    0    0    0    0    0    0
12709784 3784080 0 4322 0 0   0    0    0    0    0    0    0    0    0    0
12639960 3714232 86 5168 0 0   0    0    0    0    0    0    0    0    0    0
12603952 3678552 198 6115 0 0 0    0    0    0    0    0    0    0    0    0
12570648 3644872 152 3310 0 0 0    0    0    0    0    0    0    0    0    0
12557592 3631888 604 2962 0 0 0    0    0    0    0    0    0    0    0    0
12557320 3631616 38 375 0 0   0    0    0    0    0    0    0    0    0    0
12955152 4029712 825 2644 0 0 0    0    0    0    0    0    0    0    0    0
12946352 4018648 248 2544 0 0 0    0    0    0    0    0    0    0    0    0

这个改进可以避免session PGA不必要的page out,并且使用更轻量级的系统调用mmap分配内存。

Good to know it.