关于sql语句的游标共享问题

我们库里有个语句占用了大量的内存，达到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语句的共享有什么好的建议。