pdb上查看进程内存-凯发app官方网站

凯发app官方网站-凯发k8官网下载客户端中心 | | 凯发app官方网站-凯发k8官网下载客户端中心
  • 博客访问: 3502590
  • 博文数量: 718
  • 博客积分: 1860
  • 博客等级: 上尉
  • 技术积分: 7790
  • 用 户 组: 普通用户
  • 注册时间: 2008-04-07 08:51
个人简介

偶尔有空上来看看

文章分类

全部博文(718)

文章存档

2024年(4)

2023年(74)

2022年(134)

2021年(238)

2020年(115)

2019年(11)

2018年(9)

2017年(9)

2016年(17)

2015年(7)

2014年(4)

2013年(1)

2012年(11)

2011年(27)

2010年(35)

2009年(11)

2008年(11)

最近访客
相关博文
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·

分类: oracle

2021-07-03 22:50:39

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

  1. set linesize 150
  2. set pagesize 3000
  3. set numwidth 15

  4. col parameter format a30
  5. col component format a28
  6. column default_attr format a7
  7. column owner format a15
  8. column object_name format a15
  9. column all_containers format a3
  10. column container_name format a10
  11. column con_id format 999
  12. column pdb_name format a20
  13. column memory format a25
  14. column spid heading 'ospid' format a8
  15. column pid heading 'orapid' format 999999
  16. column sid heading 'sess id' format 99999
  17. column serial# heading 'serial#' format 999999
  18. column status heading 'status' format a8
  19. column pga_alloc_mem heading 'pga alloc' format 999,999,999
  20. column pga_used_mem heading 'pga used' format 999,999,999
  21. column pga_max_mem heading 'pga max' format 999,999,999
  22. column username heading 'oracleuser' format a12
  23. column osuser heading 'os user' format a12
  24. column program heading 'program' format a24
  25. column mbytes heading 'mbytes' format 999,999,999
  26. column name format a22

  27. alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
  28. --确认当前是否是cdb
  29. select name, cdb, con_id from v$database;
  30. --查询每个pdb状态

  31. --查询大于20m内存的会话
  32. rem v$sesstat pga memory over 20mb size
  33. select name, con_id, open_mode, restricted, dbid, con_uid, guid from v$containers order by con_id;

  34. break on spid skip 1

  35. select p.spid, s.sid, p.con_id, substr(n.name,1,25) memory, round(s.value/1024/1024) as mbytes
  36. from v$sesstat s, v$statname n, v$process p, v$session vs
  37. where s.statistic# = n.statistic#
  38. and n.name like '%pga memory%'
  39. and s.sid=vs.sid
  40. and vs.paddr=p.addr
  41. and s.value > 20000000 /* --remove this line to view all process size */
  42. order by spid,memory;

  43. break on off

  1. -- 某些进程最大占用内存
  2. 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
  3. from v$process
  4. where pga_alloc_mem = (select max(pga_alloc_mem)
  5.                        from v$process
  6.                        where program not like '%lgwr%');

  1. --所有pga合计
  2. select round(sum(pga_alloc_mem)/1024/1024) as "mbytes allocated", round(sum(pga_used_mem)/1024/1024) as "mbytes used"
  3. from v$process;


  1. --各pdb占用内存
  2. compute sum of "mbytes allocated" on report
  3. break on report
  4. select con_id, round(sum(pga_alloc_mem)/1024/1024) as "mbytes allocated", round(sum(pga_used_mem)/1024/1024) as "mbytes used"
  5. from v$process
  6. group by con_id
  7. order by con_id;

  8. break on off

  1. --合计当前内存
  2. select round(sum(value)/1024/1024) as mbytes
  3. from v$sesstat s, v$statname n
  4. where n.statistic# = s.statistic#
  5. and n.name = 'session pga memory';

  1. --各pdb当前内存汇总
  2. compute sum of mbytes on report
  3. break on report
  4. select con_id, round(sum(bytes)/1024/1024) as mbytes from (select p.con_id, s.value as bytes
  5. from v$sesstat s, v$statname n, v$process p, v$session vs
  6. where s.statistic# = n.statistic#
  7. and n.name = 'session pga memory'
  8. and s.sid=vs.sid
  9. and vs.paddr=p.addr)
  10. group by con_id
  11. order by con_id;

  12. break on off


  1. --实例启动以来,最大pga分配
  2. col name for a50
  3. select name, round(value/1024/1024) as mbytes from v$pgastat
  4. where name in ('maximum pga allocated','aggregate pga target parameter','aggregate pga auto target');

  1. -- pga分配历史最高前10

  2. select * from (select name,snap_id, round(value/1024/1024) mbytes from cdb_hist_pgastat
  3. where name='maximum pga allocated'
  4. order by mbytes desc,snap_id desc)
  5. where rownum <11;


  1. --查看当前各今晨分配内存情况
  2. break on con_id skip 4
  3. compute sum of pga_alloc_mem on con_id
  4. select p.con_id,
  5.        p.spid,
  6.        p.pid,
  7.        s.sid,
  8.        s.serial#,
  9.        s.status,
  10.        round(p.pga_alloc_mem/1024/1024) as pga_alloc_mem,
  11.        round(p.pga_used_mem/1024/1024) as pga_used_mem,
  12.        round(p.pga_max_mem/1024/1024) as pga_max_mem,
  13.        s.username,
  14.        s.osuser,
  15.        s.program
  16. from v$process p, v$session s
  17. where s.paddr( ) = p.addr
  18. --and p.background is null /* remove prevent listing background processes */
  19. order by con_id,pga_alloc_mem;

  20. break on off

  1. -- sga pga设置

  2. select con_id, name as parameter, value/1024/1024 as mbytes from v$parameter
  3. where name in ('pga_aggregate_target','memory_target','memory_max_target','sga_max_size','sga_target','pga_aggregate_limit')
  4. order by name;

  1. --显示动态组件的当前值
  2. 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
  3. order by component;


  1. --通过数据库视图显示 sga 内存使用情况
  2. select name, round(bytes/1024/1024) as mbytes from v$sgainfo;


  1. -显示可用来扩展 sga_target 的可用空闲 sga 内存
  2. select round(current_size/1024/1024) mbytes from v$sga_dynamic_free_memory;

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

参考:
在可插拔数据库上如何监控进程内存的使用 (doc id 1985042.1)
如何监控可插拔数据库(pdb)的 sga 内存使用情况 (doc id 2248684.1)
阅读(1158) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
")); function link(t){ var href= $(t).attr('href'); href ="?url=" encodeuricomponent(location.href); $(t).attr('href',href); //setcookie("returnouturl", location.href, 60, "/"); }
网站地图