今天早上看到某微信群有人在指点分析一个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 ~]#
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.
  • 48.
  • 49.
  • 50.
  • 51.
  • 52.
  • 53.
  • 54.
  • 55.
  • 56.
  • 57.
  • 58.
  • 59.
  • 60.

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|t