oracle process的参数设置

Posted 2021-08-24 by wukaiqiang; tagged with none

1、process不是越大越好。
一是process分配越大，占用的sga内存也会越多。平均150k一个会话数。
二是process越大，pga中保持空闲会话的内存也越多，linux平均一个会话5M，aix8M。

2、实验过程：
process=150
sys@ORCL 16:01:46> select * from v$sgainfo;

NAME BYTES RES

Fixed SGA Size 2253624 No
Redo Buffers 7393280 No
Buffer Cache Size 1174405120 Yes
Shared Pool Size 352321536 Yes
Large Pool Size 33554432 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 0 Yes
Shared IO Pool Size 0 Yes
Granule Size 16777216 No
Maximum SGA Size 1586708480 No
Startup overhead in Shared Pool 91557192 No 87MB
Free SGA Memory Available 0

process=1500
sys@ORCL 24-AUG-21> select * from v$sgainfo;

NAME BYTES RES

Fixed SGA Size 2253624 No
Redo Buffers 7393280 No
Buffer Cache Size 1191182336 Yes
Shared Pool Size 335544320 Yes
Large Pool Size 33554432 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 0 Yes
Shared IO Pool Size 0 Yes
Granule Size 16777216 No
Maximum SGA Size 1586708480 No
Startup overhead in Shared Pool 303860664 No 289MB
Free SGA Memory Available 0

sys@ORCL 15:58:38> select sum(pga_used_mem)/1024 total_used, sum(pga_used_mem)/count(1)/1024 used_avg, sum(pga_alloc_mem)/1024 total_alloc, sum(pga_alloc_mem)/count(1)/1024 alloc_avg from v$process;

TOTAL_USED USED_AVG TOTAL_ALLOC ALLOC_AVG

128314.369 4424.63342 150193.664 5179.09186
平均每个会话分配内存5179.09186 KB
平均每个会话使用内存4424.63342 KB