oracle存储过程比直接执行慢,SQL语句直接执行和在存储过程中速度的有关问题....

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

同一个SQL语句,放在存储过程中执行比直接执行差了100倍以上的速度,很不解啊...这个有什么具体的方法来优化么?

数据库版本是9i......貌似在10g上就没这么大差别..但是由于2个库的数据量是不一样的,所以没什么参考价值

SQL语句:

SQL codeSQL_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 ';

执行方式(匿名块和直接执行都是一样):

SQL codeDECLARE

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;