SQL语句直接执行和在存储过程中速度的问题......

Question

同一个SQL语句,放在存储过程中执行比直接执行差了100倍以上的速度,很不解啊...这个有什么具体的方法来优化么?
数据库版本是9i......貌似在10g上就没这么大差别..但是由于2个库的数据量是不一样的,所以没什么参考价值SQL语句:  SQL_INSERT_RL_INFO_AREA CONSTANT varchar2(1000) := 'insert into temp_addr_new ( ROW_NUMBER, GEOGRAPHY_ID, COMMUNITY_ID, DESCRIPTION, NAME, COMMUNITY_NAME, AREA_ID, BUSINESS_TYPE_ID, '
                                                  || 'USER_TYPE_ID, MANAGED_CODE ) '
                                                  || 'select * from (select rownum r_id, rl.geography_loc_id , c.community_id , rl.description , rl.name rname, c.name cname,  rl.area_id , rl.business_type_id , '
                                                  || 'rl.user_type_id , rl.manage_code '
                                                  || 'from regional_loc rl,regional_loc_2_community rl2c,community c '
                                                  || 'where rl.geography_loc_id = rl2c.geography_loc_id and rl2c.community_id = c.community_id and rl.display_type_cd = 1 '
                                                  || 'and c.status = :1 '
                                                  || 'and rl.description like :2 and c.name like :3 '
                                                  || 'and rl.regional_simple_spell like :4 and c.community_simple_spell like :5 '
                                                  || 'and rl.user_type_id in ( :6 ,3) '
                                                  || 'and c.rsc_spec_id <> :7 '
                                                  || 'and rl.area_id = :8 and rownum <= :9 ) where r_id >= :10 ';  SQL_SELECT_RL_COUNT_AREA CONSTANT varchar2(1000) := 'select count(1) from regional_loc rl,regional_loc_2_community rl2c,community c '
                                                  || 'where rl.geography_loc_id = rl2c.geography_loc_id and rl2c.community_id = c.community_id and rl.display_type_cd = 1 '
                                                  || 'and c.status = :1 '
                                                  || 'and rl.description like :2 and c.name like :3 '
                                                  || 'and rl.regional_simple_spell like :4 and c.community_simple_spell like :5 '
                                                  || 'and rl.user_type_id in ( :6 ,3) '
                                                  || 'and c.rsc_spec_id <> :7 '
                                                  || 'and rl.area_id = :8 ';执行方式(匿名块和直接执行都是一样):DECLARE
  IN_COMMUNITY_NAME VARCHAR2(200);
  IN_STANDARD_ADDR_NAME VARCHAR2(200);
  IN_PROD_SPEC_ID NUMBER;
  IN_BUSINESS_TYPE VARCHAR2(200);
  IN_USER_TYPE NUMBER;
  IN_AREA_ID NUMBER;
  IN_CONDITION_TYPE VARCHAR2(200);
  IN_BEGIN_ROW NUMBER;
  IN_END_ROW NUMBER;
  OUT_ATTRS PG_RMS_FOR_CRM_NEW.c_list;
  OUT_ROW_COUNT NUMBER;
  OUT_RET_CODE VARCHAR2(200);
  OUT_RET_INFO VARCHAR2(200);
  
  v_gpon_not_rsc_spec number;
  v_start number;
  v_rl_chinese varchar2(400);
  v_c_chinese varchar2(400);
  v_rl_simple_spell varchar2(400);
  v_c_simple_spell varchar2(400);
  v_community_state number;
BEGIN
  v_start := dbms_utility.get_time;
  IN_COMMUNITY_NAME := '永翔佳苑';
  IN_STANDARD_ADDR_NAME := '';
  IN_PROD_SPEC_ID := '';
  IN_BUSINESS_TYPE := 'Broadband';
  IN_USER_TYPE := 1;
  IN_AREA_ID := 24;
  IN_CONDITION_TYPE := 0;
  IN_BEGIN_ROW := 1;
  IN_END_ROW := 10;
  v_gpon_not_rsc_spec := 10102008;
  
  v_rl_chinese := '%' || IN_STANDARD_ADDR_NAME || '%';
  v_c_chinese := '%' || IN_COMMUNITY_NAME || '%';
  v_rl_simple_spell := '%%';
  v_c_simple_spell := '%%';
  v_community_state := 4;  EXECUTE IMMEDIATE PG_RMS_FOR_CRM_NEW.SQL_SELECT_RL_COUNT_AREA
    INTO OUT_ROW_COUNT
    USING v_community_state,v_rl_chinese,v_c_chinese,v_rl_simple_spell,v_c_simple_spell,in_user_type,v_gpon_not_rsc_spec,IN_AREA_ID;
    
dbms_output.put_line('性能测试1 : ' || (dbms_utility.get_time - v_start) * 10 || ' ms');
v_start := dbms_utility.get_time;         
          
  EXECUTE IMMEDIATE PG_RMS_FOR_CRM_NEW.SQL_INSERT_RL_INFO_AREA
    USING v_community_state,v_rl_chinese,v_c_chinese,v_rl_simple_spell,v_c_simple_spell,in_user_type,v_gpon_not_rsc_spec,IN_AREA_ID,IN_END_ROW,IN_BEGIN_ROW;
    
dbms_output.put_line('性能测试2 : ' || (dbms_utility.get_time - v_start) * 10 || ' ms');
v_start := dbms_utility.get_time;  
  -- Modify the code to output the variable
  -- DBMS_OUTPUT.PUT_LINE('OUT_ATTRS = ' || OUT_ATTRS);
  DBMS_OUTPUT.PUT_LINE('OUT_ROW_COUNT = ' || OUT_ROW_COUNT);
  DBMS_OUTPUT.PUT_LINE('OUT_RET_CODE = ' || OUT_RET_CODE);
  DBMS_OUTPUT.PUT_LINE('OUT_RET_INFO = ' || OUT_RET_INFO);
END;这种执行的速度是:性能测试1 : 80 ms
性能测试2 : 30 ms
OUT_ROW_COUNT = 68
OUT_RET_CODE = 然后用在存储过程中,存储过程里面的SQL调用方式一模一样,速度是:DECLARE
  IN_COMMUNITY_NAME VARCHAR2(200);
  IN_STANDARD_ADDR_NAME VARCHAR2(200);
  IN_PROD_SPEC_ID NUMBER;
  IN_BUSINESS_TYPE VARCHAR2(200);
  IN_USER_TYPE NUMBER;
  IN_AREA_ID NUMBER;
  IN_CONDITION_TYPE VARCHAR2(200);
  IN_BEGIN_ROW NUMBER;
  IN_END_ROW NUMBER;
  OUT_ATTRS RMLK.PG_RMS_FOR_CRM_NEW.c_list;
  OUT_ROW_COUNT NUMBER;
  OUT_RET_CODE VARCHAR2(200);
  OUT_RET_INFO VARCHAR2(200);
BEGIN
  IN_COMMUNITY_NAME := '永翔佳苑';
  IN_STANDARD_ADDR_NAME := '';
  IN_PROD_SPEC_ID := '';
  IN_BUSINESS_TYPE := 'Broadband';
  IN_USER_TYPE := 1;
  IN_AREA_ID := 24;
  IN_CONDITION_TYPE := 0;
  IN_BEGIN_ROW := 1;
  IN_END_ROW := 10;  PG_RMS_FOR_CRM_NEW.SELECT_ADDR_INFO(
    IN_COMMUNITY_NAME => IN_COMMUNITY_NAME,
    IN_STANDARD_ADDR_NAME => IN_STANDARD_ADDR_NAME,
    IN_PROD_SPEC_ID => IN_PROD_SPEC_ID,
    IN_BUSINESS_TYPE => IN_BUSINESS_TYPE,
    IN_USER_TYPE => IN_USER_TYPE,
    IN_AREA_ID => IN_AREA_ID,
    IN_CONDITION_TYPE => IN_CONDITION_TYPE,
    IN_BEGIN_ROW => IN_BEGIN_ROW,
    IN_END_ROW => IN_END_ROW,
    OUT_ATTRS => OUT_ATTRS,
    OUT_ROW_COUNT => OUT_ROW_COUNT,
    OUT_RET_CODE => OUT_RET_CODE,
    OUT_RET_INFO => OUT_RET_INFO
  );
  -- Modify the code to output the variable
  -- DBMS_OUTPUT.PUT_LINE('OUT_ATTRS = ' || OUT_ATTRS);
  DBMS_OUTPUT.PUT_LINE('OUT_ROW_COUNT = ' || OUT_ROW_COUNT);
  DBMS_OUTPUT.PUT_LINE('OUT_RET_CODE = ' || OUT_RET_CODE);
END;性能测试1 : 12170 ms
性能测试2 : 10280 ms
OUT_ROW_COUNT = 68
OUT_RET_CODE = 

Answer 1

你那个语句哪里需要动态？要想快直接写，别用动态SQL。

Answer 2

一个是动态SQL，一个写死的SQL，当然不一样了。

Answer 3

 PG_RMS_FOR_CRM_NEW.SELECT_ADDR_INFO(
    IN_COMMUNITY_NAME => IN_COMMUNITY_NAME,
    IN_STANDARD_ADDR_NAME => IN_STANDARD_ADDR_NAME,
    IN_PROD_SPEC_ID => IN_PROD_SPEC_ID,
    IN_BUSINESS_TYPE => IN_BUSINESS_TYPE,
    IN_USER_TYPE => IN_USER_TYPE,
    IN_AREA_ID => IN_AREA_ID,
    IN_CONDITION_TYPE => IN_CONDITION_TYPE,
    IN_BEGIN_ROW => IN_BEGIN_ROW,
    IN_END_ROW => IN_END_ROW,
    OUT_ATTRS => OUT_ATTRS,
    OUT_ROW_COUNT => OUT_ROW_COUNT,
    OUT_RET_CODE => OUT_RET_CODE,
    OUT_RET_INFO => OUT_RET_INFO
  );--你这个的执行方式和上面直接sql的执行不同吧？他们花费的时间肯定不一样

Answer 4

对的...这个包的包体里面的内容和上面匿名块是一样的...我就是封装成程序包而已...
这样会造成100多倍的速度影响么?还有我如在程序包里直接写SQL语句...不是用这种EXECUTE IMMEDIATE 的方式来执行的话就是相当快的速度...参数也是外部传进去的...

Answer 5

直接执行，那肯定要快啊，动态执行本来就会慢，这个是不争的事实啊，因为人家实现了动态sql啊，你的允许人家慢一点啊