pdb上查看进程内存

pdb的引入，大家共享一个实例，有时候需要弄清除到底谁的会话导致占用内存高呢？
以下几板斧，看个清楚。

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

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
--确认当前是否是cdb
select name, cdb, con_id from v$database;
--查询每个pdb状态

--查询大于20M内存的会话
REM v$sesstat pga memory over 20MB size
SELECT NAME, CON_ID, OPEN_MODE, RESTRICTED, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

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

-- 某些进程最大占用内存
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%');

--所有pga合计
SELECT ROUND(SUM(pga_alloc_mem)/1024/1024) AS "Mbytes allocated", ROUND(SUM(PGA_USED_MEM)/1024/1024) AS "Mbytes used"
FROM v$process;

--各pdb占用内存
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

--合计当前内存
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';

--各pdb当前内存汇总
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

--实例启动以来，最大pga分配
col name for a50
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');

-- pga分配历史最高前10

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;

--查看当前各今晨分配内存情况
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

-- sga pga设置

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;

--显示动态组件的当前值
select con_id, component, ROUND(USER_SPECIFIED_SIZE/1024/1024) as Mbytes, OPER_COUNT, LAST_OPER_TIME as "Last op time", ROUND(current_size/1024/1024) as Mbytes from v$memory_dynamic_components
order by component;

--通过数据库视图显示 SGA 内存使用情况
select name, ROUND(bytes/1024/1024) as Mbytes from v$sgainfo;

-显示可用来扩展 sga_target 的可用空闲 sga 内存
select ROUND(current_size/1024/1024) Mbytes from V$SGA_DYNAMIC_FREE_MEMORY;

--只显示超过 10M 的内存分配
break on con_id skip 4
select con_id, pool, name, ROUND(bytes/1024/1024,1) as Mbytes from v$sgastat
where bytes/1024/1024 > 10 --alter to see smaller/larger allocations
order by con_id;
break on off

参考：
在可插拔数据库上如何监控进程内存的使用 (Doc ID 1985042.1)
如何监控可插拔数据库（PDB）的 SGA 内存使用情况 (Doc ID 2248684.1)