一个存储过程，百思不解啊

Question

create or replace procedure test(lrqqq in varchar2, lrqqz in varchar2,  l_gjjgdm in varchar2 )
as
beginINSERT INTO sb_jks
SELECT to_number(zj.jk_xh || zj.jklsmxh_xh_char) xtspxh,
             to_number(yzmx.pz_xh || yzmx.yzmx_xh_char) zsxh,
             b.swdjzh nsrdzdah,
             null,
             yzmx.sfssq_qsrq sssq_q,
             yzmx.sfssq_zzrq sssq_z,
             yzmx.zsxm_dm,
             yzmx.zspm_dm,
             yzmx.sksx1_dm skzl_dm,
             yzmx.sksx2_dm sksx_dm,
             yzpz.sbfs_dm sbfs_dm,
             yzmx.pz_xh yzpzxh,
             yzmx.yzfs_rq yzfsrq,
             0 kssl,
             yzmx.js_yj xssr,
             yzmx.sl,
             0 dwse,
             yzmx.yzsf_je sjse,
             ' ' spzl_dm,
             pz.kpry_dm kpr_dm,
             pz.tf_sj kprq,
             '0' xtsphm,
             pz.pzhm yssphm,
             b.zclx_dm djzclx_dm,
             b.hy_dm,
             ' ' yskm_dm,
             ' ' ysfpbl_dm,
             '' ZSFS_DM,
             zj.skzhid SKGK_DM,
             '' YHZL_DM,
             '' YH_DM,
             '' YHZH,
             to_date('1900-01-01','yyyy-mm-dd') spjkqx,
             ' ' hzjksbz,
             '' BZ1,
             '' BZ2,
             zj.jkfs_dm,
             '' YSJYH,
             '' YSCZZT_DM,
             '' YSBL_YJFSRQ,
             '' YSBL_FSRQ,
             '' BLCGRQ,
             '' ZZZYWSZ_DYRQ,
             '' ZZZYWSZ_DYR_DM,
             '' RKRQ,
             yzmx.tzlx_dm,
             '' TZRQ,
             '' ZFR_DM,
             pz.zf_rq zfrq,
             '' SPZFLX_DM,
             '' JDXZ_DM,
             '' ZGSWRY_DM,
             ' ' sk_ssswjg_dm,
             ' ' zsjg_dm,
             b.gljg_dm nsr_swjg_dm,
             g.swjg_dm swjg_dm,
             yzmx.lrry_dm lrr_dm,
             yzmx.lr_sj lrrq,
             yzmx.xgry_dm xgr_dm,
             yzmx.xg_sj xgrq
        FROM (SELECT a.pz_xh,
                     a.yzmx_xh,
                     CASE
                       WHEN a.yzmx_xh < 10 THEN
                        '0' || a.yzmx_xh
                       ELSE
                        '' || a.yzmx_xh
                     END AS yzmx_xh_char,
                     a.nsrnbm,
                     a.sfssq_qsrq,
                     a.sfssq_zzrq,
                     a.zsxm_dm,
                     a.zspm_dm,
                     a.sksx1_dm,
                     a.sksx2_dm,
                     a.dzsx_dm,
                     a.tzlx_dm,
                     a.js_yj,
                     a.sl,
                     a.yzsf_je,
                     a.yzfs_rq,
                     a.jk_qx,
                     a.zsfs_dm,
                     a.zsjg_dm,
                     a.hsjg_dm,
                     a.lr_sj,
                     a.lrry_dm,
                     a.xg_sj,
                     a.xgry_dm
                FROM t_zs_yzmx@dblink_ctais2 a
               WHERE a.lr_sj  >= to_date(lrqqq,'yyyy-mm-dd')
                 AND  a.lr_sj < to_date(lrqqz,'yyyy-mm-dd')
                 AND a.gljg_dm = l_gjjgdm) yzmx,
             (SELECT f.pz_xh, f.pzzl_dm, f.sbfs_dm
                FROM t_sb_yzpz@dblink_ctais2 f
               WHERE f.lr_sj  >= to_date(lrqqq,'yyyy-mm-dd')
                 AND  f.lr_sj  < to_date(lrqqz,'yyyy-mm-dd')
                 AND f.gljg_dm = l_gjjgdm) yzpz,
             (SELECT e.dzsph_xh,
                     e.pzzl_dm,
                     e.tf_sj,
                     e.pzhm,
                     e.zf_rq,
                     e.kpry_dm
                FROM t_zs_pzsyqk@dblink_ctais2 e
               WHERE  e.lr_sj  >= to_date(lrqqq,'yyyy-mm-dd')
                 AND  e.lr_sj  < to_date(lrqqz,'yyyy-mm-dd')
                 AND  e.zf_rq is null
                 and  e.zfr_dm is null) pz,
             (SELECT c.pz_xh,
                     c.yzmx_xh,
                     c.jk_xh,
                     c.jklsmxh_xh,
                     CASE
                       WHEN c.jklsmxh_xh < 10 THEN
                        '0' || c.jklsmxh_xh
                       ELSE
                        '' || c.jklsmxh_xh
                     END AS jklsmxh_xh_char,
                     c.kj_rq,
                     '' hzjksh,
                     c.rk_rq,
                     c.xgry_dm,
                     c.rk_rq,
                     c.jkfs_dm,
                     c.yskm_dm,
                     c.ysfpbl_dm,
                     c.skzhid
                FROM t_zs_zjjkmx@dblink_ctais2 c
               WHERE c.lr_sj >= to_date(lrqqq,'yyyy-mm-dd')
                 AND c.lr_sj  < to_date(lrqqz,'yyyy-mm-dd')) zj,
             t_dj_jgnsr@dblink_ctais2 b,
             t_dm_gy_swry@dblink_ctais2 g,
             t_zs_pzmx@dblink_ctais2 d
       WHERE yzmx.pz_xh = yzpz.pz_xh
         AND yzmx.pz_xh = zj.pz_xh(+)
         AND yzmx.yzmx_xh = zj.yzmx_xh(+)
         AND zj.jk_xh = d.jk_xh
         AND zj.jklsmxh_xh = d.jklsmxh_xh
         AND d.dzsph_xh = pz.dzsph_xh
         AND yzmx.nsrnbm = b.nsrnbm
         AND b.zgy = g.swry_dm;
  end ;
各位大侠，这是一个简单的存储过程，目的是从另一个库里，按需要抽取一些数据，如果单独执行插入操作是可成功插入的，但放到存储过程里，这个插入操作就会死掉，可执行的例子如下： 

Answer 1

INSERT INTO sb_jks
      SELECT to_number(zj.jk_xh || zj.jklsmxh_xh_char) xtspxh,
             to_number(yzmx.pz_xh || yzmx.yzmx_xh_char) zsxh,
             b.swdjzh nsrdzdah,
             null,
             yzmx.sfssq_qsrq sssq_q,
             yzmx.sfssq_zzrq sssq_z,
             yzmx.zsxm_dm,
             yzmx.zspm_dm,
             yzmx.sksx1_dm skzl_dm,
             yzmx.sksx2_dm sksx_dm,
             yzpz.sbfs_dm sbfs_dm,
             yzmx.pz_xh yzpzxh,
             yzmx.yzfs_rq yzfsrq,
             0 kssl,
             yzmx.js_yj xssr,
             yzmx.sl,
             0 dwse,
             yzmx.yzsf_je sjse,
             ' ' spzl_dm,
             pz.kpry_dm kpr_dm,
             pz.tf_sj kprq,
             '0' xtsphm,
             pz.pzhm yssphm,
             b.zclx_dm djzclx_dm,
             b.hy_dm,
             ' ' yskm_dm,
             ' ' ysfpbl_dm,
             '' ZSFS_DM,
             zj.skzhid SKGK_DM,  
             '' YHZL_DM,
             '' YH_DM,
             '' YHZH,
             to_date('1900-01-01','yyyy-mm-dd') spjkqx,
             ' ' hzjksbz,
             '' BZ1, 
             '' BZ2,
             zj.jkfs_dm,
             '' YSJYH,
             '' YSCZZT_DM,
             '' YSBL_YJFSRQ,
             '' YSBL_FSRQ,
             '' BLCGRQ,
             '' ZZZYWSZ_DYRQ,
             '' ZZZYWSZ_DYR_DM,
             '' RKRQ,
             yzmx.tzlx_dm,
             '' TZRQ,
             '' ZFR_DM,
             pz.zf_rq zfrq,
             '' SPZFLX_DM,
             '' JDXZ_DM,
             '' ZGSWRY_DM,
             ' ' sk_ssswjg_dm,
             ' ' zsjg_dm,
             b.gljg_dm nsr_swjg_dm,
             g.swjg_dm swjg_dm,
             yzmx.lrry_dm lrr_dm,
             yzmx.lr_sj lrrq,
             yzmx.xgry_dm xgr_dm,
             yzmx.xg_sj xgrq
        FROM (SELECT a.pz_xh,
                     a.yzmx_xh,
                     CASE
                       WHEN a.yzmx_xh < 10 THEN
                        '0' || a.yzmx_xh
                       ELSE
                        '' || a.yzmx_xh
                     END AS yzmx_xh_char,
                     a.nsrnbm,
                     a.sfssq_qsrq,
                     a.sfssq_zzrq,
                     a.zsxm_dm,
                     a.zspm_dm,
                     a.sksx1_dm,
                     a.sksx2_dm,
                     a.dzsx_dm,
                     a.tzlx_dm,
                     a.js_yj,
                     a.sl,
                     a.yzsf_je,
                     a.yzfs_rq,
                     a.jk_qx,
                     a.zsfs_dm,
                     a.zsjg_dm,
                     a.hsjg_dm,
                     a.lr_sj,
                     a.lrry_dm,
                     a.xg_sj,
                     a.xgry_dm
                FROM t_zs_yzmx@dblink_ctais2 a
               WHERE a.lr_sj >= to_date('2008-04-02','yyyy-mm-dd')     
                 AND  a.lr_sj < to_date('2008-04-03','yyyy-mm-dd')      
                 AND a.gljg_dm = '15490000300') yzmx,
             (SELECT f.pz_xh, f.pzzl_dm, f.sbfs_dm
                FROM t_sb_yzpz@dblink_ctais2 f
               WHERE f.lr_sj  >= to_date('2008-04-02','yyyy-mm-dd') 
                 AND  f.lr_sj  < to_date('2008-04-03','yyyy-mm-dd')
                 AND f.gljg_dm = '15490000300') yzpz,
             (SELECT e.dzsph_xh,
                     e.pzzl_dm,
                     e.tf_sj,
                     e.pzhm,
                     e.zf_rq,
                     e.kpry_dm
                FROM t_zs_pzsyqk@dblink_ctais2 e
               WHERE  e.lr_sj  >= to_date('2008-04-02','yyyy-mm-dd') 
                 AND  e.lr_sj  < to_date('2008-04-03','yyyy-mm-dd')
                 AND  e.zf_rq is null
                 and e.zfr_dm is null) pz,
             (SELECT c.pz_xh,
                     c.yzmx_xh,
                     c.jk_xh,
                     c.jklsmxh_xh,
                     CASE
                       WHEN c.jklsmxh_xh < 10 THEN
                        '0' || c.jklsmxh_xh
                       ELSE
                        '' || c.jklsmxh_xh
                     END AS jklsmxh_xh_char,
                     c.kj_rq,
                     '' hzjksh,
                     c.rk_rq,
                     c.xgry_dm,
                     c.rk_rq,
                     c.jkfs_dm,
                     c.yskm_dm,
                     c.ysfpbl_dm,
                     c.skzhid
                FROM t_zs_zjjkmx@dblink_ctais2 c
               WHERE c.lr_sj >= to_date('2008-04-02','yyyy-mm-dd') 
                 AND c.lr_sj  < to_date('2008-04-03','yyyy-mm-dd')) zj,
             t_dj_jgnsr@dblink_ctais2 b,
             t_dm_gy_swry@dblink_ctais2 g,
             t_zs_pzmx@dblink_ctais2 d
       WHERE yzmx.pz_xh = yzpz.pz_xh
         AND yzmx.pz_xh = zj.pz_xh(+)
         AND yzmx.yzmx_xh = zj.yzmx_xh(+)
         AND zj.jk_xh = d.jk_xh
         AND zj.jklsmxh_xh = d.jklsmxh_xh
         AND d.dzsph_xh = pz.dzsph_xh
         AND yzmx.nsrnbm = b.nsrnbm
         AND b.zgy = g.swry_dm;另外，我觉得这个查询操作，效率也是慢的可以了，希望高手给看看。

Answer 2

这种问题我也遇到过，当时没有解决，今天又看到类似的问题，查了下资料。可能的原因是，两个sql走的执行计划是不一样的，你将两个查询语句加上hint提示，都选择cbo或者rbo的优化器试试是不是一样的效率。