问题背景：

        1、客户环境有一个存储过程，代码量在2w行左右。

        2、在运行过程中，存在卡顿运行缓慢等问题。通过性能监视工具，可以定位到存储过程中卡顿的SQL位置。

        3、其中问题SQL与delete、insert select * from table这的类型语句有关，怀疑存在显著占用内存峰值的潜在危险。

        4、建议临时将查询结果存入临时表，在通过delete、insert增删数据。存储过程性能得到提升。

分析优化和测试过程记录：

1、创建大字段测试表

create table test_big_var1 (id number,name varchar2(5000));
create table test_big_var2 (id number,name varchar2(5000));
create table test_big_var3 (id number,name varchar2(5000));
create table test_big_var4 (id number,name varchar2(5000));

create sequence test_big_var_s1 start with 1 increment by 1;
create sequence test_big_var_s2 start with 1 increment by 1;
create sequence test_big_var_s3 start with 1 increment by 1;
create sequence test_big_var_s4 start with 1 increment by 1;

--对4张表插入测试数据
declare
	i int;
begin
	i=0;
	for i in 1..50000 LOOP
		insert into test_big_var1 VALUES (test_big_var_s1.nextval,'FAS……FAS');--5000字符串
	end loop;
	commit;
end;

--创建交叉查询表
create table test_big_var_x3 (id number,name1 varchar2(6000),name2 varchar2(6000),name3 varchar2(6000),name4 varchar2(6000));
create table test_big_var_x2 (id number,name1 varchar2(6000),name2 varchar2(6000),name3 varchar2(6000));
create table test_big_var_x (id number,name1 varchar2(6000),name2 varchar2(6000));

每张表的空间占用情况

2、创建存储过程（模拟客户存储过程运行环境）

create or replace procedure p_test_big_var_x()
is 
begin
--两表连接
	delete test_big_var_x where id in 
	(select a.id from test_big_var a,test_big_var2 b where a.id = b.id);
	commit;
	insert into test_big_var_x 
	select * from (
	select a.id,a.name as "aname",b.name as "bname" from test_big_var a,test_big_var2 b where a.id = b.id
	) s1;
	commit;
--三表连接
	delete test_big_var_x2 where id in 
	(select a.id from test_big_var a,test_big_var2 b,test_big_var3 c where a.id = b.id and b.id = c.id);
	commit;
	insert into test_big_var_x2 
	select * from (
	select a.id,a.name as "aname",b.name as "bname",c.name as "cname" from test_big_var a,test_big_var2 b,test_big_var3 c where a.id = b.id and b.id = c.id
	) s1;
	commit;
--四表连接
	delete test_big_var_x3 where id in 
	(select a.id from test_big_var a,test_big_var2 b,test_big_var3 c,test_big_var4 d where a.id = b.id and b.id = c.id and d.id=c.id);
	commit;
	insert into test_big_var_x3 
	select * from (
	select a.id,a.name as "aname",b.name as "bname",c.name as "cname",d.name as "dname" 
	from test_big_var a,test_big_var2 b,test_big_var3 c,test_big_var4 d 
	where a.id = b.id and b.id = c.id and d.id = c.id
	) s1;
	commit;
end;

3、执行观察

打开性能监视工具，通过这种方式可以观察定位到存储过程中执行sql存在的性能问题

查询每个会话执行的内存占用大小

SELECT SESSID; --查询当前会话id

SELECT
	S.SESS_ID,
	M.CREATOR  ,
	S.SQL_TEXT                    ,
	M.NAME                        ,
	M.TOTAL_SIZE/1024/1024 TOTAL_M,
	M.DATA_SIZE /1024/1024 DATA_SIZE_M,
	S.STATE,
	S.CLNT_IP,
	S.CLNT_VER,
	S.CLNT_HOST,
	S.APPNAME,
	S.OSNAME,
	S.RUN_STATUS,
	S.MSG_STATUS,
	S.LAST_RECV_TIME,
	S.LAST_SEND_TIME,
	SYSDATE()
FROM
V$MEM_POOL M,
V$SESSIONS S
WHERE
M.CREATOR = S.THRD_ID
and S.SESS_ID = '&当前会话ID'
ORDER BY TOTAL_M DESC;

两表连接，内存使用情况

三表连接，内存使用情况

四表连接，内存使用情况

4、尝试调节MAX_SESSION_MEMORY

select * from v$dm_ini where para_name like '%MAX_SESSION%';

sp_set_para_value(1,'MAX_SESSION_MEMORY',50);--两表连接报错

sp_set_para_value(1,'MAX_SESSION_MEMORY',200);--三表连接报错

sp_set_para_value(1,'MAX_SESSION_MEMORY',450);--四表连接报错

如果不做MAX_SESSION_MEMORY限制，是会存在单一会话内存使用过高的风险。

5、优化存储过程，使用显示游标的方式改写

create or replace procedure p_test_big_var_x()
is 
	type TABLE_TT is table of test_big_var_x%ROWTYPE;
	type TABLE_TT2 is table of test_big_var_x2%ROWTYPE;
	type TABLE_TT3 is table of test_big_var_x3%ROWTYPE;

	V_TT TABLE_TT;
	V_TT2 TABLE_TT2;
	V_TT3 TABLE_TT3;

	V_CUR SYS_REFCURSOR;
	v_limit_nr constant binary_integer:=10000;
begin

	EXECUTE immediate 'truncate table test_big_var_x3';
	EXECUTE immediate 'truncate table test_big_var_x2';
	EXECUTE immediate 'truncate table test_big_var_x';
--两表连接
	--delete test_big_var_x where id in 
	--(select a.id from test_big_var a,test_big_var2 b where a.id = b.id);
	--commit;


	OPEN V_CUR FOR select a.id,a.name as "aname",b.name as "bname" from test_big_var a,test_big_var2 b where a.id = b.id;
	LOOP
		fetch V_CUR BULK COLLECT into V_TT limit v_limit_nr;
		exit when V_TT.count()=0;
		for i in V_TT.first..V_TT.last LOOP
			INSERT INTO test_big_var_x VALUES V_TT(i);
		end loop;
		exit when V_TT.count<v_limit_nr;
	end loop;
	close V_CUR;

--三表连接
	--delete test_big_var_x2 where id in 
	--(select a.id from test_big_var a,test_big_var2 b,test_big_var3 c where a.id = b.id and b.id = c.id);
	--commit;
	
	OPEN V_CUR FOR 	select a.id,a.name as "aname",b.name as "bname",c.name as "cname" 
	from test_big_var a,test_big_var2 b,test_big_var3 c where a.id = b.id and b.id = c.id;

	LOOP
		fetch V_CUR BULK COLLECT into V_TT2 limit v_limit_nr;
		exit when V_TT2.count()=0;
		for i in V_TT2.first..V_TT2.last LOOP
			INSERT INTO test_big_var_x2 VALUES V_TT2(i);
		end loop;
		exit when V_TT2.count<v_limit_nr;
	end loop;
	close V_CUR;	

--四表连接
	--delete test_big_var_x3 where id in 
	--(select a.id from test_big_var a,test_big_var2 b,test_big_var3 c,test_big_var4 d where a.id = b.id and b.id = c.id and d.id=c.id);
	--commit;
	
	OPEN V_CUR FOR 	select a.id,a.name as "aname",b.name as "bname",c.name as "cname",d.name as "dname" 
	from test_big_var a,test_big_var2 b,test_big_var3 c,test_big_var4 d 
	where a.id = b.id and b.id = c.id and d.id = c.id;

	LOOP
		fetch V_CUR BULK COLLECT into V_TT3 limit v_limit_nr;
		exit when V_TT3.count()=0;
		for i in V_TT3.first..V_TT3.last LOOP
			INSERT INTO test_big_var_x3 VALUES V_TT3(i);
		end loop;
		exit when V_TT3.count<v_limit_nr;
	end loop;
	close V_CUR;
	
end;

会话使用内存大小

        这种显著占用内存的峰值可能是危险的，因为他们可能会减慢其他活动。这意味着，适用于连接数量少且负载重条件下的数据迁移问题的完美解决方案，对有大量并发连接的情况可能并不是最优的。这是性能和资源利用率之间的平衡问题。

        显示游标的方式避免了内存的滥用而不知的情况，但如果表是远程表，也会使得往返次数加倍。这意味着在找出针对处理最有效的限制过程中，总有折中。取决于本地环境的很多参数，如硬件配置、网络配置、总负载、峰值负载等。从经验来说，获得最佳性能的最好机会是每次取100行到10000行之间（其中已经包含使用太多服务器内存的危险）

https://eco.dameng.com