[20170208]关于pre_page_sga参数.txt
--//我感觉很奇怪,至少我以前也见过别人这样设置pre_page_sga=true,并没有出现这个问题.虽然我自己从来没有这么设置.
--//先看看官方的定义:
http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams201.htm#REFRN10174
PRE_PAGE_SGA determines whether Oracle reads the entire SGA into memory at instance startup. Operating system page table
entries are then prebuilt for each page of the SGA. This setting can increase the amount of time necessary for instance
startup, but it is likely to decrease the amount of time necessary for Oracle to reach its full performance capacity
after startup.
Note:
This setting does not prevent your operating system from paging or swapping the SGA after it is initially read into
memory.
PRE_PAGE_SGA can increase the process startup duration, because every process that starts must access every page in the
SGA. The cost of this strategy is fixed; however, you might simply determine that 20,000 pages must be touched every
time a process starts. This approach can be useful with some applications, but not with all applications. Overhead can
be significant if your system frequently creates and destroys processes by, for example, continually logging on and
logging off.
The advantage that PRE_PAGE_SGA can afford depends on page size. For example, if the SGA is 80 MB in size and the page
size is 4 KB, then 20,000 pages must be touched to refresh the SGA (80,000/4 = 20,000).
If the system permits you to set a 4 MB page size, then only 20 pages must be touched to refresh the SGA (80,000/4,000 =
20). The page size is operating system-specific and generally cannot be changed. Some operating systems, however, have a
special implementation for shared memory whereby you can change the page size.
--//按照上面的介绍,问题出在page size上,我猜测对方居然没有使用hugepage,这样页面表的array随进程启动将占用很大RES,还是通过测
--//试来说明问题.
1.环境:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
-------------------- ----------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> show parameter pre_page_sga
NAME TYPE VALUE
------------ -------- ------
pre_page_sga boolean FALSE
SYS@book> show parameter sga_max_size
NAME TYPE VALUE
------------- ------------ --------
sga_max_size big integer 608M
SYS@book> show sga
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
# top -U oracle
top - 08:44:56 up 426 days, 8 min, 1 user, load average: 0.25, 0.17, 0.15
Tasks: 238 total, 1 running, 237 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0%us, 0.1%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 132261196k total, 25569612k used, 106691584k free, 8350052k buffers
Swap: 31455264k total, 2772k used, 31452492k free, 14210976k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
36706 oracle -2 0 845m 15m 13m S 1.7 0.0 26:32.86 oracle
44457 oracle 20 0 12940 1168 772 R 0.7 0.0 0:00.04 top
28200 oracle 20 0 32596 11m 712 S 0.3 0.0 1:19.34 tmux
28201 oracle 20 0 70960 4408 1244 S 0.0 0.0 0:00.61 bash
33544 oracle 20 0 70992 4392 1240 S 0.0 0.0 0:00.38 bash
33709 oracle 20 0 70992 4424 1244 S 0.0 0.0 0:00.69 bash
34123 oracle 20 0 70992 4396 1240 S 0.0 0.0 0:00.40 bash
35328 oracle 20 0 146m 12m 9280 S 0.0 0.0 0:21.26 tnslsnr
36701 oracle 20 0 847m 17m 14m S 0.0 0.0 0:12.01 oracle
36703 oracle 20 0 845m 15m 13m S 0.0 0.0 0:14.24 oracle
36710 oracle 20 0 845m 15m 13m S 0.0 0.0 0:02.01 oracle
36712 oracle 20 0 845m 15m 13m S 0.0 0.0 0:03.95 oracle
36714 oracle 20 0 846m 21m 18m S 0.0 0.0 0:03.04 oracle
36716 oracle 20 0 846m 16m 13m S 0.0 0.0 0:39.16 oracle
36718 oracle 20 0 845m 15m 13m S 0.0 0.0 0:02.18 oracle
36720 oracle 20 0 854m 23m 14m S 0.0 0.0 0:04.33 oracle
36722 oracle 20 0 852m 22m 14m S 0.0 0.0 0:03.68 oracle
36724 oracle 20 0 852m 22m 14m S 0.0 0.0 0:03.67 oracle
36726 oracle 20 0 861m 17m 14m S 0.0 0.0 0:12.51 oracle
36728 oracle 20 0 846m 16m 14m S 0.0 0.0 0:24.02 oracle
36730 oracle 20 0 855m 28m 22m S 0.0 0.0 0:05.95 oracle
36732 oracle 20 0 847m 21m 18m S 0.0 0.0 0:00.97 oracle
36738 oracle 20 0 851m 16m 13m S 0.0 0.0 0:00.94 oracle
36740 oracle 20 0 850m 32m 30m S 0.0 0.0 0:04.28 oracle
36748 oracle 20 0 895m 57m 17m S 0.0 0.0 0:08.75 oracle
36750 oracle 20 0 879m 33m 17m S 0.0 0.0 0:17.52 oracle
36764 oracle 20 0 849m 30m 25m S 0.0 0.0 0:13.00 oracle
36795 oracle 20 0 845m 16m 14m S 0.0 0.0 0:02.38 oracle
37978 oracle 20 0 72052 3140 996 S 0.0 0.0 0:00.07 rlwrap
37979 oracle 20 0 72940 14m 9.9m S 0.0 0.0 0:00.07 sqlplus
37980 oracle 20 0 850m 31m 27m S 0.0 0.0 0:00.39 oracle
37989 oracle 20 0 848m 26m 22m S 0.0 0.0 0:06.20 oracle
37991 oracle 20 0 846m 20m 17m S 0.0 0.0 0:31.42 oracle
44237 oracle 20 0 70736 4064 1176 S 0.0 0.0 0:00.28 bash
44288 oracle 20 0 21512 972 732 S 0.0 0.0 0:00.00 tmux
44348 oracle 20 0 845m 16m 14m S 0.0 0.0 0:00.01 oracle
--注意看RES 基本在20M,VIRT 基本就是sga的大约在850M上下.
--转抄链接:https://blogs.oracle.com/Database4CN/entry/%E5%85%B3%E4%BA%8Esys_cpu_usage_100_%E9%97%AE%E9%A2%98%E7%9A%84%E5%88%86%E6%9E%90
接着man ps
VSZ: virtual memory usage of entire process. vm_lib + vm_exe + vm_data + vm_stack
RSS: resident set size, the non-swapped physical memory that a task has used (in kiloBytes). (alias rssize, rsz).
任何一个oracle进程的VSZ约等于SGA加上这个进程的PGA(实际上VSZ还包含一些kernel内存)，正常情况下一个进程的pga是很小的。以上
输出中VSZ没有改变，因此发生巨大变化的RSS申请的内存一定不是PGA而是SGA(因为如果增长的是PAG那么VSZ也会跟着增长)。
--//注:我的sga_max_size=608M.好像差距有点大...
=======
2.修改看看:
SYS@book> alter system set pre_page_sga=true scope=spfile;
System altered.
--//重新启动:
# top -U oracle
top - 08:54:16 up 426 days, 17 min, 1 user, load average: 0.18, 0.33, 0.23
Tasks: 241 total, 1 running, 240 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.3%us, 1.6%sy, 0.0%ni, 98.1%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 132261196k total, 25680412k used, 106580784k free, 8350104k buffers
Swap: 31455264k total, 2772k used, 31452492k free, 14249804k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
44594 oracle 20 0 12936 1168 764 R 3.8 0.0 0:00.03 top
44482 oracle -2 0 845m 15m 13m S 1.9 0.0 0:06.76 oracle
44492 oracle 20 0 846m 16m 13m S 1.9 0.0 0:00.18 oracle
28200 oracle 20 0 45292 24m 712 S 0.0 0.0 1:27.00 tmux
28201 oracle 20 0 70960 4408 1244 S 0.0 0.0 0:00.63 bash
33544 oracle 20 0 70992 4392 1240 S 0.0 0.0 0:00.40 bash
33709 oracle 20 0 70992 4424 1244 S 0.0 0.0 0:00.70 bash
34123 oracle 20 0 70992 4396 1240 S 0.0 0.0 0:00.40 bash
35328 oracle 20 0 146m 12m 9280 S 0.0 0.0 0:21.28 tnslsnr
44237 oracle 20 0 70736 4064 1176 S 0.0 0.0 0:00.28 bash
44288 oracle 20 0 21512 972 732 S 0.0 0.0 0:00.00 tmux
44472 oracle 20 0 69780 3084 940 S 0.0 0.0 0:00.03 rlwrap
44473 oracle 20 0 73080 14m 9.9m S 0.0 0.0 0:00.14 sqlplus
44478 oracle 20 0 847m 17m 14m S 0.0 0.0 0:00.23 oracle
44480 oracle 20 0 845m 15m 13m S 0.0 0.0 0:00.09 oracle
44486 oracle 20 0 845m 15m 13m S 0.0 0.0 0:00.02 oracle
44488 oracle 20 0 845m 15m 13m S 0.0 0.0 0:00.03 oracle
44490 oracle 20 0 846m 20m 17m S 0.0 0.0 0:00.04 oracle
44494 oracle 20 0 845m 15m 13m S 0.0 0.0 0:00.05 oracle
44496 oracle 20 0 852m 23m 14m S 0.0 0.0 0:00.03 oracle
44498 oracle 20 0 852m 22m 13m S 0.0 0.0 0:00.03 oracle
44500 oracle 20 0 852m 22m 13m S 0.0 0.0 0:00.03 oracle
44502 oracle 20 0 860m 16m 14m S 0.0 0.0 0:00.08 oracle
44504 oracle 20 0 846m 16m 14m S 0.0 0.0 0:00.12 oracle
44506 oracle 20 0 852m 25m 21m S 0.0 0.0 0:00.18 oracle
44508 oracle 20 0 846m 20m 17m S 0.0 0.0 0:00.02 oracle
44510 oracle 20 0 849m 32m 28m S 0.0 0.0 0:00.40 oracle
44512 oracle 20 0 846m 17m 14m S 0.0 0.0 0:00.17 oracle
44514 oracle 20 0 851m 15m 12m S 0.0 0.0 0:00.02 oracle
44516 oracle 20 0 846m 14m 12m S 0.0 0.0 0:00.02 oracle
44522 oracle 20 0 851m 35m 29m S 0.0 0.0 0:00.61 oracle
44524 oracle 20 0 895m 41m 17m S 0.0 0.0 0:00.13 oracle
44526 oracle 20 0 879m 19m 16m S 0.0 0.0 0:00.11 oracle
44540 oracle 20 0 849m 25m 23m S 0.0 0.0 0:00.22 oracle
44541 oracle 20 0 150m 84m 896 S 0.0 0.1 0:07.15 rlwrap
44545 oracle 20 0 72060 13m 9732 S 0.0 0.0 0:08.58 sqlplus
44546 oracle 20 0 849m 30m 26m S 0.0 0.0 0:00.85 oracle
44589 oracle 20 0 845m 15m 13m S 0.0 0.0 0:00.02 oracle
44591 oracle 20 0 846m 19m 16m S 0.0 0.0 0:00.02 oracle
--//注意看RES列,依旧20M上下,并没有出现对方机器这么大的变化.
3.继续测试:
--我的测试机器虽然内存很小,但是使用hugepage的.
$ cat /proc/meminfo | grep -i huge
AnonHugePages: 26624 kB
HugePages_Total: 305
HugePages_Free: 98
HugePages_Rsvd: 98
HugePages_Surp: 0
Hugepagesize: 2048 kB
--//关闭hupegae的使用看看.
SYS@book> alter system set use_large_pages=false scope=spfile;
System altered.
--//重新启动数据库:
# top -U oracle
top - 09:06:54 up 426 days, 30 min, 1 user, load average: 0.03, 0.11, 0.18
Tasks: 243 total, 1 running, 242 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0%us, 0.0%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 132261196k total, 26258372k used, 106002824k free, 8350116k buffers
Swap: 31455264k total, 2772k used, 31452492k free, 14870280k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
44606 oracle -2 0 845m 611m 609m S 1.3 0.5 0:11.56 oracle
44724 oracle 20 0 12940 1188 772 R 0.7 0.0 0:00.05 top
28200 oracle 20 0 45308 24m 712 S 0.3 0.0 1:27.52 tmux
44620 oracle 20 0 852m 620m 610m S 0.3 0.5 0:00.27 oracle
28201 oracle 20 0 70960 4408 1244 S 0.0 0.0 0:00.63 bash
33544 oracle 20 0 70992 4392 1240 S 0.0 0.0 0:00.41 bash
33709 oracle 20 0 70992 4424 1244 S 0.0 0.0 0:00.71 bash
34123 oracle 20 0 70992 4396 1240 S 0.0 0.0 0:00.40 bash
35328 oracle 20 0 146m 12m 9280 S 0.0 0.0 0:21.29 tnslsnr
44237 oracle 20 0 70736 4064 1176 S 0.0 0.0 0:00.28 bash
44288 oracle 20 0 21512 972 732 S 0.0 0.0 0:00.00 tmux
44472 oracle 20 0 71868 3144 996 S 0.0 0.0 0:00.05 rlwrap
44473 oracle 20 0 73080 14m 9m S 0.0 0.0 0:00.26 sqlplus
44602 oracle 20 0 847m 613m 611m S 0.0 0.5 0:00.54 oracle
44604 oracle 20 0 845m 611m 609m S 0.0 0.5 0:00.37 oracle
44610 oracle 20 0 845m 611m 609m S 0.0 0.5 0:00.25 oracle
44612 oracle 20 0 845m 611m 609m S 0.0 0.5 0:00.26 oracle
44614 oracle 20 0 846m 616m 614m S 0.0 0.5 0:00.28 oracle
44616 oracle 20 0 846m 612m 609m S 0.0 0.5 0:00.51 oracle
44618 oracle 20 0 845m 611m 609m S 0.0 0.5 0:00.29 oracle
44622 oracle 20 0 852m 618m 610m S 0.0 0.5 0:00.15 oracle
44624 oracle 20 0 852m 618m 610m S 0.0 0.5 0:00.24 oracle
44626 oracle 20 0 860m 620m 617m S 0.0 0.5 0:00.36 oracle
44628 oracle 20 0 846m 613m 610m S 0.0 0.5 0:00.42 oracle
44630 oracle 20 0 852m 622m 618m S 0.0 0.5 0:00.47 oracle
44632 oracle 20 0 846m 616m 614m S 0.0 0.5 0:00.22 oracle
44634 oracle 20 0 849m 629m 625m S 0.0 0.5 0:00.60 oracle
44636 oracle 20 0 846m 614m 610m S 0.0 0.5 0:00.53 oracle
44638 oracle 20 0 851m 611m 609m S 0.0 0.5 0:00.25 oracle
44640 oracle 20 0 846m 610m 608m S 0.0 0.5 0:00.25 oracle
44647 oracle 20 0 851m 631m 626m S 0.0 0.5 0:00.95 oracle
44649 oracle 20 0 895m 637m 613m S 0.0 0.5 0:00.29 oracle
44651 oracle 20 0 879m 616m 613m S 0.0 0.5 0:00.38 oracle
44665 oracle 20 0 849m 622m 619m S 0.0 0.5 0:00.39 oracle
44670 oracle 20 0 69812 2936 852 S 0.0 0.0 0:00.02 rlwrap
44671 oracle 20 0 72292 13m 9660 S 0.0 0.0 0:00.05 sqlplus
44672 oracle 20 0 847m 619m 616m S 0.0 0.5 0:00.25 oracle
44716 oracle 20 0 845m 611m 609m S 0.0 0.5 0:00.21 oracle
44718 oracle 20 0 845m 615m 613m S 0.0 0.5 0:00.22 oracle
44720 oracle 20 0 847m 615m 612m S 0.0 0.5 0:00.26 oracle
44722 oracle 20 0 845m 611m 609m S 0.0 0.5 0:00.24 oracle
--// 再看RES列,清晰明了地显示达到61XM.再次说明采用hugepage的好处.
4.继续测试:
--//取消pre_page_sga设置,还原为false:
SYS@book> alter system reset pre_page_sga;
System altered.
--//重新启动数据库,注意use_large_pages=false:
top - 09:20:20 up 426 days, 43 min, 1 user, load average: 0.28, 0.19, 0.16
Tasks: 239 total, 1 running, 238 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.3%us, 1.6%sy, 0.0%ni, 98.1%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 132261196k total, 25837324k used, 106423872k free, 8350124k buffers
Swap: 31455264k total, 2772k used, 31452492k free, 14477880k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
44857 oracle 20 0 12936 1164 764 R 3.8 0.0 0:00.03 top
44781 oracle -2 0 845m 16m 14m S 1.9 0.0 0:02.23 oracle
28200 oracle 20 0 45320 24m 712 S 0.0 0.0 1:28.36 tmux
28201 oracle 20 0 70960 4408 1244 S 0.0 0.0 0:00.63 bash
33544 oracle 20 0 70992 4392 1240 S 0.0 0.0 0:00.43 bash
33709 oracle 20 0 70992 4424 1244 S 0.0 0.0 0:00.72 bash
34123 oracle 20 0 70992 4396 1240 S 0.0 0.0 0:00.40 bash
35328 oracle 20 0 146m 12m 9280 S 0.0 0.0 0:21.31 tnslsnr
44237 oracle 20 0 70736 4064 1176 S 0.0 0.0 0:00.28 bash
44288 oracle 20 0 21512 972 732 S 0.0 0.0 0:00.00 tmux
44472 oracle 20 0 71868 3144 996 S 0.0 0.0 0:00.06 rlwrap
44473 oracle 20 0 73080 14m 9m S 0.0 0.0 0:00.39 sqlplus
44777 oracle 20 0 847m 19m 17m S 0.0 0.0 0:00.04 oracle
44779 oracle 20 0 845m 16m 14m S 0.0 0.0 0:00.04 oracle
44785 oracle 20 0 845m 16m 14m S 0.0 0.0 0:00.01 oracle
44787 oracle 20 0 845m 16m 14m S 0.0 0.0 0:00.01 oracle
44789 oracle 20 0 846m 26m 23m S 0.0 0.0 0:00.04 oracle
44791 oracle 20 0 846m 20m 17m S 0.0 0.0 0:00.06 oracle
44793 oracle 20 0 845m 27m 25m S 0.0 0.0 0:00.05 oracle
44795 oracle 20 0 852m 24m 15m S 0.0 0.0 0:00.03 oracle
44797 oracle 20 0 851m 23m 14m S 0.0 0.0 0:00.02 oracle
44799 oracle 20 0 851m 24m 14m S 0.0 0.0 0:00.02 oracle
44801 oracle 20 0 860m 25m 23m S 0.0 0.0 0:00.04 oracle
44803 oracle 20 0 846m 21m 19m S 0.0 0.0 0:00.05 oracle
44805 oracle 20 0 848m 51m 46m S 0.0 0.0 0:00.10 oracle
44807 oracle 20 0 846m 22m 20m S 0.0 0.0 0:00.02 oracle
44809 oracle 20 0 849m 59m 55m S 0.0 0.0 0:00.37 oracle
44811 oracle 20 0 846m 22m 19m S 0.0 0.0 0:00.07 oracle
44813 oracle 20 0 851m 16m 13m S 0.0 0.0 0:00.01 oracle
44815 oracle 20 0 846m 15m 13m S 0.0 0.0 0:00.01 oracle
44821 oracle 20 0 851m 83m 78m S 0.0 0.1 0:00.66 oracle
44823 oracle 20 0 927m 50m 18m S 0.0 0.0 0:00.12 oracle
44825 oracle 20 0 878m 21m 17m S 0.0 0.0 0:00.07 oracle
44839 oracle 20 0 850m 38m 35m S 0.0 0.0 0:00.19 oracle
44842 oracle 20 0 69740 2960 892 S 0.0 0.0 0:00.03 rlwrap
44846 oracle 20 0 72060 13m 9560 S 0.0 0.0 0:00.03 sqlplus
44847 oracle 20 0 849m 43m 39m S 0.0 0.0 0:00.20 oracle
--//从这里看RES比前面的大.
--总结:
1.以上测试仅仅基于我的猜测.也许不对.
2.从测试看设置pre_page_sga=true,注意是否采用hugepage.我个人不建议设置.
3.或者可能改动了什么比如增加sga大小,导致无法使用hugepage. 10.2.03 不像11G,设置的页面数量要大于需要量.
