在可插拔数据库上如何监控进程内存的使用

网友投稿 602 2022-05-29

适用于:

Oracle Database - Enterprise Edition - 版本 12.1.0.1 和更高版本

Oracle Database Cloud Schema Service - 版本 N/A 和更高版本

Oracle Database Exadata Express Cloud Service - 版本 N/A 和更高版本

Oracle Database Exadata Cloud Machine - 版本 N/A 和更高版本

Oracle Cloud Infrastructure - Database Service - 版本 N/A 和更高版本

本文档所含信息适用于所有平台

解决方案

容器数据库(CDB)和所有的可插拔数据库(PDBs)共享一个单一数据库实例,这个实例由一个系统全局区(SGA)和一些后台进程组成。因为这种共享内存资源的特性,使得你可能非常想要去区分共享该实例的各个数据库,究竟各自都使用了多少资源。如果可能的话,我们还将提供一个查询,将各个每个可插拔数据库所使用的进程内存(PGA)也区分开。

使用了 ROUND 函数使得查询结果更容易用 MB 形式来展现;因为得到是四舍五入的值,所以得到的结果和直接查询 V$PROCESS 和 V$SESSTAT 得到的结果不能精确匹配。

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               

PDB$SEED                    2 READ ONLY  NO              

PDB_SS                      3 READ WRITE NO               

PDB1                        4 READ WRITE NO               

PDB_COPY                    5 MOUNTED                     

PDB2                        6 READ WRITE NO               

这个基于视图 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

这个基于 V$SESSTAT 的查询合计了合计了实例或 CDB 所有进程的当前分配的内存。这个值包含了 CDB 和所有 PDBs 的所有后台和前台进程。这是除了查询 v$process 之外的另一种方式来查看所有 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.

这个基于 V$SESSION 和 V$PROCESS 的查询提供了 CDB 和 PDBs 的所有进程的信息总结,包含 OS ID 和 Oracle id,在 v$session 中定义的会话状态,Oracle 和 OS 登陆用户名,及程序信息。

这个结果集以容器 id 和每个容器的当前所有 pga 分配的总和进行分组,以当前分配的内存进行升序排序, 最大值放在底部靠近合计。

如果不想显示后台进程的信息,可以去掉 AND 字句的注释(AND p.background is null)。

下面的查询提供 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 的总体内存使用情况。

任务调度 数据库

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:数学建模学习(68):机器学习训练模型的保存与模型使用
下一篇:华为云ModelArts入门开发(完成物体分类、物体检测)丨【华为云AI贺新年】
相关文章