1、创建查询结果记录表格，将查询结果插入相应结果表中。

create TABLE mem_pool_monitor(
name VARCHAR(128),
sum_total_size BIGINT,
sum_reserved_size BIGINT,
sum_data_size BIGINT,
avg_target_size DECIMAL(38,6),
sum_n_alloc BIGINT,
time1 TIMESTAMP
);

insert into MEM_POOL_MONITOR select name,sum(total_size),sum(reserved_size),sum(data_size),avg(target_size),sum(n_alloc),SYSDATE() from v$mem_pool group by name;
commit;

create TABLE bufferpool_monitor(
name VARCHAR(20),
sum_n_pages BIGINT,
sum_n_dirty BIGINT,
sum_n_clear BIGINT,
time1 TIMESTAMP
);
insert into bufferpool_monitor select name,sum(n_pages),sum(n_dirty),sum(n_clear),SYSDATE() from v$bufferpool group by name;
commit;

create TABLE mem1(
BUFFER_SIZE_MB BIGINT,
mem_pool_MB BIGINT,
total_size_MB BIGINT,
time1 TIMESTAMP);

insert into mem1 select
(select sum(n_pages * page_size)/1024/1024 from v$bufferpool) as "BUFFER_SIZE(MB)",
( select sum(total_size)/1024/1024 from v$mem_pool) as "mem_pool(MB)",
(select sum(n_pages * page_size)/1024/1024 from v$bufferpool)+(select sum(total_size)/1024/1024 from v$mem_pool) as "TOTAL_SIZE(MB)",
 SYSDATE()
From dual;
commit;

create TABLE mem3(
name VARCHAR(20),
org_size BIGINT,
total_size BIGINT,
target_size BIGINT,
time1 TIMESTAMP);

insert into mem3 select name,org_size/1024/1024,total_size/1024/1024, target_size/1024/1024,SYSDATE()
from  v$mem_pool  where  n_extend_exclusive >0  and  total_size>target_size ;
commit;

create table sessions_monitor(
num INT,
state VARCHAR(8),
time1 TIMESTAMP);

insert into sessions_monitor select count(0),state,SYSDATE() from v$sessions group by STATE;
commit；

2、创建数据库定时作业，定时执行以上insert语句，例如4小时执行一次（可以根据实际情况设置执行频率，也可以通过图形化工具进行创建）。

call SP_CREATE_JOB('monitor_mem',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('monitor_mem');

call SP_ADD_JOB_STEP('monitor_mem', 'mon_sql', 0, '--1
insert into MEM_POOL_MONITOR select name,sum(total_size),sum(reserved_size),sum(data_size),avg(target_size),sum(n_alloc),SYSDATE() from v$mem_pool group by name;
--2
insert into bufferpool_monitor select name,sum(n_pages),sum(n_dirty),sum(n_clear),SYSDATE() from v$bufferpool group by name;
--3
insert into mem1 select
(select sum(n_pages * page_size)/1024/1024 from v$bufferpool) as "BUFFER_SIZE(MB)",
( select sum(total_size)/1024/1024 from v$mem_pool) as "mem_pool(MB)",
(select sum(n_pages * page_size)/1024/1024 from v$bufferpool)+(select sum(total_size)/1024/1024 from v$mem_pool) as "TOTAL_SIZE(MB)",
 SYSDATE()
From dual;
--4
insert into mem3 select name,org_size/1024/1024,total_size/1024/1024, target_size/1024/1024,SYSDATE()
from  v$mem_pool  where  n_extend_exclusive >0  and  total_size>target_size ;
--5
insert into sessions_monitor select count(0),state,SYSDATE() from v$sessions group by STATE;
commit;', 0, 1, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('monitor_mem', 'mem_mon', 1, 1, 1, 0, 240, '00:00:00', '23:59:59', '2024-02-23 17:17:53', NULL, '');

call SP_JOB_CONFIG_COMMIT('monitor_mem');

3、待数据库内存使用率升高以后查询以上结果记录表格，分析问题。