ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于sql语句的游标共享问题

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

原创 Linux操作系统 作者:aaqwsh 时间:2011-06-04 11:27:15 0 删除 编辑
我们库里有个语句占用了大量的内存,达到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语句的共享有什么好的建议。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/758322/viewspace-697169/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-24

  • 博文量
    132
  • 访问量
    262201