[20181109]12cR2 的pre_page_sga引數.txt

--//12CR2改變了引數pre_page_sga設定為True.設定為true有好處也有缺點.

--//先看看官方的定義:

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.

--//可以發現使用PRE_PAGE_SGA=true,僅僅在啟動是讀整個SGA進入記憶體,如果你設定很大,現在的伺服器記憶體上幾百G很正常,啟動就慢很多,

--//但是能很快達到最佳效能.但是它並不能避免交換使用.

--//如果你仔細讀後面的資訊,可以發現PRE_PAGE_SGA=true的情況設定使用hugepages非常必要.

--//這也是我為什麼強調安裝oracle必須配置hugepags作為必要的步驟,現在許多安裝人員基本不做,而業務開展以後要改也帶來不必要的

--//麻煩,畢竟要停業務.

1.環境:

SQL> select BANNER_FULL from v$version;

BANNER_FULL

--------------------------------------------------------------------------------

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

--//辦公環境沒有12c,那18c來說明問題一樣的.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

------ -------- ---------- ----------

2 PDB$SEED READ ONLY NO

3 ORCL READ WRITE NO

SQL> show parameter pre_page_sga

NAME TYPE VALUE

------------- ----------- ------------------------------

pre_page_sga boolean TRUE

SQL> show spparameter pre_page_sga

SID NAME TYPE VALUE

-------- ----------------------------- ----------- ----------------------------

* pre_page_sga boolean

--//引數pre_page_sga沒有寫入spfile引數檔案,12CR2以上預設設定為True.oracle 之所以這樣設定主要考慮PDB的原因(我個人人為)

2.看看記憶體使用情況:

# cat /proc/meminfo | grep -i page

AnonPages: 2973340 kB

PageTables: 803364 kB

AnonHugePages: 0 kB

HugePages_Total: 0

HugePages_Free: 0

HugePages_Rsvd: 0

HugePages_Surp: 0

Hugepagesize: 2048 kB

--//頁面表消耗800M.

# ps -ef | grep orclcd[b]|wc

85 684 5753

--//僅僅85個程式.

# top -u oracle

--//按F,移動到RES列,選擇s,改變排序模式.這樣按照RES排序.

top - 08:58:20 up 70 days, 5:06, 3 users, load average: 1.02, 1.04, 1.05

Tasks: 541 total, 1 running, 540 sleeping, 0 stopped, 0 zombie

%Cpu(s): 0.2 us, 0.1 sy, 0.0 ni, 99.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st

KiB Mem : 65709508 total, 8128052 free, 6214192 used, 51367264 buff/cache

KiB Swap: 16773116 total, 16773116 free, 0 used. 30169176 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

12970 oracle 20 0 29.2g 11.3g 11.2g S 0.0 18.0 12:10.07 ora_dbw0_orclcd

14369 oracle 20 0 29.2g 3.2g 3.1g S 0.0 5.1 3:24.60 ora_w00b_orclcd

13094 oracle 20 0 29.3g 3.0g 2.9g S 0.0 4.7 4:29.54 ora_w003_orclcd

14420 oracle 20 0 29.2g 2.6g 2.5g S 0.0 4.1 4:17.85 ora_w00f_orclcd

13065 oracle 20 0 29.2g 2.2g 2.2g S 0.0 3.6 20:24.95 ora_p002_orclcd

13067 oracle 20 0 29.2g 2.2g 2.1g S 0.0 3.5 21:26.00 ora_p003_orclcd

14333 oracle 20 0 29.2g 2.1g 2.0g S 0.0 3.3 2:26.60 ora_w005_orclcd

14342 oracle 20 0 29.2g 2.1g 2.0g S 0.0 3.3 2:27.30 ora_w007_orclcd

14365 oracle 20 0 29.2g 2.0g 1.9g S 0.0 3.2 2:28.03 ora_w00a_orclcd

14415 oracle 20 0 29.2g 1.9g 1.8g S 0.0 3.0 2:12.27 ora_w00e_orclcd

12986 oracle 20 0 29.2g 1.9g 1.8g S 0.0 3.0 2:56.72 ora_w000_orclcd

13308 oracle 20 0 29.2g 1.8g 1.8g S 0.0 2.9 110:41.60 ora_cjq0_orclcd

13061 oracle 20 0 29.2g 1.8g 1.7g S 0.0 2.8 24:00.66 ora_p000_orclcd

14360 oracle 20 0 29.2g 1.8g 1.7g S 0.0 2.8 2:16.13 ora_w009_orclcd

13063 oracle 20 0 29.3g 1.7g 1.6g S 0.0 2.7 23:02.60 ora_p001_orclcd

14410 oracle 20 0 29.2g 1.6g 1.5g S 0.0 2.5 1:45.68 ora_w00d_orclcd

14337 oracle 20 0 29.2g 1.6g 1.5g S 0.0 2.5 2:59.62 ora_w006_orclcd

12948 oracle 20 0 29.1g 1.5g 1.5g S 0.0 2.4 1:33.42 ora_mman_orclcd

13051 oracle 20 0 29.2g 1.4g 1.4g S 0.0 2.2 30:11.14 ora_imco_orclcd

14403 oracle 20 0 29.2g 1.3g 1.2g S 0.0 2.0 2:07.15 ora_w00c_orclcd

13000 oracle 20 0 29.2g 1.2g 1.2g S 0.0 2.0 25:06.94 ora_mmon_orclcd

13073 oracle 20 0 29.2g 1.1g 1.0g S 0.0 1.7 5:34.12 ora_p006_orclcd

13075 oracle 20 0 29.1g 1.1g 1.0g S 0.0 1.7 5:35.03 ora_p007_orclcd

13099 oracle 20 0 29.2g 1.0g 1.0g S 0.0 1.6 1:24.58 ora_w004_orclcd

...

--//你可以發現一些oracle相關程式RES佔用很大的記憶體.

--//貼一個我們生產系統(exadata的情況): pre_page_sga=false.

# cat /proc/meminfo | grep -i page

AnonPages: 22546648 kB

PageTables: 1593420 kB

HugePages_Total: 78600

HugePages_Free: 3961

HugePages_Rsvd: 3954

HugePages_Surp: 0

Hugepagesize: 2048 kB

--//頁面表僅僅消耗1.6G.

# ps -ef | grep -i oracled[b]|wc

3648 32832 266538

--//3648個程式.

top - 09:04:59 up 1429 days, 6:40, 1 user, load average: 4.18, 3.99, 3.64

Tasks: 3963 total, 4 running, 3959 sleeping, 0 stopped, 0 zombie

Cpu(s): 20.7%us, 5.6%sy, 0.0%ni, 73.1%id, 0.0%wa, 0.0%hi, 0.6%si, 0.0%st

Mem: 264235388k total, 244039764k used, 20195624k free, 9676076k buffers

Swap: 25165820k total, 0k used, 25165820k free, 33019892k cached

PID PPID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ RUSER UID GROUP TTY P SWAP TIME CODE DATA nFLT nDRT WCHAN Flags COMMAND

112340 12166 oracle 20 0 951m 499m 12m S 0.0 0.2 1348:50 oracle 1001 oinstall ? 20 451m 22,28 36 857m 0 0 futex_wai ..4.2... /u01/app/oracle/product/agent12c/core/12.1.0.4.0/jdk/bin/java -Xmx223M -XX:MaxPerm

25209 1 oracle 20 0 76.1g 121m 21m S 0.0 0.0 39:38.45 oracle 1001 asmadmin ? 23 76g 39:38 185m 6076 0 0 semtimedo ..4.2... ora_arc9_dbcn1

24016 1 oracle 20 0 76.1g 112m 17m S 0.9 0.0 28805:22 oracle 1001 asmadmin ? 3 75g 480,05 185m 3564 4 0 poll_sche ..4.2... ora_dia0_dbcn1

25199 1 oracle 20 0 76.1g 110m 21m S 0.0 0.0 39:52.84 oracle 1001 asmadmin ? 23 76g 39:52 185m 6168 0 0 semtimedo ..4.2... ora_arc4_dbcn1

25203 1 oracle 20 0 76.1g 110m 21m S 0.0 0.0 39:15.27 oracle 1001 asmadmin ? 23 76g 39:15 185m 6140 0 0 semtimedo ..4.2... ora_arc6_dbcn1

25201 1 oracle 20 0 76.1g 110m 21m S 0.0 0.0 78:29.38 oracle 1001 asmadmin ? 12 76g 78:29 185m 5372 0 0 semtimedo ..4.2... ora_arc5_dbcn1

25205 1 oracle 20 0 76.1g 109m 21m S 0.0 0.0 40:12.35 oracle 1001 asmadmin ? 9 76g 40:12 185m 6152 0 0 semtimedo ..4.2... ora_arc7_dbcn1

25207 1 oracle 20 0 76.1g 109m 21m S 0.0 0.0 40:05.73 oracle 1001 asmadmin ? 20 76g 40:05 185m 6120 0 0 semtimedo ..4.2... ora_arc8_dbcn1

25197 1 oracle 20 0 76.1g 109m 21m S 0.0 0.0 40:26.04 oracle 1001 asmadmin ? 23 76g 40:26 185m 6120 0 0 semtimedo ..4.2... ora_arc3_dbcn1

25191 1 oracle 20 0 76.1g 108m 21m S 0.0 0.0 38:48.21 oracle 1001 asmadmin ? 3 76g 38:48 185m 6096 8 0 semtimedo ..4.2... ora_arc0_dbcn1

25193 1 oracle 20 0 76.1g 108m 21m S 0.0 0.0 39:19.44 oracle 1001 asmadmin ? 6 76g 39:19 185m 6068 0 0 semtimedo ..4.2... ora_arc1_dbcn1

28227 1 oracle 20 0 76.1g 107m 20m S 0.0 0.0 27:07.26 oracle 1001 asmadmin ? 19 76g 27:07 185m 5112 0 0 semtimedo ..4.2... ora_arc7_dben1

28225 1 oracle 20 0 76.1g 105m 20m S 0.0 0.0 27:03.77 oracle 1001 asmadmin ? 5 75g 27:03 185m 4988 0 0 semtimedo ..4.2... ora_arc6_dben1

28213 1 oracle 20 0 76.1g 104m 20m S 0.0 0.0 27:05.39 oracle 1001 asmadmin ? 9 76g 27:05 185m 4984 0 0 semtimedo ..4.2... ora_arc0_dben1

28229 1 oracle 20 0 76.1g 100m 21m S 0.0 0.0 27:14.12 oracle 1001 asmadmin ? 17 76g 27:14 185m 4992 0 0 semtimedo ..4.2... ora_arc8_dben1

28221 1 oracle 20 0 76.1g 98m 21m S 0.0 0.0 27:20.46 oracle 1001 asmadmin ? 3 76g 27:20 185m 5060 0 0 semtimedo ..4.2... ora_arc4_dben1

28231 1 oracle 20 0 76.1g 96m 20m S 0.0 0.0 27:05.01 oracle 1001 asmadmin ? 9 76g 27:05 185m 4992 0 0 semtimedo ..4.2... ora_arc9_dben1

28223 1 oracle 20 0 76.1g 94m 21m S 0.0 0.0 27:03.38 oracle 1001 asmadmin ? 19 76g 27:03 185m 4960 0 0 semtimedo ..4.2... ora_arc5_dben1

80614 1 oracle 20 0 76.1g 93m 20m S 0.0 0.0 9:43.50 oracle 1001 asmadmin ? 5 76g 9:43 185m 4988 0 0 semtimedo ..4.2... ora_arc2_dben1

27568 1 oracle -2 0 76.1g 88m 17m S 0.6 0.0 3825:00 oracle 1001 asmadmin ? 18 75g 63,45 185m 3732 0 0 poll_sche ..4.2... ora_lms1_dben1

27564 1 oracle -2 0 76.1g 88m 17m S 0.6 0.0 3781:02 oracle 1001 asmadmin ? 0 75g 63,01 185m 3732 0 0 poll_sche ..4.2... ora_lms0_dben1

24037 1 oracle 20 0 76.1g 86m 19m S 0.0 0.0 1252:39 oracle 1001 asmadmin ? 22 75g 20,52 185m 52m 0 0 semtimedo ..4.2... ora_dbw0_dbcn1

25171 1 oracle 20 0 76.0g 85m 27m S 0.0 0.0 2295:56 oracle 1001 asmadmin ? 1 75g 38,15 185m 4644 1 0 semtimedo ..4.2... ora_rsm0_dbcn1

24041 1 oracle 20 0 76.1g 85m 18m S 0.0 0.0 1157:28 oracle 1001 asmadmin ? 22 75g 19,17 185m 52m 0 0 semtimedo ..4.2... ora_dbw2_dbcn1

24039 1 oracle 20 0 76.1g 83m 18m S 0.0 0.0 1173:16 oracle 1001 asmadmin ? 0 75g 19,33 185m 51m 0 0 semtimedo ..4.2... ora_dbw1_dbcn1

28215 1 oracle 20 0 76.1g 83m 19m S 0.0 0.0 70:07.81 oracle 1001 asmadmin ? 23 75g 70:07 185m 4172 0 0 semtimedo ..4.2... ora_arc1_dben1

27557 1 oracle 20 0 76.0g 80m 17m S 2.8 0.0 23005:17 oracle 1001 asmadmin ? 3 75g 383,25 185m 3564 0 0 poll_sche ..4.2... ora_dia0_dben1

--//不知道為什麼這個版本的swap顯示這麼大.實際上應該是0.

3.難道11g下有bug還是一些問題.我在測試重新測試看看.我以前看到不是這樣的情況:

SYS@book> @ 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> create pfile='/tmp/@.ora' from spfile;

File created.

--//修改/tmp/book.ora檔案,修改加入如下:

*.use_large_pages=false

*.pre_page_sga=true

SYS@book> startup pfile='/tmp/book.ora;

ORACLE instance started.

Total System Global Area 643084288 bytes

Fixed Size 2255872 bytes

Variable Size 205521920 bytes

Database Buffers 427819008 bytes

Redo Buffers 7487488 bytes

Database mounted.

Database opened.

--//忘記輸入結尾的引號,竟然也能透過.

SYS@book> show parameter pre_page_sga

NAME TYPE VALUE

------------ ------- -------

pre_page_sga boolean TRUE

$ cat /proc/meminfo | grep HugePages

AnonHugePages: 0 kB

HugePages_Total: 104

HugePages_Free: 104

HugePages_Rsvd: 0

HugePages_Surp: 0

--//可以發現HugePages_Total=HugePages_Free.表明沒有使用hugepages.

SYS@book> show sga

Total System Global Area 643084288 bytes

Fixed Size 2255872 bytes

Variable Size 205521920 bytes

Database Buffers 427819008 bytes

Redo Buffers 7487488 bytes

# top -u oracle

--//按照res排序.

top - 09:22:00 up 1065 days, 45 min, 1 user, load average: 0.18, 0.14, 0.10

Tasks: 245 total, 1 running, 244 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, 80358708k used, 51902488k free, 2489960k buffers

Swap: 31455264k total, 2292k used, 31452972k free, 72454796k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

22305 oracle 20 0 884m 645m 618m S 0.0 0.5 0:00.39 ora_arc0_book

22303 oracle 20 0 859m 639m 634m S 0.0 0.5 0:00.82 oraclebook (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

22291 oracle 20 0 859m 638m 632m S 0.0 0.5 0:00.66 ora_mmon_book

22323 oracle 20 0 858m 634m 628m S 0.0 0.5 0:00.40 ora_cjq0_book

22283 oracle 20 0 868m 628m 625m S 0.0 0.5 0:00.20 ora_lgwr_book

22277 oracle 20 0 860m 626m 618m S 0.0 0.5 0:00.17 ora_dbw0_book

22279 oracle 20 0 859m 625m 617m S 0.0 0.5 0:00.28 ora_dbw1_book

22281 oracle 20 0 859m 625m 617m S 0.0 0.5 0:00.26 ora_dbw2_book

22287 oracle 20 0 854m 625m 622m S 0.0 0.5 0:00.30 ora_smon_book

22289 oracle 20 0 855m 625m 622m S 0.0 0.5 0:00.27 ora_reco_book

22271 oracle 20 0 854m 624m 622m S 0.0 0.5 0:00.30 ora_dbrm_book

22307 oracle 20 0 876m 621m 618m S 0.0 0.5 0:00.23 ora_arc1_book

22259 oracle 20 0 855m 621m 619m S 0.0 0.5 0:00.49 ora_pmon_book

22293 oracle 20 0 854m 621m 618m S 0.0 0.5 0:00.37 ora_mmnl_book

22285 oracle 20 0 854m 620m 618m S 0.0 0.5 0:00.35 ora_ckpt_book

22273 oracle 20 0 854m 620m 617m S 0.0 0.5 0:00.31 ora_dia0_book

22275 oracle 20 0 853m 619m 617m S 0.0 0.5 0:00.25 ora_mman_book

22295 oracle 20 0 859m 619m 617m S 0.0 0.5 0:00.22 ora_d000_book

22261 oracle 20 0 853m 619m 617m S 0.0 0.5 0:00.20 ora_psp0_book

22269 oracle 20 0 853m 619m 617m S 0.0 0.5 0:00.24 ora_diag_book

22263 oracle -2 0 853m 619m 617m S 1.8 0.5 0:03.61 ora_vktm_book

22267 oracle 20 0 853m 619m 617m S 0.0 0.5 0:00.24 ora_gen0_book

22297 oracle 20 0 854m 618m 616m S 0.0 0.5 0:00.15 ora_s000_book

--//可以發現RES消耗很大的記憶體.

這樣頁面表消耗如下:

$ cat /proc/meminfo | grep -i page

AnonPages: 228148 kB

PageTables: 39968 kB

AnonHugePages: 0 kB

HugePages_Total: 104

HugePages_Free: 104

HugePages_Rsvd: 0

HugePages_Surp: 0

Hugepagesize: 2048 kB

--//重新啟動看看:

SYS@book> show parameter pre_page_sga

NAME TYPE VALUE

------------ ------- ------

pre_page_sga boolean FALSE

$ cat /proc/meminfo | grep -i page

AnonPages: 201812 kB

PageTables: 12536 kB

AnonHugePages: 0 kB

HugePages_Total: 309

HugePages_Free: 98

HugePages_Rsvd: 98

HugePages_Surp: 205

Hugepagesize: 2048 kB

--//pre_page_sga=false,使用hugepages.

top - 09:31:30 up 1065 days, 54 min, 1 user, load average: 0.12, 0.10, 0.09

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, 80083584k used, 52177612k free, 2489960k buffers

Swap: 31455264k total, 2292k used, 31452972k free, 71826716k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

5459 oracle 20 0 69752 48m 716 S 0.3 0.0 19:08.01 tmux new-session -s oracle -n bashsqlplus -d

22431 oracle 20 0 859m 34m 29m S 0.0 0.0 0:00.60 oraclebook (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

22417 oracle 20 0 859m 33m 28m S 0.0 0.0 0:00.47 ora_mmon_book

22451 oracle 20 0 858m 28m 23m S 0.0 0.0 0:00.19 ora_cjq0_book

22403 oracle 20 0 860m 22m 13m S 0.0 0.0 0:00.03 ora_dbw0_book

22405 oracle 20 0 859m 21m 13m S 0.0 0.0 0:00.02 ora_dbw1_book

22407 oracle 20 0 859m 21m 13m S 0.0 0.0 0:00.02 ora_dbw2_book

22413 oracle 20 0 854m 21m 18m S 0.0 0.0 0:00.06 ora_smon_book

22415 oracle 20 0 855m 20m 18m S 0.0 0.0 0:00.02 ora_reco_book

22397 oracle 20 0 854m 20m 17m S 0.0 0.0 0:00.02 ora_dbrm_book

22385 oracle 20 0 855m 17m 14m S 0.0 0.0 0:00.03 ora_pmon_book

22419 oracle 20 0 854m 16m 13m S 0.0 0.0 0:00.06 ora_mmnl_book

22409 oracle 20 0 868m 16m 14m S 0.0 0.0 0:00.04 ora_lgwr_book

22411 oracle 20 0 854m 16m 14m S 0.0 0.0 0:00.04 ora_ckpt_book

22435 oracle 20 0 876m 16m 13m S 0.0 0.0 0:00.02 ora_arc1_book

22399 oracle 20 0 854m 16m 13m S 0.0 0.0 0:00.08 ora_dia0_book

22433 oracle 20 0 876m 16m 13m S 0.0 0.0 0:00.02 ora_arc0_book

--//RES僅僅20-30M上下.

總之:

我個人建議安裝oracle,設定使用hugepages作為必要的步驟.設定pre_page_sga=true的情況下,更加必要.

[20181109]12cR2 的pre_page_sga引數.txt

相關文章