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

Question

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 这两个错误

Answer 1

最近没做过变动，从8号开始，突然JOB不能执行
存储过程里的就两个MERGE，单独拿出来执行都正常
用存储过程就是不行
由于此存储过程正好也要更新，所以索性更新成新的，两个MERGE我并成了一句，结果也是同样的情况，单独执行没问题，存储过程一执行，负载就上去，一直执行不出来单独执行的语句，就是修改了四个参数
in_start_day   - trunc(sysdate)-4
   in_end_day     -sysdate                                         
   in_so_id       -0           
   in_etr_id     -'SYSTEM'

Answer 2

WHAT里的内容 可以的话用MERGE 代码替换了试试...
看问题是出在存储过程还是本身JOB

Answer 3

[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说当时没什么操作，只是早上清了个归档日志，时间点也对不起来

Answer 4

试试增加临时表空间数据文件的大小，可能是数据文件的HWM超过了空间大小！

Answer 5

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

Answer 6

是否你的job 在调用时，存储过程的参数出现异常了？处理的数据量变的非常非常大？
否则 你的语句在存储过程中和单独执行时，执行计划不同了？ 这个也有可能是存储过程中是绑定变量，而在单独执行时是常量。关注你相关表的分析时间。总之，在job执行时，做个trace 看看应该就知道了。

Answer 7

参数无异常
用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
执行都很快

Answer 8

另外建立个JOB执行呢，能否执行？

Answer 9

楼主和我最最近遇到的问题几乎一样，我也是有一个过程，过程里面有个dml操作，如果单独执行这个dml操作的话执行速度很快，但是放到过程里面就不行了，执行时间非常长。我暂时得出的原因是：我这个dml里有个函数要调用执行，可能每次执行那个函数的时侯，过程里的上下文的切换占用了大量时间，我现在还不确定，楼主如果有好想法，一定将答案贴上来。

Answer 10

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

Answer 11

一、先trace一下，alter session set SQL_TRACE=true;查看一下.trc文件，看看oracle在执行job过程中都做了那些事情。二、如果问题定位不出来，再看看alert_orcl.log文件，看看数据最近出现什么错误，尝试定位问题原因。

Answer 12

咨询了下盖国强，也说跟踪下，怀疑是执行计划出了问题
现在做了如下改动
另外写了个存储过程，与原来的存储过程的差别就是不传参数进去，直接固定参数，即单独执行的SQL
这个存储过程执行就正常了怀疑可能有做过表分析或者索引重建之类的，造成存储过程里的执行计划有变动
先结贴，慢慢再研究