内存监控脚本

/以下代码使用SYSDBA用户执行/
--开启代理
SP_INIT_JOB_SYS(1);
--开启内存泄漏检测,怀疑存在内存泄漏时再开启，生产环境慎用
ALTER SYSTEM SET 'MEMORY_LEAK_CHECK'=1 ;

create user DM_USER IDENTIFIED by "Dm_user123456";
GRANT RESOURCE TO DM_USER;
GRANT SELECT ON V$MEM_POOL TO DM_USER;
GRANT SELECT ON V$BUFFERPOOL TO DM_USER;
GRANT SELECT ON V$SQL_STAT TO DM_USER;
GRANT SELECT ON V$MEM_REGINFO TO DM_USER;
GRANT SELECT ON V$SYSSTAT TO DM_USER;
GRANT ADMIN JOB TO DM_USER;

/以下代码使用DM_USER用户执行/
DROP TABLE IF EXISTS DM_USER.TOOL_MEM_POOL;
DROP TABLE IF EXISTS DM_USER.TOOL_BUFFERPOOL;
DROP TABLE IF EXISTS DM_USER.TOOL_MEM_BUFFER;
DROP TABLE IF EXISTS DM_USER.TOOL_SQL_STAT;
DROP TABLE IF EXISTS DM_USER.TOOL_MEM_REGINFO;
DROP TABLE IF EXISTS DM_USER.TOOL_SYSSTAT;

CREATE TABLE DM_USER.TOOL_MEM_POOL AS
SELECT SYSDATE TJ_TIME,
NAME, --内存池名称
IS_SHARED, --是否是共享的
IS_OVERFLOW, --是否用到了备份池
ORG_SIZE/1024.0/1024.0 ORG_SIZE, --内存池初始大小
TOTAL_SIZE/1024.0/1024.0 TOTAL_SIZE, --内存池总大小(包括扩展的)
RESERVED_SIZE/1024.0/1024.0 RESERVED_SIZE, --当前已分配大小(包括扩展的)
DATA_SIZE/1024.0/1024.0 DATA_SIZE, --实际有效字节
EXTEND_SIZE, --每次扩展多少
TARGET_SIZE, --目标大小
N_EXTEND_NORMAL , --TARGET范围内累计扩展次数
N_EXTEND_EXCLUSIVE --超过TARGET累计扩展次数
FROM V$MEM_POOL
WHERE 1=2;

create index ind_TJ_TIME_01 on DM_USER.TOOL_MEM_POOL
(TJ_TIME desc
) ONLINE;

CREATE TABLE DM_USER.TOOL_BUFFERPOOL AS
SELECT SYSDATE TJ_TIME,
NAME, --缓冲区名称
N_PAGES, --页数
FREE, --空闲页数目
N_DISCARD64 --淘汰的页数
FROM V$BUFFERPOOL
WHERE 1=2;

create index ind_TJ_TIME_02 on DM_USER.TOOL_BUFFERPOOL
(TJ_TIME desc
) ONLINE;

CREATE TABLE DM_USER.TOOL_MEM_BUFFER AS
SELECT SYSDATE TJ_TIME, (SELECT SUM(N_PAGES * PAGE_SIZE)/1024/1024 FROM V$BUFFERPOOL)||'MB' AS BUFFER_SIZE, ( SELECT SUM(TOTAL_SIZE)/1024/1024 FROM V$MEM_POOL)||'MB' AS MEM_POOL, (SELECT SUM(N_PAGES * PAGE_SIZE)/1024/1024 FROM V$BUFFERPOOL)+(SELECT SUM(TOTAL_SIZE)/1024/1024 FROM V$MEM_POOL)||'MB' AS TOTAL_SIZE
FROM DUAL
WHERE 1=2;

create index ind_TJ_TIME_03 on DM_USER.TOOL_MEM_BUFFER
(TJ_TIME desc
) ONLINE;

CREATE TABLE DM_USER.TOOL_SQL_STAT AS
SELECT SYSDATE TJ_TIME,
"SESSID",
MAX_MEM_USED/1024||'MB',
SQL_TXT
FROM V$SQL_STAT
WHERE MAX_MEM_USED>1024
AND 1=2;

create index ind_TJ_TIME_04 on DM_USER.TOOL_SQL_STAT
(TJ_TIME desc
) ONLINE;

CREATE TABLE DM_USER.TOOL_MEM_REGINFO AS
SELECT SYSDATE TJ_TIME,A.* FROM V$MEM_REGINFO A WHERE 1=2;

create index ind_TJ_TIME_05 on DM_USER.TOOL_MEM_REGINFO
(TJ_TIME desc
) ONLINE;

CREATE TABLE DM_USER.TOOL_SYSSTAT AS
SELECT SYSDATE TJ_TIME,
NAME ,
STAT_VAL/1024.0/1024.0 STAT_VAL_MB
FROM V$SYSSTAT
WHERE CLASSID=11
AND 1=2;

create index ind_TJ_TIME_06 on DM_USER.TOOL_SYSSTAT
(TJ_TIME desc
) ONLINE;

CREATE OR REPLACE
PROCEDURE P_STAT_MEM_BUFF
AS
/变量说明部分/
V_TJ_TIME DATETIME;
BEGIN
/执行体/
SELECT SYSDATE DUAL INTO V_TJ_TIME;

INSERT INTO DM_USER.TOOL_MEM_POOL
SELECT V_TJ_TIME TJ_TIME,
       NAME,                                      --内存池名称
       IS_SHARED,                                 --是否是共享的
       IS_OVERFLOW,                               --是否用到了备份池
       ORG_SIZE/1024.0/1024.0 ORG_SIZE,           --内存池初始大小
       TOTAL_SIZE/1024.0/1024.0 TOTAL_SIZE,       --内存池总大小(包括扩展的)
       RESERVED_SIZE/1024.0/1024.0 RESERVED_SIZE, --当前已分配大小(包括扩展的)
       DATA_SIZE/1024.0/1024.0 DATA_SIZE,         --实际有效字节
       EXTEND_SIZE,                               --每次扩展多少
       TARGET_SIZE,                               --目标大小
       N_EXTEND_NORMAL ,                          --TARGET范围内累计扩展次数
       N_EXTEND_EXCLUSIVE                         --超过TARGET累计扩展次数
  FROM V$MEM_POOL
 WHERE 1=1;

COMMIT;
INSERT INTO DM_USER.TOOL_BUFFERPOOL
SELECT V_TJ_TIME TJ_TIME,
       NAME,       --缓冲区名称
       N_PAGES,    --页数
       FREE,       --空闲页数目
       N_DISCARD64 --淘汰的页数
  FROM V$BUFFERPOOL
 WHERE 1=1;

COMMIT;
INSERT INTO DM_USER.TOOL_MEM_BUFFER
SELECT V_TJ_TIME TJ_TIME, (SELECT SUM(N_PAGES * PAGE_SIZE)/1024/1024 FROM V$BUFFERPOOL)||'MB' BUFFER_SIZE, ( SELECT SUM(TOTAL_SIZE)/1024/1024 FROM V$MEM_POOL)||'MB' MEM_POOL, (SELECT SUM(N_PAGES * PAGE_SIZE)/1024/1024 FROM V$BUFFERPOOL)+(SELECT SUM(TOTAL_SIZE)/1024/1024 FROM V$MEM_POOL)||'MB' TOTAL_SIZE
  FROM DUAL
 WHERE 1=1;

COMMIT;
INSERT INTO DM_USER.TOOL_SQL_STAT
SELECT V_TJ_TIME TJ_TIME,
       "SESSID",
       MAX_MEM_USED/1024||'MB',
       SQL_TXT
  FROM V$SQL_STAT
 WHERE MAX_MEM_USED>1024
   AND 1=1;

COMMIT;
INSERT INTO DM_USER.TOOL_MEM_REGINFO
SELECT V_TJ_TIME TJ_TIME,A.* FROM V$MEM_REGINFO A WHERE 1=1;

COMMIT;
INSERT INTO DM_USER.TOOL_SYSSTAT
SELECT V_TJ_TIME TJ_TIME,
       NAME ,
       STAT_VAL/1024.0/1024.0 STAT_VAL_MB
  FROM V$SYSSTAT
 WHERE CLASSID=11
   AND 1=1;

COMMIT;

END;
--CALL P_STAT_MEM_BUFF();

--设置作业每分钟统计一次内存使用情况
call SP_CREATE_JOB('stat_mem_buffer',1,0,'',0,0,'',0,'数据库内存统计分析作业');
call SP_JOB_CONFIG_START('stat_mem_buffer');
call SP_ADD_JOB_STEP('stat_mem_buffer', 'stat_mem_buffer', 0, 'call P_STAT_MEM_BUFF();', 1, 1, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('stat_mem_buffer', 'stat_mem_buffer', 1, 1, 1, 0, 1, '00:00:00', '23:59:59', '2023-05-05 11:39:22', NULL, '');
call SP_JOB_CONFIG_COMMIT('stat_mem_buffer');

select * from DM_USER.TOOL_MEM_POOL order by TJ_TIME desc;
select * from DM_USER.TOOL_BUFFERPOOL order by TJ_TIME desc;
select * from DM_USER.TOOL_MEM_BUFFER order by TJ_TIME desc;
select * from DM_USER.TOOL_SQL_STAT order by TJ_TIME desc;
select * from DM_USER.TOOL_MEM_REGINFO order by TJ_TIME desc;
select * from DM_USER.TOOL_SYSSTAT order by TJ_TIME desc;

作者