定期检查m000是否消耗pga

偶然发现一个库的m000占用pga较高，导致ora-4036问题

sho parameter ga 看看当前参数

好像是bug

需要定期检查

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')riqi from dual;

select round(sum(PGA_ALLOC_MEM)/1024/1024,2) "Total PGA Allocated (Mb)" from v$process p, v$session s where p.addr = s.paddr;

col username for a15
set lin 300 pages 100
col PROGRAM for a30
col PID_REMOTE for a20
col sid for a8
col status for a12
col spid for a8
select * from (
SELECT s.username, SUBSTR(s.sid,1,5) sid, p.spid, logon_time,
 SUBSTR(s.program,1,22) program , s.process pid_remote,
 s.status,
 ROUND(pga_used_mem/1024/1024) usme,
 ROUND(pga_alloc_mem/1024/1024) alme,
 ROUND(pga_freeable_mem/1024/1024) frme,
 ROUND(pga_max_mem/1024/1024) mame
FROM  v$session s,v$process p
WHERE p.addr=s.paddr --and p.pname in ('M000','M001','M002','M003','M004','M005','M006','M007','M008','M009')
ORDER BY mame DESC) where rownum<21;

--大于2048的m000进程就kill
set pagesize 9999 lin 250 echo off heading off verify off feedback off trims on
spool /tmp/kill_m000.sql
select 'alter system kill session '''||a.sid||','||a.serial#||''' immediate;' killcmd from v$session a where  sid in (
SELECT  s.sid  
FROM  v$session s,v$process p
WHERE p.addr=s.paddr and p.pname in ('M000','M001','M002','M003','M004','M005','M006','M007','M008','M009')
 and ROUND(p.pga_alloc_mem/1024/1024)>2048
 and s.status<>'KILLED'
);
spool off
@/tmp/kill_m000
exit


crontab 
*/30 * * * * /home/oracle/check_m000.sh

再观察