存储过程不能执行，但里面语句单独执行没问题-CSDN论坛

⋅分享贴，关于上次提问的有关DBLINK的问题 ⋅有关DBLINK的问题，是否是BUG? ⋅存储过程不能执行，但里面语句单独执行没问题 更多帖子 关注空间博客 hebo2005 本版专家分：27611 红花 2008年7月 Oracle大版内专家分月排行榜第一 2008年6月 Oracle大版内专家分月排行榜第一 2008年5月 Oracle大版内专家分月排行榜第一 黄花 2008年9月 Oracle大版内专家分月排行榜第二 2008年8月 Oracle大版内专家分月排行榜第二 2008年4月 Oracle大版内专家分月排行榜第二 结帖率 100%





CREATE OR REPLACE PROCEDURE CHINA.sp_make_ap030_temp (

   in_start_day   IN   DATE,                                         -- 起始日

   in_end_day     IN   DATE,                                          --结束日

   in_so_id       IN   NUMBER,             

   in_etr_id      IN   VARCHAR2                                     --生成者ID

)

IS

BEGIN

   

   MERGE INTO tb_ap030 p

      USING ....

 MERGE INTO ...;

   COMMIT;

EXCEPTION

   WHEN NO_DATA_FOUND

   THEN

      NULL;

   WHEN OTHERS

   THEN

      -- Consider logging the error and then re-raise

      RAISE;

END sp_make_ap030_temp;

/

最近遇到一个怪问题，以上这个存储过程，每天会有个JOB调用执行，一直正常，运行了有一年多了
从8号开始突然执行有问题，原来正常执行20秒就能结束的，结果几个小时都没执行完，造成数据库狂慢
里面就两个MERGE语句
将这两个语句直接执行，没有问题，20秒左右就执行完了，但直接用存储过程就是不行
尝试过重启数据库，存储过程重编译，重命名，目标表重建,表索引去除等方法，还是情况依旧
单独执行SQL正常，一用存储过程就出问题，百思不得其解

唯一有点线索的，就是管理曾经执行过很长时间，最终报oralce 06512 04021 这两个错误

0 2010-06-17 09:30:45

回复数 16 只看楼主引用举报楼主

⋅第一顆星星 更多帖子 关注空间博客 ngx20080110 本版专家分：10531: 不懂，帮顶

0 2010-06-17 09:33:53

只看TA 引用举报 #1 得分 0

wanglipo 本版专家分：188: 关注帮顶。。。。。。。。。。。。。。。。

0 2010-06-17 09:43:36

只看TA 引用举报 #2 得分 0

⋅sql server2008 联机丛书疑问 ⋅ASE抽取数据到IQ ⋅ASE数据抽取到IQ方案 更多帖子 关注空间博客 iqlife 本版专家分：46857 红花 2010年7月 Oracle大版内专家分月排行榜第一 2010年6月 Oracle大版内专家分月排行榜第一 黄花 2013年4月 Oracle大版内专家分月排行榜第二 2013年3月 Oracle大版内专家分月排行榜第二 2010年5月 Oracle大版内专家分月排行榜第二 蓝花 2013年1月 Oracle大版内专家分月排行榜第三 2011年12月 Oracle大版内专家分月排行榜第三 2010年8月 Oracle大版内专家分月排行榜第三 2010年4月 Oracle大版内专家分月排行榜第三 2010年3月 Oracle大版内专家分月排行榜第三: 什么时候开始这样的?最近做过什么变动??
应用层,后台...
好像是数据超过长度造成的?看下插入的数据是否有异常...

0 2010-06-17 10:00:42

只看TA 引用举报 #3 得分 20

⋅分享贴，关于上次提问的有关DBLINK的问题 ⋅有关DBLINK的问题，是否是BUG? ⋅存储过程不能执行，但里面语句单独执行没问题 更多帖子 关注空间博客 hebo2005 本版专家分：27611 红花 2008年7月 Oracle大版内专家分月排行榜第一 2008年6月 Oracle大版内专家分月排行榜第一 2008年5月 Oracle大版内专家分月排行榜第一 黄花 2008年9月 Oracle大版内专家分月排行榜第二 2008年8月 Oracle大版内专家分月排行榜第二 2008年4月 Oracle大版内专家分月排行榜第二: 最近没做过变动，从8号开始，突然JOB不能执行
存储过程里的就两个MERGE，单独拿出来执行都正常
用存储过程就是不行
由于此存储过程正好也要更新，所以索性更新成新的，两个MERGE我并成了一句，结果也是同样的情况，单独执行没问题，存储过程一执行，负载就上去，一直执行不出来

单独执行的语句，就是修改了四个参数
in_start_day   - trunc(sysdate)-4
   in_end_day     -sysdate
   in_so_id       -0
   in_etr_id     -'SYSTEM'

0 2010-06-17 10:24:25

只看TA 引用举报 #4    得分 0

⋅sql server2008 联机丛书疑问 ⋅ASE抽取数据到IQ ⋅ASE数据抽取到IQ方案 更多帖子 关注空间博客 iqlife 本版专家分：46857 红花 2010年7月 Oracle大版内专家分月排行榜第一 2010年6月 Oracle大版内专家分月排行榜第一 黄花 2013年4月 Oracle大版内专家分月排行榜第二 2013年3月 Oracle大版内专家分月排行榜第二 2010年5月 Oracle大版内专家分月排行榜第二 蓝花 2013年1月 Oracle大版内专家分月排行榜第三 2011年12月 Oracle大版内专家分月排行榜第三 2010年8月 Oracle大版内专家分月排行榜第三 2010年4月 Oracle大版内专家分月排行榜第三 2010年3月 Oracle大版内专家分月排行榜第三: WHAT里的内容可以的话用MERGE 代码替换了试试...
看问题是出在存储过程还是本身JOB

0 2010-06-17 10:35:51

只看TA 引用举报 #5 得分 0

⋅分享贴，关于上次提问的有关DBLINK的问题 ⋅有关DBLINK的问题，是否是BUG? ⋅存储过程不能执行，但里面语句单独执行没问题 更多帖子 关注空间博客 hebo2005 本版专家分：27611 红花 2008年7月 Oracle大版内专家分月排行榜第一 2008年6月 Oracle大版内专家分月排行榜第一 2008年5月 Oracle大版内专家分月排行榜第一 黄花 2008年9月 Oracle大版内专家分月排行榜第二 2008年8月 Oracle大版内专家分月排行榜第二 2008年4月 Oracle大版内专家分月排行榜第二: [code=SQL]
CREATE OR REPLACE PROCEDURE CHINA.sp_make_ap030_temp (
   in_start_day   IN   DATE,   -- 起始日
   in_end_day     IN   DATE,                                          --结束日
   in_so_id       IN   NUMBER,             --电视台,0为全部，单独计算写入SO_ID
   in_etr_id      IN   VARCHAR2                                     --生成者ID
)
IS
BEGIN
   MERGE INTO tb_ap030 p
      USING (SELECT a.good_id, a.so_id, a.form_fr_date, a.form_end_date,
                    a.form_mis, NVL (b.qty, 0) qty, NVL (b.amt, 0) amt,
                    ROUND (NVL (b.amt, 0) - NVL (b.qty, 0) * c.tax_cost,
                           2
                          ) profit
               FROM tb_ag001 c,
                    (SELECT DISTINCT b.good_id, a.so_id, a.form_fr_date,
                                     a.form_end_date, a.form_mis
                                FROM tb_ab010 a, tb_ab060 b
                               WHERE a.title_id = b.title_id
                                 AND a.form_end_date IS NOT NULL
                                 /*AND a.title_cd <> '40'*/
                                 and a.so_id<>'1000023'
                                 AND a.so_id =
                                        DECODE (in_so_id,
                                                0, a.so_id,
                                                in_so_id
                                               )
                                 AND a.form_fr_date BETWEEN in_start_day
                                                        AND in_end_day
                                                            - 0 / 12) a,
                    (SELECT   b.good_id, b.so_id, b.form_fr_date,
                              SUM (a.ord_qty) qty, SUM (a.ord_amt) amt
                         FROM tb_aj002 a,
                              (SELECT a.good_id, a.so_id, a.form_fr_date,
                                      a.form_fr_date - 1 / 24 start_time,
                                      a.form_end_date + 1 / 24 end_time
                                 FROM (SELECT DISTINCT b.good_id, a.so_id,
                                                       a.form_fr_date,
                                                       a.form_end_date
                                                  FROM tb_ab010 a, tb_ab060 b
                                                 WHERE a.title_id = b.title_id
                                                   AND a.form_end_date IS NOT NULL

                                                   and a.so_id<>'1000023'
                                                 AND a.so_id=                                                         DECODE (in_so_id,
                                                                  0, a.so_id,
                                                                  in_so_id
                                                                 )
                                                   AND a.form_fr_date
                                                          BETWEEN in_start_day
                                                              AND   in_end_day
                                                                  - 0 / 12) a) b
                        WHERE a.good_id = b.good_id
                          AND a.so_id = b.so_id
                          AND a.medi_lclss_id = 1    AND a.so_id =
                                       DECODE (in_so_id,
                                               0, a.so_id,
                                               in_so_id)
                          AND a.chg_yn = 'N'
                          AND a.ord_stat_cd < '90'
                          AND a.gift_cd IS NULL
                          AND a.outgo_site_no IN ('C01', 'C02', 'C03','C51')
                          AND a.etr_date BETWEEN b.start_time AND b.end_time
                          AND a.etr_date BETWEEN in_start_day AND in_end_day
                     GROUP BY b.good_id,b.so_id, b.form_fr_date,
                              b.end_time) b
              WHERE a.good_id = b.good_id(+)
                AND a.so_id = b.so_id(+)
                AND a.good_id = c.good_id
                AND a.form_fr_date = b.form_fr_date(+)) q
      ON (    p.good_id = q.good_id AND p.so_id = q.so_id  AND p.form_fr_date = q.form_fr_date)
      WHEN MATCHED THEN
         UPDATE
            SET p.qty = q.qty, p.amt = q.amt, p.profit = q.profit,
                p.mdf_id = in_etr_id, p.mdf_date = SYSDATE
      WHEN NOT MATCHED THEN
         INSERT
         VALUES (q.good_id, q.so_id, q.form_fr_date, q.form_end_date,
                 q.form_mis, q.qty, q.amt, q.profit, in_etr_id, SYSDATE,
                 in_etr_id, SYSDATE,0,0,0);
MERGE INTO tb_ap030 p
     USING (SELECT a.good_id, a.so_id, a.form_fr_date, a.form_end_date,
                    a.form_mis, NVL (b.qty, 0) qty, NVL (b.amt, 0) amt,
                    ROUND (NVL (b.amt, 0) - NVL (b.qty, 0) * c.tax_cost,
                           2
                          ) profit
               FROM tb_ag001 c,
                    (SELECT DISTINCT b.good_id, a.so_id, a.form_fr_date,
                                     a.form_end_date, a.form_mis
                                FROM tb_ab010 a, tb_ab060 b
                               WHERE a.title_id = b.title_id
                                 AND a.form_end_date IS NOT NULL
                                 /*AND a.title_cd <> '40'*/
                                 and a.so_id<>'1000023'
                                 AND a.so_id =
                                        DECODE (in_so_id,
                                                0, a.so_id,
                                                in_so_id
                                               )
                                 AND a.form_fr_date BETWEEN in_start_day
                                                        AND in_end_day
                                                            - 0 / 12) a,
                    (SELECT   b.good_id, b.so_id, b.form_fr_date,
                              SUM (a.ord_qty) qty, SUM (a.ord_amt) amt
                         FROM tb_aj002 a,
                              (SELECT a.good_id, a.so_id, a.form_fr_date,
                                      a.form_fr_date  start_time,
                                      a.form_end_date  end_time
                                 FROM (SELECT DISTINCT b.good_id, a.so_id,
                                                       a.form_fr_date,
                                                       a.form_end_date
                                                  FROM tb_ab010 a, tb_ab060 b
                                                 WHERE a.title_id = b.title_id
                                                   AND a.form_end_date IS NOT NULL
                                                   /*AND a.title_cd <> '40'*/
                                                   and a.so_id<>'1000023'
                                                   AND a.so_id =
                                                          DECODE (in_so_id,
                                                                  0, a.so_id,
                                                                  in_so_id
                                                                 )
                                                   AND a.form_fr_date
                                                          BETWEEN in_start_day
                                                              AND   in_end_day
                                                                  - 0 / 12) a) b
                        WHERE a.good_id = b.good_id
                          AND a.so_id = b.so_id
                          AND a.medi_lclss_id = 1
                          AND a.so_id =
                                       DECODE (in_so_id,
                                               0, a.so_id,
                                               in_so_id
                                              )
                          AND a.chg_yn = 'N'
                          AND a.ord_stat_cd < '90'
                          AND a.gift_cd IS NULL
                          AND a.outgo_site_no IN ('C01', 'C02', 'C03','C51')
                          AND a.etr_date BETWEEN b.start_time AND b.end_time
                          AND a.etr_date BETWEEN in_start_day AND in_end_day
                     GROUP BY b.good_id,
                              b.so_id,
                              b.form_fr_date,
                              b.end_time) b
              WHERE a.good_id = b.good_id(+)
                AND a.so_id = b.so_id(+)
                AND a.good_id = c.good_id
                AND a.form_fr_date = b.form_fr_date(+)) q
      ON (    p.good_id = q.good_id
          AND p.so_id = q.so_id
          AND p.form_fr_date = q.form_fr_date)
      WHEN MATCHED THEN
         UPDATE
            SET p.now_qty = q.qty, p.now_amt = q.amt, p.now_profit = q.profit,
                p.mdf_id = in_etr_id, p.mdf_date = SYSDATE
      WHEN NOT MATCHED THEN
         INSERT
         VALUES (q.good_id, q.so_id, q.form_fr_date, q.form_end_date,
                 q.form_mis, 0, 0, 0, in_etr_id, SYSDATE,
                 in_etr_id, SYSDATE,q.qty,q.amt,q.profit);
   COMMIT;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
            RAISE;
END sp_make_ap030_temp;
code]
这是完整的，里面两个MERGE单独执行没问题的

我们也查了下
原本是有个SCH_JOB在每天上班时间的每小时的15分和45分各跑一次，发现8号13：45跑的正常的，之后14：15就无响应了，而据我们DBA说当时没什么操作，只是早上清了个归档日志，时间点也对不起来

0 2010-06-17 11:09:53

只看TA 引用举报 #6    得分 0

ggxx008 本版专家分：15: 试试增加临时表空间数据文件的大小，可能是数据文件的HWM超过了空间大小！

0 2010-06-17 16:52:49

只看TA 引用举报 #8 得分 15

⋅分享贴，关于上次提问的有关DBLINK的问题 ⋅有关DBLINK的问题，是否是BUG? ⋅存储过程不能执行，但里面语句单独执行没问题 更多帖子 关注空间博客 hebo2005 本版专家分：27611 红花 2008年7月 Oracle大版内专家分月排行榜第一 2008年6月 Oracle大版内专家分月排行榜第一 2008年5月 Oracle大版内专家分月排行榜第一 黄花 2008年9月 Oracle大版内专家分月排行榜第二 2008年8月 Oracle大版内专家分月排行榜第二 2008年4月 Oracle大版内专家分月排行榜第二: 引用 8 楼 ggxx008 的回复:
试试增加临时表空间数据文件的大小，可能是数据文件的HWM超过了空间大小！

兄弟，能说说你的想法吗？
一直百思不得其解，为什么单独执行SQL就可以，用存储过程执行就不行，执行的数据天获取天数是一样的

0 2010-06-17 17:14:29

只看TA 引用举报 #9 得分 0

⋅在解析器程序中，如何错误处理，实现错误定位？ 更多帖子 关注空间博客 kongkongye 本版专家分：1723: 是否你的job 在调用时，存储过程的参数出现异常了？处理的数据量变的非常非常大？
否则你的语句在存储过程中和单独执行时，执行计划不同了？这个也有可能是存储过程中是绑定变量，而在单独执行时是常量。关注你相关表的分析时间。

总之，在job执行时，做个trace 看看应该就知道了。

0 2010-06-17 17:41:07

只看TA 引用举报 #10 得分 20

⋅分享贴，关于上次提问的有关DBLINK的问题 ⋅有关DBLINK的问题，是否是BUG? ⋅存储过程不能执行，但里面语句单独执行没问题 更多帖子 关注空间博客 hebo2005 本版专家分：27611 红花 2008年7月 Oracle大版内专家分月排行榜第一 2008年6月 Oracle大版内专家分月排行榜第一 2008年5月 Oracle大版内专家分月排行榜第一 黄花 2008年9月 Oracle大版内专家分月排行榜第二 2008年8月 Oracle大版内专家分月排行榜第二 2008年4月 Oracle大版内专家分月排行榜第二

参数无异常
用JOB传的



BEGIN 

  SYS.DBMS_JOB.REMOVE(301);

COMMIT;

END;

/



DECLARE

  X NUMBER;

BEGIN

  SYS.DBMS_JOB.SUBMIT

  ( job       => X 

   ,what      => 'CHINA.SP_MAKE_AP030

  (sysdate-3/* DATE */ ,

   sysdate/* DATE */ ,

   0 /* NUMBER */ ,

   ''SYSTEM''/* VARCHAR2 */  );'

   ,next_date => to_date('18/06/2010 06:00:00','dd/mm/yyyy hh24:mi:ss')

   ,interval  => 'trunc(SYSDATE+1)+1/4'

   ,no_parse  => FALSE

  );

  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));

COMMIT;

END;

/

就传三天的数据

手工调用存储过程也测试过，传一天进去，都是无响应
单独执行SQL，传五天进去
trunc(sysdate)-4 and sysdate
执行都很快

0 2010-06-17 18:02:46

只看TA 引用举报 #11 得分 0

⋅sql server2008 联机丛书疑问 ⋅ASE抽取数据到IQ ⋅ASE数据抽取到IQ方案 更多帖子 关注空间博客 iqlife 本版专家分：46857 红花 2010年7月 Oracle大版内专家分月排行榜第一 2010年6月 Oracle大版内专家分月排行榜第一 黄花 2013年4月 Oracle大版内专家分月排行榜第二 2013年3月 Oracle大版内专家分月排行榜第二 2010年5月 Oracle大版内专家分月排行榜第二 蓝花 2013年1月 Oracle大版内专家分月排行榜第三 2011年12月 Oracle大版内专家分月排行榜第三 2010年8月 Oracle大版内专家分月排行榜第三 2010年4月 Oracle大版内专家分月排行榜第三 2010年3月 Oracle大版内专家分月排行榜第三: 另外建立个JOB执行呢，能否执行？

0 2010-06-17 18:09:43

只看TA 引用举报 #12 得分 5

⋅求助：怎么在事件里面给触发该事件的按钮增加一个弹出窗口？ ⋅结婚回来，第一天上班，散分！ ⋅解决ORA-04091变异表的问题 更多帖子 关注空间博客 vber1010 本版专家分：3643: 楼主和我最最近遇到的问题几乎一样，我也是有一个过程，过程里面有个dml操作，如果单独执行这个dml操作的话执行速度很快，但是放到过程里面就不行了，执行时间非常长。我暂时得出的原因是：我这个dml里有个函数要调用执行，可能每次执行那个函数的时侯，过程里的上下文的切换占用了大量时间，我现在还不确定，楼主如果有好想法，一定将答案贴上来。

0 2010-06-17 22:30:10

只看TA 引用举报 #13 得分 20

⋅分享贴，关于上次提问的有关DBLINK的问题 ⋅有关DBLINK的问题，是否是BUG? ⋅存储过程不能执行，但里面语句单独执行没问题 更多帖子 关注空间博客 hebo2005 本版专家分：27611 红花 2008年7月 Oracle大版内专家分月排行榜第一 2008年6月 Oracle大版内专家分月排行榜第一 2008年5月 Oracle大版内专家分月排行榜第一 黄花 2008年9月 Oracle大版内专家分月排行榜第二 2008年8月 Oracle大版内专家分月排行榜第二 2008年4月 Oracle大版内专家分月排行榜第二: 引用 12 楼 java3344520 的回复:
另外建立个JOB执行呢，能否执行？

和JOB无关，手动执行存储过程一样，其它JOB基本都正常
奇怪的是，另外有个存储过程也是同样的情况
两个存储过程基本差不多，目标表结构也差不多，数据来源表也是同样的两张表

多谢楼上兄弟的回答，我也看看是否有你说的情况

0 2010-06-18 09:25:09

只看TA 引用举报 #14 得分 0

⋅最近想考OCP的进来看下 ⋅监听一直卡着，没有反应。 ⋅求sql语句 更多帖子 关注空间博客 mahanso 本版专家分：1332: 一、先trace一下，alter session set SQL_TRACE=true;查看一下.trc文件，看看oracle在执行job过程中都做了那些事情。

二、如果问题定位不出来，再看看alert_orcl.log文件，看看数据最近出现什么错误，尝试定位问题原因。

0 2010-06-18 10:20:51

只看TA 引用举报 #15 得分 20

⋅分享贴，关于上次提问的有关DBLINK的问题 ⋅有关DBLINK的问题，是否是BUG? ⋅存储过程不能执行，但里面语句单独执行没问题 更多帖子 关注空间博客 hebo2005 本版专家分：27611 红花 2008年7月 Oracle大版内专家分月排行榜第一 2008年6月 Oracle大版内专家分月排行榜第一 2008年5月 Oracle大版内专家分月排行榜第一 黄花 2008年9月 Oracle大版内专家分月排行榜第二 2008年8月 Oracle大版内专家分月排行榜第二 2008年4月 Oracle大版内专家分月排行榜第二: 咨询了下盖国强，也说跟踪下，怀疑是执行计划出了问题
现在做了如下改动
另外写了个存储过程，与原来的存储过程的差别就是不传参数进去，直接固定参数，即单独执行的SQL
这个存储过程执行就正常了

怀疑可能有做过表分析或者索引重建之类的，造成存储过程里的执行计划有变动
先结贴，慢慢再研究

0 2010-06-29 09:26:51

只看TA 引用举报 #16 得分 0

存储过程不能执行，但里面语句单独执行没问题 [问题点数：100分，结帖人hebo2005]