我们库里有个语句占用了大量的内存,达到4.5G,该语句的整体架构是一样的,主要差别在于里面有四个in 例如: Id in ( 1,2 ),而in的值是动态变化的:
SQL> select sum(SHARABLE_MEM)/power(1024,3) from v$sql where substr(sql_text, 1, 40) = 'select id as ppppid, p2m.mllch';
SUM(SHARABLE_MEM)/POWER(1024,3)
-------------------------------
4.45558127
想法: 目前因为in是不定长,我们可以用重复的数据填充将其变为定长。即原来是 例如 id in (1,2,3),我们将其定长到20位, id in (1,2,3…………..3),后面补上17个3,这样少于20个的,都变为20个(或者50、70、80.。)。
以下是我对上面想法的测试(下面只是框架,相关的表名已去掉):
CREATE OR REPLACE PROCEDURE pro_test2 IS
v_str_sql varchar2(1000);
v_num1 number(18);
v_num2 number(18);
v_num3 number(18);
v_num4 number(18);
v_num5 number(18);
BEGIN
--两个参数
v_str_sql :='select count(*) from ( select Id .... in (:1, :2)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2) ) or ( CId in (:1, :2)) or (pCId in (:1, :2) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2,v_num1,v_num2,v_num1,v_num2,v_num1,v_num2 ;
end loop;
--三个参数
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3) ) or ( CId in (:1, :2,:3)) or (pCId in (:1, :2,:3) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
v_num3 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2 ,v_num3,v_num1,v_num2 ,v_num3,v_num1,v_num2 ,v_num3,v_num1,v_num2 ,v_num3;
end loop;
--四个参数
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3,:4)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3,:4) ) or ( CId in (:1, :2,:3,:4)) or (pCId in (:1, :2,:3,:4) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
v_num3 := trunc(dbms_random.value(10,999999)) ;
v_num4 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2 ,v_num3,v_num4,v_num1,v_num2 ,v_num3,v_num4,v_num1,v_num2 ,v_num3,v_num4,v_num1,v_num2 ,v_num3,v_num4;
end loop;
-------------定长
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3,:4,:5,:6)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3,:4,:5,:6) ) or ( CId in (:1, :2,:3,:4,:5,:6)) or (pCId in (:1, :2,:3,:4,:5,:6) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2,1,1,1,1,v_num1,v_num2,1,1,1,1,v_num1,v_num2,1,1,1,1,v_num1,v_num2,1,1,1,1;
end loop;
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3,:4,:5,:6)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3,:4,:5,:6) ) or ( CId in (:1, :2,:3,:4,:5,:6)) or (pCId in (:1, :2,:3,:4,:5,:6) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
v_num3 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2 ,v_num3,1,1,1,v_num1,v_num2 ,v_num3,1,1,1,v_num1,v_num2 ,v_num3,1,1,1,v_num1,v_num2 ,v_num3,1,1,1;
end loop;
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3,:4,:5,:6)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3,:4,:5,:6) ) or ( CId in (:1, :2,:3,:4,:5,:6)) or (pCId in (:1, :2,:3,:4,:5,:6) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
v_num3 := trunc(dbms_random.value(10,999999)) ;
v_num4 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2 ,v_num3,v_num4,1,1,v_num1,v_num2 ,v_num3,v_num4,1,1,v_num1,v_num2 ,v_num3,v_num4,1,1,v_num1,v_num2 ,v_num3,v_num4,1,1;
end loop;
END pro_test2;
运行过程中awr的情况:
Begin Snap: 7888 03-6月 -11 14:13:42 505 1.0
End Snap: 7889 03-6月 -11 14:14:33 499 1.1
Elapsed: 0.85 (mins)
DB Time: 2.34 (mins)
SQL ordered by CPU Time
CPU Time (s) Elapsed Time (s) Executions CPU per Exec (s) % Total % Total DB Time SQL Id SQL Module SQL Text
17 17 30,000 0.00 20.26 12.01 05xdg6pgfyfw0
SQL*Plus select count(*) from ( select ...
6 6 10,000 0.00 7.52 4.45 132hj2q7kx0uj
SQL*Plus select count(*) from ( select ...
6 6 10,000 0.00 6.73 3.99 3zm4ksgsdy4z1
SQL*Plus select count(*) from ( select ...
6 6 10,000 0.00 6.70 3.97 dgkg15t97fncr
SQL*Plus select count(*) from ( select ...
SQL ordered by Gets
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
1,620,000 30,000 54.00 22.54 17.25 16.88 05xdg6pgfyfw0
SQL*Plus select count(*) from ( select ...
540,000 10,000 54.00 7.51 6.41 6.25 132hj2q7kx0uj
SQL*Plus select count(*) from ( select ...
540,000 10,000 54.00 7.51 5.73 5.62 3zm4ksgsdy4z1
SQL*Plus select count(*) from ( select ...
540,000 10,000 54.00 7.51 5.71 5.59 dgkg15t97fncr
SQL*Plus select count(*) from ( select ...
内存占用情况:
SQL_ID SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM
05xdg6pgfyfw0 54639 15320 13616
132hj2q7kx0uj 47943 15160 12848
3zm4ksgsdy4z1 45315 14360 13040
dgkg15t97fncr 50391 14680 13232
结果:
单独从语句来看,这两个没有大的差别,分别模拟了变长2-4个参数
定长6个参数,变长累加和定长的消耗资源差不多,定长的sql变成了一个。
我担心如果参数变多,sql的版本(sql的执行计划)可能会增多,代价相应会增大。
所以我建议还是请QA同事压力一下。
不知各位对sql语句的共享有什么好的建议。
SQL> select sum(SHARABLE_MEM)/power(1024,3) from v$sql where substr(sql_text, 1, 40) = 'select id as ppppid, p2m.mllch';
SUM(SHARABLE_MEM)/POWER(1024,3)
-------------------------------
4.45558127
想法: 目前因为in是不定长,我们可以用重复的数据填充将其变为定长。即原来是 例如 id in (1,2,3),我们将其定长到20位, id in (1,2,3…………..3),后面补上17个3,这样少于20个的,都变为20个(或者50、70、80.。)。
以下是我对上面想法的测试(下面只是框架,相关的表名已去掉):
CREATE OR REPLACE PROCEDURE pro_test2 IS
v_str_sql varchar2(1000);
v_num1 number(18);
v_num2 number(18);
v_num3 number(18);
v_num4 number(18);
v_num5 number(18);
BEGIN
--两个参数
v_str_sql :='select count(*) from ( select Id .... in (:1, :2)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2) ) or ( CId in (:1, :2)) or (pCId in (:1, :2) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2,v_num1,v_num2,v_num1,v_num2,v_num1,v_num2 ;
end loop;
--三个参数
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3) ) or ( CId in (:1, :2,:3)) or (pCId in (:1, :2,:3) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
v_num3 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2 ,v_num3,v_num1,v_num2 ,v_num3,v_num1,v_num2 ,v_num3,v_num1,v_num2 ,v_num3;
end loop;
--四个参数
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3,:4)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3,:4) ) or ( CId in (:1, :2,:3,:4)) or (pCId in (:1, :2,:3,:4) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
v_num3 := trunc(dbms_random.value(10,999999)) ;
v_num4 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2 ,v_num3,v_num4,v_num1,v_num2 ,v_num3,v_num4,v_num1,v_num2 ,v_num3,v_num4,v_num1,v_num2 ,v_num3,v_num4;
end loop;
-------------定长
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3,:4,:5,:6)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3,:4,:5,:6) ) or ( CId in (:1, :2,:3,:4,:5,:6)) or (pCId in (:1, :2,:3,:4,:5,:6) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2,1,1,1,1,v_num1,v_num2,1,1,1,1,v_num1,v_num2,1,1,1,1,v_num1,v_num2,1,1,1,1;
end loop;
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3,:4,:5,:6)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3,:4,:5,:6) ) or ( CId in (:1, :2,:3,:4,:5,:6)) or (pCId in (:1, :2,:3,:4,:5,:6) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
v_num3 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2 ,v_num3,1,1,1,v_num1,v_num2 ,v_num3,1,1,1,v_num1,v_num2 ,v_num3,1,1,1,v_num1,v_num2 ,v_num3,1,1,1;
end loop;
v_str_sql :='select count(*) from ( select Id .... in (:1, :2,:3,:4,:5,:6)) or ';
v_str_sql :=v_str_sql || '(t_Id in (:1, :2,:3,:4,:5,:6) ) or ( CId in (:1, :2,:3,:4,:5,:6)) or (pCId in (:1, :2,:3,:4,:5,:6) ) ))))';
v_str_sql :=v_str_sql || ' ) ';
for i in 1..10000 loop
v_num1 := trunc(dbms_random.value(10,999999)) ;
v_num2 := trunc(dbms_random.value(10,999999)) ;
v_num3 := trunc(dbms_random.value(10,999999)) ;
v_num4 := trunc(dbms_random.value(10,999999)) ;
execute immediate v_str_sql into v_num5 using v_num1,v_num2 ,v_num3,v_num4,1,1,v_num1,v_num2 ,v_num3,v_num4,1,1,v_num1,v_num2 ,v_num3,v_num4,1,1,v_num1,v_num2 ,v_num3,v_num4,1,1;
end loop;
END pro_test2;
运行过程中awr的情况:
Begin Snap: 7888 03-6月 -11 14:13:42 505 1.0
End Snap: 7889 03-6月 -11 14:14:33 499 1.1
Elapsed: 0.85 (mins)
DB Time: 2.34 (mins)
SQL ordered by CPU Time
CPU Time (s) Elapsed Time (s) Executions CPU per Exec (s) % Total % Total DB Time SQL Id SQL Module SQL Text
17 17 30,000 0.00 20.26 12.01 05xdg6pgfyfw0
SQL*Plus select count(*) from ( select ...
6 6 10,000 0.00 7.52 4.45 132hj2q7kx0uj
SQL*Plus select count(*) from ( select ...
6 6 10,000 0.00 6.73 3.99 3zm4ksgsdy4z1
SQL*Plus select count(*) from ( select ...
6 6 10,000 0.00 6.70 3.97 dgkg15t97fncr
SQL*Plus select count(*) from ( select ...
SQL ordered by Gets
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
1,620,000 30,000 54.00 22.54 17.25 16.88 05xdg6pgfyfw0
SQL*Plus select count(*) from ( select ...
540,000 10,000 54.00 7.51 6.41 6.25 132hj2q7kx0uj
SQL*Plus select count(*) from ( select ...
540,000 10,000 54.00 7.51 5.73 5.62 3zm4ksgsdy4z1
SQL*Plus select count(*) from ( select ...
540,000 10,000 54.00 7.51 5.71 5.59 dgkg15t97fncr
SQL*Plus select count(*) from ( select ...
内存占用情况:
SQL_ID SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM
05xdg6pgfyfw0 54639 15320 13616
132hj2q7kx0uj 47943 15160 12848
3zm4ksgsdy4z1 45315 14360 13040
dgkg15t97fncr 50391 14680 13232
结果:
单独从语句来看,这两个没有大的差别,分别模拟了变长2-4个参数
定长6个参数,变长累加和定长的消耗资源差不多,定长的sql变成了一个。
我担心如果参数变多,sql的版本(sql的执行计划)可能会增多,代价相应会增大。
所以我建议还是请QA同事压力一下。
不知各位对sql语句的共享有什么好的建议。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/758322/viewspace-697169/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/758322/viewspace-697169/