在可插拔数据库上如何监控进程内存的使用
本文档旨在说明如何查看可插拔数据库进程的内存使用情况。
在 RAC 环境上，因为 v$ 视图用于提供单个数据库实例的准确的内存使用情况，所以应该分别在每个实例上执行这些脚本。
由于使用了 SQL_Plus 特定的功能，这些脚本必须在 SQL_Plus 中执行。
必须使用 sysdba 或拥有 CDB*，DBA* 和 V$ 表访问权限的数据库用户执行这些脚本。
提供的脚本例子和输出结果只是为了演示的目的。
附件文件包含了所有 SQL 的文件及把他们的示例输出。
本文提供的示例代码仅用于教育目的，并不被 Oracle 技术支持所支持。示例代码已经通过内部测试，但是我们并不能确保在您的环境中能正常运行，请确保在使用前已经在您的测试环境上测试通过。
在使用它之前务必对这个例子代码进行校对!因为在文本编辑器，e-mail 和系统处理的文本格式（空格，缩进符，回车）方面存在不同，当您拷贝后这个例子代码不一定能成功的执行。一定检查例子代码以确保没有任何错误。
解决方案
因为诸多查询引用了容器特定的视图或字段，这些查询旨在包含可插拔数据库（PDB）的根容器数据库（CDB）上进行执行。对于在非容器数据库的查询，请参照 Document: 399497.1。
容器数据库（CDB）和所有的可插拔数据库（PDBs）共享一个单一数据库实例，这个实例由一个系统全局区（SGA）和一些后台进程组成。因为这种共享内存资源的特性，使得你可能非常想要去区分共享该实例的各个数据库，究竟各自都使用了多少资源。如果可能的话，我们还将提供一个查询，将各个每个可插拔数据库所使用的进程内存（PGA）也区分开。
使用了 ROUND 函数使得查询结果更容易用 MB 形式来展现；因为得到是四舍五入的值，所以得到的结果和直接查询 VPROCESS 和 VSESSTAT 得到的结果不能精确匹配。
set linesize 150 set pagesize 3000 set NUMWIDTH 15 col Parameter format a30 col component format a28 COLUMN DEFAULT\_ATTR FORMAT A7 COLUMN OWNER FORMAT A15 COLUMN OBJECT\_NAME FORMAT A15 COLUMN ALL\_CONTAINERS FORMAT A3 COLUMN CONTAINER\_NAME FORMAT A10 COLUMN CON\_ID FORMAT 999 COLUMN pdb\_name FORMAT A20 COLUMN memory Format A25 COLUMN spid HEADING ‘OSpid’ FORMAT a8 COLUMN pid HEADING ‘Orapid’ FORMAT 999999 COLUMN sid HEADING ‘Sess id’ FORMAT 99999 COLUMN serial# HEADING ‘Serial#’ FORMAT 999999 COLUMN status HEADING ‘Status’ FORMAT a8 COLUMN pga\_alloc\_mem HEADING ‘PGA alloc’ FORMAT 999,999,999 COLUMN pga\_used\_mem HEADING ‘PGA used’ FORMAT 999,999,999 COLUMN pga\_max\_mem HEADING ‘PGA Max’ FORMAT 999,999,999 COLUMN username HEADING ‘oracleuser’ FORMAT a12 COLUMN osuser HEADING ‘OS user’ FORMAT a12 COLUMN program HEADING ‘Program’ FORMAT a24 COLUMN Mbytes Heading ‘Mbytes’ FORMAT 999,999,999 COLUMN name FORMAT A22 – 设置 session 的日期格式 alter session set nls\_date\_format=‘DD-MON-YYYY HH24:MI:SS’; – 脚本运行的日期 select sysdate from dual;复制
这个查询将识别脚本是否是运行在根容器数据库（CDB）。
通过查看变量 con_name 的值可以表明容器的名字是 CDB$ROOT，看 con_id 的值可以知道容器ID是1。
“show pdbs”这个命令显示了 CDB 下所有可插拔数据库的状态（是否打开，是否以受限制的模式打开）。这个命令也被用于识别 PDB 名和容器ID的联系（con_id：用于许多查询分解内存使用到指定的容器）。
如果在非容器数据库执行的，这些命令将返回 NULL 值。
这些例子结果如下展示了关联到当前 CDB 的除种子 PDB 外的四个额外的 PDBs。PDB（ ID：5，名：PDB_COPY）没有被打开，其他所有的 PDBs 是打开的状态。
show con\_name show con\_id show pdbs CON\_NAME CDB$ROOT CON\_ID 1 CON\_ID CON\_NAME OPEN MODE RESTRICTED * * * 2 PDB$SEED READ ONLY NO 3 PDB_SS READ WRITE NO 4 PDB1 READ WRITE NO 5 PDB_COPY MOUNTED 6 PDB2 READ WRITE NO复制
下面的查询提供了根容器数据库（CDB）的名字。例如：CDB1。
select name, cdb, con\_id from v$database; NAME CDB CON\_ID * * * CDB1 YES 0复制
下面的查询展示了关于每个容器其他的ID信息和数据库状态。
–Information About Each Container SELECT NAME, CON\_ID, OPEN\_MODE, RESTRICTED, DBID, CON\_UID, GUID FROM V$CONTAINERS ORDER BY CON\_ID; NAME CON\_ID OPEN\_MODE RES DBID CON\_UID GUID * * * CDB$ROOT 1 READ WRITE NO 762218087 1 C40F9B49FC9D19E0E0430BAAE80AFF01 PDB$SEED 2 READ ONLY NO 4031134518 4031134518 C40F9B49FC9C19E0E0430BAAE80AFF01 PDB\_SS 3 READ WRITE NO 1556201860 1556201860 C4109F71E0095A2FE0430BAAE80A6619 PDB1 4 READ WRITE NO 3296179875 3296179875 C4AFBF825964352DE04362F519904F91 PDB\_COPY 5 MOUNTED 1667449117 1667449117 D14DA20BBD781142E0430100007FBAFE PDB2 6 READ WRITE NO 3868752707 3868752707 D14DA20BBD7C1142E0430100007FBAFE复制
这个基于视图 V$SESSTAT 的查询同时展现了用 “session pga memory” 标识当前进程的大小和用 “session pga memory max” 标识在进程生命周期中进程的最大内存大小。
AND 子句"s.value > 20000000"用于排除内存小于 20MB 的进程,如果您想要看到所有的进程，请删除这个子句或者修改当前值成其他的值。
下面例子的结果展示了容器 4 有一个 OS PID 4356 的进程曾经达到 1163MB；容器 6 有一个 OS pid 8367 的进程曾经达到 1,386MB。容器 DB 有一个 OS pid 7303 的进程曾经达到 940MB。当前，在这个实例上只有一个进程大于 20MB，所有其他的进程都小于 20MB，不过在他们的生命周期里曾经使用了非常大的内存（“session pga memory max”指明了这一点）。
REM v$sesstat pga memory over 20MB size
break on spid skip 1
SELECT p.spid, s.sid, p.con\_id, substr(n.name,1,25) memory, ROUND(s.value/1024/1024) as MBytes
FROM v$sesstat s, v$statname n, v$process p, v$session vs
WHERE s.statistic# = n.statistic#
AND n.name LIKE ‘%pga memory%’
AND s.sid=vs.sid
AND vs.paddr=p.addr
AND s.value > 20000000 /\* --remove this line to view all process size \*/
order by spid,memory;
break on off
OSpid Sess id CON\_ID MEMORY Mbytes
* * *
3727 246 0 session pga memory 20
246 0 session pga memory max 30
4356 22 4 session pga memory max 1,163
7303 257 1 session pga memory max 940
8367 237 6 session pga memory max 1,386
复制这个基于 v$process 的查询展示了当前使用内存最大的进程和对应的容器 ID。
下面例子的结果展示了，通过查看字段 PGA_alloc 当前最大的进程分配了 11MB PGA，而字段 PGA_MAX 显示在进程的生命周期曾最多分配 PGA 内存达到 16MB。
List largest process based on v$process:
/\* Do NOT eliminate all background process because certain background processes do need to be monitored at times \*/
SELECT pid, spid, con\_id, substr(username,1,13) username, program, ROUND(pga\_used\_mem/1024/1024) pga\_used, ROUND(pga\_alloc\_mem/1024/1024) pga\_alloc, ROUND(pga\_freeable\_mem/1024/1024) pga\_freeable, ROUND(pga\_max\_mem/1024/1024) pga\_max
FROM v$process
WHERE pga\_alloc\_mem = (SELECT max(pga\_alloc\_mem)
FROM v$process
WHERE program NOT LIKE ‘%LGWR%’);
Orapid OSpid CON\_ID oracleuser Program PGA\_USED PGA\_ALLOC PGA\_FREEABLE PGA\_MAX
* * *
19 3724 0 oracle oracle@localhost.localdo 4 11 7 16
main (MMON)
复制这个基于 v$process 的查询合计了实例或 CDB 所有进程的当前分配的 PGA。这个值包含了 CDB 和所有 PDBs 的所有后台和前台进程。这个值很好的标识了 CDB 和所有 PDBs 使用的私有内存。
–Summation of ALL PGA based on v$process:
REM allocated includes free PGA memory not yet released to the operating system by the server process
SELECT ROUND(SUM(pga\_alloc\_mem)/1024/1024) AS “Mbytes allocated”, ROUND(SUM(PGA\_USED\_MEM)/1024/1024) AS “Mbytes used”
FROM v$process;
Mbytes allocated Mbytes used
* * *
83 58
复制这个基于 v$process 的查询合计了当前所有已分配 PGA 并且按各个容器的使用进行了划分。这个值包含了 CDB 和所有 PDBs 的所有后台和前台进程。PGA 内存的使用通过容器 id 进行标识。
这个例子展示了 CDB id 0和 1 使用了 76MB， 可插拔 DB id 4 使用了 3MB，且可插拔 DB id 6 使用了 3MB。
–Summation of each container PGA based on v$process:
REM allocated includes free PGA memory not yet released to the operating system by the server process
compute sum of “Mbytes allocated” on report
break on report
SELECT con\_id, ROUND(SUM(pga\_alloc\_mem)/1024/1024) AS “Mbytes allocated”, ROUND(SUM(PGA\_USED\_MEM)/1024/1024) AS “Mbytes used”
FROM v$process
group by con\_id
order by con\_id;
break on off
CON\_ID Mbytes allocated Mbytes used
* * *
0 72 51
1 4 2
4 3 2
6 3 2
----------------
sum 82
复制这个基于 VSESSTAT 的查询合计了合计了实例或 CDB 所有进程的当前分配的内存。这个值包含了 CDB 和所有 PDBs 的所有后台和前台进程。这是除了查询 vprocess 之外的另一种方式来查看所有 PGA 的使用情况。
–Summation of ALL PGA memory based on V$SESSTAT:
SELECT ROUND(SUM(value)/1024/1024) AS Mbytes
FROM v$sesstat s, v$statname n
WHERE n.STATISTIC# = s.STATISTIC#
AND n.name = ‘session pga memory’;
Mbytes
---------------------------------------------------------------------------------------------------------------------------------------------------------------
53
复制这个基于 V$SESSTAT 的查询合计了当前所有已分配 PGA 并且按各个容器的使用进行了划分。这个值包含了 CDB 和所有 PDBs 的所有后台和前台进程。PGA 内存的使用通过容器 id 进行标识。
这个例子展示了 CDB id 0 和 1 使用了 51MB， 可插拔 DB id 4 使用了 2MB，且可插拔 DB id 6 使用了 2MB。
–Summation each container PGA memory based on V$SESSTAT:
compute sum of MBYTES on report
break on report
select con\_id, ROUND(sum(bytes)/1024/1024) as MBYTES from (SELECT p.con\_id, s.value as bytes
FROM v$sesstat s, v$statname n, v$process p, v$session vs
WHERE s.statistic# = n.statistic#
AND n.name = ‘session pga memory’
AND s.sid=vs.sid
AND vs.paddr=p.addr)
group by con\_id
order by con\_id;
break on off
CON\_ID Mbytes
* * *
0 47
1 4
4 2
6 2
------------
sum 55
复制这个查询通过"aggregate PGA target parameter"标识了 pga_aggregate_target 的值，通过"aggregate PGA auto target"动态调整当前 pga_aggregate_target 的值。
在下面的例子中，pga_aggregate_target 的值被设置成 208MB，当前调整后的 pga_aggregate_target 的值是 136MB。
实例启动后，这个查询也将可以在任何时间标识所有实例的 PGA 使用的最大值。也能被用做标识启动后 PGA 的使用曾经达到多高的值。
下面的例子展示了实例启动后在某个时间点 PGA 分配的最大值达到 1,453MB。
–PGA stats from V$PGASTAT:
–show max total pga allocated since instance startup
select name, ROUND(value/1024/1024) as Mbytes from v$pgastat
where name in (‘maximum PGA allocated’,‘aggregate PGA target parameter’,‘aggregate PGA auto target’);
NAME Mbytes
* * *
aggregate PGA target p 208
arameter
aggregate PGA auto tar 136
get
maximum PGA allocated 1,453
复制对 CDB_HIST_PGASTAT 的查询显示了 AWR 历史上最大的 PGA 内存和对应的 snapshot id。这个查询能被用于查找在什么时候 PGA 的使用达到这个高值。这个 snap_id 也能用作帮助找到 AWR 报告生成的时间范围。
下面这个例子显示了在 snapshot 211 这一时刻 PGA 内存增长到了 1,453MB。
–show max pga allocated from history
select \* from (select name,SNAP\_ID, ROUND(VALUE/1024/1024) Mbytes from CDB\_HIST\_PGASTAT
where name=‘maximum PGA allocated’
order by Mbytes desc,snap\_id desc)
where rownum <11;
NAME SNAP\_ID Mbytes
* * *
maximum PGA allocated 211 1,453
maximum PGA allocated 211 1,453
maximum PGA allocated 211 1,453
maximum PGA allocated 211 1,453
maximum PGA allocated 211 1,453
maximum PGA allocated 204 595
maximum PGA allocated 204 595
maximum PGA allocated 204 595
maximum PGA allocated 204 595
maximum PGA allocated 204 595
10 rows selected.
复制这个基于 VSESSION 和 VPROCESS 的查询提供了 CDB 和 PDBs 的所有进程的信息总结，包含 OS ID 和 Oracle id，在 v$session 中定义的会话状态，Oracle 和 OS 登陆用户名，及程序信息。
这个结果集以容器 id 和每个容器的当前所有 pga 分配的总和进行分组，以当前分配的内存进行升序排序, 最大值放在底部靠近合计。
如果不想显示后台进程的信息，可以去掉 AND 字句的注释（AND p.background is null）。
下面这个例子展示了一个 MMON 进程的 pga 最大的使用达到 24MB； PDB 4 正在使用 3MB，PDB 6 当前的使用是 4MB。
break on con\_id skip 4
compute sum of pga\_alloc\_mem on con\_id
SELECT p.con\_id,
p.spid,
p.pid,
s.sid,
s.serial#,
s.status,
ROUND(p.pga\_alloc\_mem/1024/1024) as pga\_alloc\_mem,
ROUND(p.pga\_used\_mem/1024/1024) as pga\_used\_mem,
ROUND(p.PGA\_MAX\_MEM/1024/1024) as pga\_max\_mem,
s.username,
s.osuser,
s.program
FROM v$process p, v$session s
WHERE s.paddr( + ) = p.addr
–AND p.background is null /\* Remove prevent listing background processes \*/
ORDER BY con\_id,pga\_alloc\_mem;
break on off
CON\_ID OSpid Orapid Sess id Serial# Status PGA alloc PGA used PGA Max PNAME oracleuser OS user Program
* * *
0 1 0 0 0
3809 33 1 1 1 P002
3673 6 3 1 ACTIVE 1 1 1 MMAN oracle oracle@localhost.localdo
main (MMAN)
3681 8 4 1 ACTIVE 1 1 1 DIAG oracle oracle@localhost.localdo
main (DIAG)
3655 2 1 1 ACTIVE 1 1 1 PMON oracle oracle@localhost.localdo
main (PMON)
3663 4 2 1 ACTIVE 1 1 1 VKTM oracle oracle@localhost.localdo
main (VKTM)
3813 34 1 1 1 P003
3821 36 1 1 1 P005
3741 22 1 1 1 S000
3737 21 1 1 1 D000
3817 35 1 1 1 P004
3773 24 1 0 4 P000
3777 25 1 0 4 P001
3829 38 1 1 1 P007
3825 37 1 1 1 P006
9983 43 282 3795 ACTIVE 1 1 1 Q002 oracle oracle@localhost.localdo
main (Q002)
4132 75 268 7 ACTIVE 1 1 1 SMCO oracle oracle@localhost.localdo
main (SMCO)
3709 14 7 1 ACTIVE 1 1 1 LG00 oracle oracle@localhost.localdo
main (LG00)
3725 18 9 1 ACTIVE 1 1 1 LREG oracle oracle@localhost.localdo
main (LREG)
3733 20 10 1 ACTIVE 1 1 1 MMNL oracle oracle@localhost.localdo
main (MMNL)
3781 26 11 5 ACTIVE 1 1 1 TMON oracle oracle@localhost.localdo
main (TMON)
3797 30 14 1 ACTIVE 1 1 1 QM01 oracle oracle@localhost.localdo
main (QM01)
3659 3 238 1 ACTIVE 1 1 1 PSP0 oracle oracle@localhost.localdo
main (PSP0)
3669 5 239 1 ACTIVE 1 1 1 GEN0 oracle oracle@localhost.localdo
main (GEN0)
3685 9 240 3 ACTIVE 1 1 1 OFSD oracle oracle@localhost.localdo
main (OFSD)
3705 13 243 1 ACTIVE 1 1 1 CKPT oracle oracle@localhost.localdo
main (CKPT)
3713 15 244 1 ACTIVE 1 1 1 LG01 oracle oracle@localhost.localdo
main (LG01)
3785 27 249 5 ACTIVE 1 1 1 TT00 oracle oracle@localhost.localdo
main (TT00)
3793 29 251 1 ACTIVE 1 1 1 AQPC oracle oracle@localhost.localdo
main (AQPC)
3801 31 253 1 ACTIVE 1 1 1 Q001 oracle oracle@localhost.localdo
main (Q001)
10354 23 263 5027 ACTIVE 1 1 1 W000 oracle oracle@localhost.localdo
main (W000)
3717 16 8 1 ACTIVE 2 1 2 SMON oracle oracle@localhost.localdo
main (SMON)
3693 10 5 1 ACTIVE 2 2 2 DIA0 oracle oracle@localhost.localdo
main (DIA0)
3721 17 245 1 ACTIVE 2 1 2 RECO oracle oracle@localhost.localdo
main (RECO)
3689 7 241 1 ACTIVE 2 1 2 DBRM oracle oracle@localhost.localdo
main (DBRM)
3789 28 12 5 ACTIVE 4 3 4 FBDA oracle oracle@localhost.localdo
main (FBDA)
3873 32 17 9 ACTIVE 7 2 8 CJQ0 oracle oracle@localhost.localdo
main (CJQ0)
3697 11 242 1 ACTIVE 7 7 7 DBW0 oracle oracle@localhost.localdo
main (DBW0)
3701 12 6 1 ACTIVE 11 11 11 LGWR oracle oracle@localhost.localdo
main (LGWR)
3729 19 246 1 ACTIVE 24 14 24 MMON oracle oracle@localhost.localdo
main (MMON)
\*\*\*\*\*\* ------------
sum 91
1 4488 42 51 7 ACTIVE 3 3 4 SYS oracle sqlplus@localhost.locald
omain (TNS V1-V3)
\*\*\*\*\*\* ------------
sum 3
4 4510 40 19 9 INACTIVE 3 2 7 SYS oracle sqlplus@localhost.locald
omain (TNS V1-V3)
\*\*\*\*\*\* ------------
sum 3
6 4592 44 47 23 INACTIVE 4 2 13 SYS oracle sqlplus@localhost.locald
omain (TNS V1-V3)
\*\*\*\*\*\* ------------
sum 4
复制下面的查询提供 SGA 和 PGA 相关的参数的设定。如果 sga_target和pga_aggregate_target 的值是零，并且 memory_target 大于零，那么这些值将通过 AMM 自动被设置。
–user defined parameters
select con\_id, name as Parameter, value/1024/1024 as Mbytes from v$parameter
where name in (‘pga\_aggregate\_target’,‘memory\_target’,‘memory\_max\_target’,‘sga\_max\_size’,‘sga\_target’,‘pga\_aggregate\_limit’)
order by name;
CON\_ID PARAMETER Mbytes
* * *
1 memory_max_target 600
1 memory_target 600
1 pga_aggregate_limit 4,096
1 pga_aggregate_target 0
1 sga_max_size 600
1 sga_target 0
复制上面查询 PGA 的语句可以和查询 SGA 视图的语句结合在一起，来判断 CDB 的总体内存使用情况。关于对 SGA 内存的使用和实例总体内存使用的讨论，请参考文档： Document: 1516229.1 How to Monitor SGA Memory on Pluggable Database for a discussion on determining sga memory usage and total instance memory usage。
参考
NOTE:399497.1 - FAQ: ORA-4030 [Video]
