今天早上看到某微信群有人在指点分析一个awr报告，反馈说pga设置过小。 实际上10年前我特意去研究过Oracle 10g版本中，一个进程大概会消耗4-5MB内存左右，而11g+版本，印象中会更高一些。目前很多客户基本上都是Oracle 12c甚至19c版本了，因此我想有必要再简单测一下。
oracle@11g-node1:/home/oracle $sqlplus roger/roger@mytest
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 13 10:46:32 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ROGER@mytest>select instance_name from v$instance;
INSTANCE_NAME
--------------------------------
rac11g2
ROGER@mytest> select sid from v$mystat where rownum=1;
SID
----------
779
ROGER@mytest>
oracle@11g-node2:/home/oracle $sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 13 10:47:34 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select p.addr,p.spid from v$process p,v$session s
2 where P.ADDR=S.PADDR
3 and s.sid=779;
ADDR SPID
---------------- ------------------------------------------------
000000015D6763E0 25023
[root@11g-node2 ~]# pmap -x 25023 |tail -n 10
00007ff99b320000 4 4 4 rw--- [ anon ]
00007ff99b321000 4 4 0 r-x-- libodmd11.so
00007ff99b322000 1024 0 0 ----- libodmd11.so
00007ff99b422000 4 4 4 rw--- libodmd11.so
00007ff99b423000 4 4 4 rw--- [ anon ]
00007ffc01244000 180 180 180 rw--- [ stack ]
00007ffc01286000 4 4 0 r-x-- [ anon ]
ffffffffff600000 4 0 0 r-x-- [ anon ]
---------------- ------ ------ ------
total kB 4444044 34048 10424
[root@11g-node2 ~]#
sqlplus 连接的方式看上去内存消耗略高一些。对于数据库而言，基本上都是jdbc或者一些客户端连接工具，这里我们也针对性的看一下。
SQL> set lines 200
SQL> col username for a30
SQL> col program for a50
SELECT s.sid,
SQL> 2 s.username,
s.program,
p.spid
FROM v$process p,
v$session s
WHERE P.ADDR=S.PADDR
3 4 5 6 7 8 AND s.sid in(396,1157);
SID USERNAME PROGRAM SPID
---------- ------------------------------ -------------------------------------------------- ------------------------------------------------
1157 DBAAS_SYS JDBC Thin Client 4701
396 ROGER Mogeaver 22?0?5 ? Metadata 30058
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
oracle@11g-node2:/home/oracle $exit
logout
[root@11g-node2 ~]# pmap -x 4701 |tail -n 5
00007ffdfa3c2000 100 96 96 rw--- [ stack ]
00007ffdfa3e8000 4 4 0 r-x-- [ anon ]
ffffffffff600000 4 0 0 r-x-- [ anon ]
---------------- ------ ------ ------
total kB 4444988 33488 13544
[root@11g-node2 ~]# pmap -x 30058|tail -n 5
00007fff80f76000 144 144 144 rw--- [ stack ]
00007fff80fc9000 4 4 0 r-x-- [ anon ]
ffffffffff600000 4 0 0 r-x-- [ anon ]
---------------- ------ ------ ------
total kB 4446056 39848 14752
[root@11g-node2 ~]#
[root@11g-node2 ~]#
[root@11g-node2 ~]# pmap -d 4701 |tail -n 5
00007f3baf211000 4 rw--- 0000000000000000 000:00000 [ anon ]
00007ffdfa3c2000 100 rw--- 0000000000000000 000:00000 [ stack ]
00007ffdfa3e8000 4 r-x-- 0000000000000000 000:00000 [ anon ]
ffffffffff600000 4 r-x-- 0000000000000000 000:00000 [ anon ]
mapped: 4444988K writeable/private: 9392K shared: 4196352K
[root@11g-node2 ~]# pmap -d 30058 |tail -n 5
00007fe7e5fc1000 4 rw--- 0000000000000000 000:00000 [ anon ]
00007fff80f76000 144 rw--- 0000000000000000 000:00000 [ stack ]
00007fff80fc9000 4 r-x-- 0000000000000000 000:00000 [ anon ]
ffffffffff600000 4 r-x-- 0000000000000000 000:00000 [ anon ]
mapped: 4446056K writeable/private: 10332K shared: 4196352K
[root@11g-node2 ~]#
可以看到实际jdbc和mogevar的客户端登录的情况之下，内存消耗分别在9m、10m左右。
那么这就是Oracle server process进程的pga内存消耗吗？实际上并不是，如果我们查询相关视图发现，分配的pga要比这个小得多。
SQL> col PROCESS_NAME for a45
SQL> set lines 240
SQL> l
1 select
2 to_char(ssn.sid, '9999') as session_id,
3 ssn.serial# as session_serial,
4 nvl(ssn.username, nvl(bgp.name, 'background'))
5 || '::'
6 || nvl(lower(ssn.machine), ins.host_name) as process_name,
7 to_char(prc.spid, '999999999') as pid_thread,
8 to_char((se1.value / 1024) / 1024, '999g999g990d00') as current_size_mb,
9 to_char((se2.value / 1024) / 1024, '999g999g990d00') as maximum_size_mb
10 from
11 v$statname stat1,
12 v$statname stat2,
13 v$session ssn,
14 v$sesstat se1,
15 v$sesstat se2,
16 v$bgprocess bgp,
17 v$process prc,
18 v$instance ins
19 where
20 stat1.name = 'session pga memory'
21 and stat2.name = 'session pga memory max'
22 and se1.sid = ssn.sid
23 and se2.sid = ssn.sid
24 and se2.statistic# = stat2.statistic#
25 and se1.statistic# = stat1.statistic#
26 and ssn.paddr = bgp.paddr (+)
27 and ssn.paddr = prc.addr (+)
28 and ssn.sid in(396,1157)
29 order by
30 maximum_size_mb
31*
SQL> /
SESSION_ID SESSION_SERIAL PROCESS_NAME PID_THREAD CURRENT_SIZE_MB MAXIMUM_SIZE_MB
---------- -------------- --------------------------------------------- ------------ ------------------- -------------------
1157 8777 DBAAS_SYS::zcloud-proxy-ex 13471 1.60 3.16
396 42445 ROGER::lizhenxudemacbook-pro.local 30058 2.28 4.28
SQL>
可以看到jdbc的连接，以及我的客户端程序实际上最大的pga消耗也就3m、4m左右，比pmap看到的结果要小得多。
当然，我们也可以通过dump process来确认其uga的分配情况。
SQL> oradebug close_trace;
Statement processed.
SQL> oradebug setospid 30058
Oracle pid: 49, Unix process pid: 30058, image: oracle@11g-node2
SQL> oradebug dump heapdump 536870917
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/rac11g/rac11g2/trace/rac11g2_ora_30058.trc
[root@11g-node2 ~]# cat /u01/app/oracle/diag/rdbms/rac11g/rac11g2/trace/rac11g2_ora_30058.trc|grep "Total heap size"|awk '{sum+=$5} END {print sum}'
3621640
[root@11g-node2 ~]#
可以看到实际上该进程的pga消耗也就3M多。
同样的测试方式，我在Oracle 19.23单机版本中验证，发现其实差不多。
++++Oracle 19c
SQL> select sid,username,program from v$session where username is not null;
Sess id Oracle user Program
------- ------------------------------ --------------------------------------------------
3 SYS oracle@ora19c1 (OFSD)
11 DBAAS_MONITOR JDBC Thin Client
17 SYS sqlplus@ora19c1 (TNS V1-V3)
22 DBAAS_MONITOR zcloud_oracle_exporter@zCloud-Proxy-Ex (TNS V1-V
23 DBAAS_MONITOR zcloud_oracle_exporter@zCloud-Proxy-Ex (TNS V1-V
26 DBAAS_MONITOR zcloud_oracle_exporter@zCloud-Proxy-Ex (TNS V1-V
29 DBAAS_MONITOR JDBC Thin Client
207 DBAAS_SYS JDBC Thin Client
212 DBAAS_MONITOR zcloud_oracle_exporter@zCloud-Proxy-Ex (TNS V1-V
612 DBAAS_MONITOR zoramon_collector@zCloud-Proxy-Ex (TNS V1-V3)
10 rows selected.
SQL> set lines 200
col username for a30
col program for a50
SELECT s.sid,
s.username,
s.program,
p.spid
SQL> SQL> SQL> 2 3 4 5 FROM v$process p,
6 v$session s
WHERE P.ADDR=S.PADDR
AND s.sid in(207);
7 8
Sess id Oracle user Program OSpid
------- ------------------------------ -------------------------------------------------- --------
207 DBAAS_SYS JDBC Thin Client 15547
SQL> oradebug setospid 15547
Oracle pid: 37, Unix process pid: 15547, image: oracle@ora19c1
SQL> oradebug dump heapdump 536870917
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/killdb/killdb/trace/killdb_ora_15547.trc
SQL> SELECT p.spid,
2 p.pid,
s.sid,
s.serial#,
s.status,
p.pga_alloc_mem,
p.pga_used_mem,
s.username,
3 4 5 6 7 8 9 s.osuser,
s.program
FROM v$process p, v$session s
WHERE s.paddr( + ) = p.addr
and s.sid in(207)
ORDER BY p.pga_alloc_mem DESC; 10 11 12 13 14
OSpid Orapid Sess id Serial# Status PGA alloc PGA used Oracle user OS user Program
-------- ------- ------- ------- -------- --------------- --------------- ------------------------------ ------------ --------------------------------------------------
15547 37 207 6499 INACTIVE 2,185,445 1,754,605 DBAAS_SYS zcloud JDBC Thin Client
SQL>
[root@ora19c1 ~]# pmap -x 15547 |tail -n 10
00007f460e1ec000 8 0 0 rw-s- [aio] (deleted)
00007f460e1ee000 12 12 12 rw--- [ anon ]
00007f460e1f1000 4 4 4 r---- ld-2.17.so
00007f460e1f2000 4 4 4 rw--- ld-2.17.so
00007f460e1f3000 4 4 4 rw--- [ anon ]
00007fff52569000 544 228 228 rw--- [ stack ]
00007fff525f9000 8 4 0 r-x-- [ anon ]
ffffffffff600000 4 0 0 r-x-- [ anon ]
---------------- ------- ------- -------
total kB 5509772 31096 8360
[root@ora19c1 ~]#
[root@ora19c1 ~]# pmap -d 15547 |tail -n 5
00007f460e1f3000 4 rw--- 0000000000000000 000:00000 [ anon ]
00007fff525b8000 228 rw--- 0000000000000000 000:00000 [ stack ]
00007fff525f9000 8 r-x-- 0000000000000000 000:00000 [ anon ]
ffffffffff600000 4 r-x-- 0000000000000000 000:00000 [ anon ]
mapped: 5509456K writeable/private: 6060K shared: 5046312K
[root@ora19c1 ~]#
[root@ora19c1 ~]# cat /u01/app/oracle/diag/rdbms/killdb/killdb/trace/killdb_ora_15547.trc|grep "Total heap size"|awk '{sum+=$5} END {print sum}'
3607288
可以看到对于pga而言，单个进程也就3~4m的样子。而在os层来看，单个进程的内存消耗仍然在6m左右，似乎比11g略一点点？这可能是错觉！
同样我测试发现23Ai版本也类似，os层面单个进程实际内存消耗大约在10m左右；而pga实际上消耗也在3.5MB左右。
这里搞个简单的shell脚本来模拟创建一些空连接：
SQL> select username,count(1) from v$session group by username;
Oracle user COUNT(1)
------------------------- ----------
SYS 2
DBAAS_SYS 4
54
ROGER 201
SQL> @all_sess_pga
OSpid Orapid Sess id Serial# Status PGA alloc PGA used Oracle user OS user Program
-------- ------- ------- ------- -------- --------------- --------------- ------------------------- ------------ ----------------------------------------
30962 93 1900 4264 ACTIVE 3,689,669 2,248,797 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
635 307 1159 40941 ACTIVE 3,296,453 2,023,269 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
616 254 2292 15728 ACTIVE 3,230,917 2,080,285 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
334 168 17 59337 ACTIVE 3,034,309 2,225,677 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30219 75 1145 44035 ACTIVE 2,772,165 1,888,325 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
503 188 1534 1647 ACTIVE 2,706,629 1,985,333 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
612 252 1533 17064 ACTIVE 2,510,021 1,896,597 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
497 269 1906 37864 ACTIVE 2,313,413 1,625,437 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
471 250 797 17342 ACTIVE 2,182,341 1,625,029 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
32732 259 1172 10327 ACTIVE 2,116,805 1,684,589 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
425 199 2669 46282 ACTIVE 2,116,805 1,625,437 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
31026 99 1150 28897 ACTIVE 1,985,733 1,616,885 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
439 200 19 26019 ACTIVE 1,985,733 1,555,133 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
32742 161 396 65496 ACTIVE 1,985,733 1,555,133 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30990 96 13 30689 ACTIVE 1,920,197 1,641,733 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30953 92 1526 42949 ACTIVE 1,920,197 1,641,733 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30817 91 1148 5685 ACTIVE 1,920,197 1,641,733 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
31028 100 1527 41214 ACTIVE 1,920,197 1,641,733 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
31032 103 2660 3093 ACTIVE 1,920,197 1,641,733 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
31036 105 392 54079 ACTIVE 1,920,197 1,641,733 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30994 97 391 26262 ACTIVE 1,920,197 1,641,733 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30411 79 2656 1511 ACTIVE 1,920,197 1,641,733 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30654 89 389 6221 ACTIVE 1,920,197 1,641,733 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30531 86 2281 59230 ACTIVE 1,920,197 1,641,733 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
455 196 1551 12018 ACTIVE 1,920,197 1,650,901 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30413 84 1525 24680 ACTIVE 1,920,197 1,641,733 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30306 76 1524 53831 ACTIVE 1,920,197 1,641,733 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30477 83 1147 34988 ACTIVE 1,920,197 1,641,733 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30475 82 767 1261 ACTIVE 1,920,197 1,641,733 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30456 81 388 44942 ACTIVE 1,920,197 1,641,733 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30988 95 2658 5927 ACTIVE 1,920,197 1,641,733 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30940 94 2282 15566 ACTIVE 1,920,197 1,641,733 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
32704 113 395 40418 ACTIVE 1,854,661 1,711,949 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30339 77 1898 35674 ACTIVE 1,789,125 1,579,909 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30292 78 2280 874 ACTIVE 1,789,125 1,579,909 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30458 80 10 17661 ACTIVE 1,789,125 1,579,909 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30575 85 1899 51686 ACTIVE 1,789,125 1,579,909 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
30726 87 2657 27274 ACTIVE 1,789,125 1,579,909 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
。。。。。
709 335 2689 44879 ACTIVE 1,789,125 1,580,893 ROGER oracle sqlplus@oradb1 (TNS V1-V3)
201 rows selected.
SQL>
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
pga_aggregate_limit big integer 6000M
pga_aggregate_target big integer 300M
SQL> show parameter process
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
aq_tm_processes integer 1
cell_offload_processing boolean TRUE
db_writer_processes integer 2
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 160
log_archive_max_processes integer 4
processes integer 2000
processor_group_name string
SQL> select (6000-2048)/2000 from dual;
(6000-2048)/2000
----------------
1.976
SQL>
可以看到在12c+版本中，pga limit参数会根据原始limit+process来进行计算，似乎并不是文档提到的原始limit大小+process*4m。 从我这里计算来看，更像是每个进程实际的pga分配大写，大约1.97m。
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
加入DataBase Fans付费群，您能有哪些收获？
1、大家可与顶级数据库专家互动，问题范围不限于Oracle，MySQL,openGauss等。
-群内有全国Oracle顶级恢复专家，sql优化专家，MySQL源码专家，都是实战派
2、 入群可以获得顶级专家的收藏脚本。
3、 可提供原厂资料文档代查【包括xxxx账号,你们懂的】
4、 不定期组织直播案例分析【包括但不限于Oracle、MySQL、国产xxx数据库】
5、 付费群：365人/年 【2025/1/1 - 2025/12/31】
想加入的朋友， 加v咨询 Roger_database
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
