达梦内存等资源持续监控

有些情况下需要监控某一段存储过程中数据库资源使用情况来定位分析问题

--### 监控dmserver的内存\cpu变化情况（2s收集一次）
for rs in {1..1000};do a=`date;top -bc -n 1|grep dmserver|grep -v grep`;echo $a;sleep 2;done | tee -a vm.`date +%F`.log

--需抓下报错代码对应的堆栈
方法如下：
SP_ELOG_ERR_ADD(-7196,1 );
执行会报错的sql
SP_ELOG_ERR_CLEAR();
查看数据库日志

 

创建需要监控的表

CREATE table dm_t1 as select sysdate as stdate, * from V$MEM_POOL ;
CREATE table dm_t2 as
select sysdate as stdate,regexp_replace(name,'[0-9]'),
count(*),
trunc(sum((org_size /1024.0/1024))) 初始,
trunc(sum((data_size /1024.0/1024))) 在用,
trunc(sum((total_size /1024.0/1024))) 总的,
trunc(sum((target_size /1024.0/1024))) 水位
from v$mem_pool
group by regexp_replace(name,'[0-9]')
order by 总的 desc;

CREATE table dm_t3 as
select
sysdate as stdate,
(select sum(cast(n_pages as bigint)* page_size)/1024/1024 from v$bufferpool)||'MB' as BUFFER_SIZE,
( select sum(cast(total_size as bigint))/1024/1024 from v$mem_pool)||'MB' as mem_pool,
(select sum(cast(n_pages as bigint) * page_size)/1024/1024 from v$bufferpool)+(select sum(cast(total_size as bigint))/1024/1024 from v$mem_pool)||'MB' as TOTAL_SIZE
From dual;

CREATE table dm_t4 as select sysdate as stdate, * from V$MEM_REGINFO ORDER BY REFNUM DESC ;
CREATE table dm_t5 as select sysdate as stdate, * from V$sysstat ;

定时监控使用情况，每隔10s将监控数据写入表中

declare i int:=1;
begin
while i<=720 LOOP
--内存池使用情况
insert into dm_t1 select sysdate as stdate, * from V$MEM_POOL ;
--内存使用情况2
insert into dm_t2

select sysdate as stdate,regexp_replace(name,'[0-9]'),

count(*),

trunc(sum((org_size /1024.0/1024))) 初始,

trunc(sum((data_size /1024.0/1024))) 在用,

trunc(sum((total_size /1024.0/1024))) 总的,

trunc(sum((target_size /1024.0/1024))) 水位

from v$mem_pool

group by regexp_replace(name,'[0-9]')

order by 总的 desc;

--数据库总内存使用情况

insert into dm_t3

select

sysdate as stdate,

(select sum(cast(n_pages as bigint)* page_size)/1024/1024 from v$bufferpool)||'MB' as BUFFER_SIZE,

( select sum(cast(total_size as bigint))/1024/1024 from v$mem_pool)||'MB' as mem_pool,

(select sum(cast(n_pages as bigint) * page_size)/1024/1024 from v$bufferpool)+(select sum(cast(total_size as bigint))/1024/1024 from v$mem_pool)||'MB' as TOTAL_SIZE

From dual;

--如果看到REFNUM值特别的大，一直不会变小，说明该内存存在堆积，需要具体的分析。其中fname指定了该内存池的内存来自哪个文件，lineno指定是在该文件哪一行
-- alter system set 'MEMORY_LEAK_CHECK'=1 ; --打开内存泄漏参数
insert into dm_t4 select sysdate as stdate, * from V$MEM_REGINFO ORDER BY REFNUM DESC ;

insert into dm_t5 select sysdate as stdate, * from V$sysstat ;

commit;

sleep(10);

i=i+1;

end LOOP;

end;

/