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

hebo2005 2010-06-17 09:30:45 




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 这两个错误
...全文
2176 16 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
hebo2005 2010-06-29
  • 打赏
  • 举报
 回复
咨询了下盖国强，也说跟踪下，怀疑是执行计划出了问题
现在做了如下改动
另外写了个存储过程，与原来的存储过程的差别就是不传参数进去，直接固定参数，即单独执行的SQL
这个存储过程执行就正常了

怀疑可能有做过表分析或者索引重建之类的，造成存储过程里的执行计划有变动
先结贴，慢慢再研究
mahanso 2010-06-18
  • 打赏
  • 举报
 回复
一、先trace一下，alter session set SQL_TRACE=true;查看一下.trc文件，看看oracle在执行job过程中都做了那些事情。

二、如果问题定位不出来，再看看alert_orcl.log文件，看看数据最近出现什么错误，尝试定位问题原因。
hebo2005 2010-06-18
  • 打赏
  • 举报
 回复
[Quote=引用 12 楼 java3344520 的回复:]
另外建立个JOB执行呢，能否执行？
[/Quote]
和JOB无关，手动执行存储过程一样，其它JOB基本都正常
奇怪的是，另外有个存储过程也是同样的情况
两个存储过程基本差不多，目标表结构也差不多，数据来源表也是同样的两张表

多谢楼上兄弟的回答，我也看看是否有你说的情况
hebo2005 2010-06-17
  • 打赏
  • 举报
 回复
[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说当时没什么操作，只是早上清了个归档日志，时间点也对不起来
iqlife 2010-06-17
  • 打赏
  • 举报
 回复
WHAT里的内容 可以的话用MERGE 代码替换了试试...
看问题是出在存储过程还是本身JOB
vber1010 2010-06-17
  • 打赏
  • 举报
 回复
楼主和我最最近遇到的问题几乎一样，我也是有一个过程，过程里面有个dml操作，如果单独执行这个dml操作的话执行速度很快，但是放到过程里面就不行了，执行时间非常长。我暂时得出的原因是：我这个dml里有个函数要调用执行，可能每次执行那个函数的时侯，过程里的上下文的切换占用了大量时间，我现在还不确定，楼主如果有好想法，一定将答案贴上来。
hebo2005 2010-06-17
  • 打赏
  • 举报
 回复
最近没做过变动，从8号开始，突然JOB不能执行
存储过程里的就两个MERGE，单独拿出来执行都正常
用存储过程就是不行
由于此存储过程正好也要更新，所以索性更新成新的，两个MERGE我并成了一句，结果也是同样的情况，单独执行没问题，存储过程一执行，负载就上去，一直执行不出来

单独执行的语句，就是修改了四个参数
in_start_day - trunc(sysdate)-4
in_end_day -sysdate
in_so_id -0
in_etr_id -'SYSTEM'
iqlife 2010-06-17
  • 打赏
  • 举报
 回复
什么时候开始这样的?最近做过什么变动??
应用层,后台...
好像是数据超过长度造成的?看下插入的数据是否有异常...
wanglipo 2010-06-17
  • 打赏
  • 举报
 回复
关注 帮顶。。。。。。。。。。。。。。。。
ngx20080110 2010-06-17
  • 打赏
  • 举报
 回复
不懂，帮顶
iqlife 2010-06-17
  • 打赏
  • 举报
 回复
另外建立个JOB执行呢，能否执行？
hebo2005 2010-06-17
  • 打赏
  • 举报
 回复
参数无异常
用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
执行都很快
kongkongye 2010-06-17
  • 打赏
  • 举报
 回复
是否你的job 在调用时，存储过程的参数出现异常了？处理的数据量变的非常非常大？
否则 你的语句在存储过程中和单独执行时，执行计划不同了？ 这个也有可能是存储过程中是绑定变量，而在单独执行时是常量。关注你相关表的分析时间。

总之，在job执行时，做个trace 看看应该就知道了。
hebo2005 2010-06-17
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 ggxx008 的回复:]
试试增加临时表空间数据文件的大小，可能是数据文件的HWM超过了空间大小！
[/Quote]
兄弟，能说说你的想法吗？
一直百思不得其解，为什么单独执行SQL就可以，用存储过程执行就不行，执行的数据天获取天数是一样的
ggxx008 2010-06-17
  • 打赏
  • 举报
 回复
试试增加临时表空间数据文件的大小，可能是数据文件的HWM超过了空间大小！
oracle存储过程不执行sql语句,Oracle存储过程执行update语句不报错不生效问题
转载链接:http://lin49940.iteye.com/blog/466626 今天一个同事写oracle 的存储过程遇到了一个问题, 他在里面update 操作不能完成更新的操作, 但是又不会报错. 如一个表 A(id,code, name, type) 在存储过程中的更新操作的语句: update A x set x.type = 变量A where x.code = 变量B; 变量A...
单独执行sql语句没问题，放到存储过程where判断失效
命名重合，不区分大小写，需改变名称
错误记录： MYSQL存储过程执行时间长，但单独执行sql没有问题
MYSQL存储过程执行时间长，但单独执行sql没有问题：查看字符集
mysql，单独执行sql语句和写在存储过程中耗时不一样的问题
最近查看之前数据库问题时发现当时出现并发过高，数据库查询出现瓶颈，导致登录响应过长。写了几个测试脚本定位问题，最后发现是因为同样的语句，直接调用和放在存储过程中调用的时间差距过大导致。 当时表格内容有30多W条记录，加了索引，按理说这点量不应该出现效率问题，最近我在自己电脑上模拟了一样的表格，里面放入100+W记录，执行一条select的时间，放在存储过程和直接调用时间如下： 单独执行如下图，时间...
存储过程很慢单独执行很快_DB2中存储过程执行慢问题故障处理
其实这个问题是以前同一个客户遇见的问题，当时一个工程师解决后记录的过程如下：应用同事反映但是对应到执行存储过程，执行了2，3个小时了，还没出来结果。存储过程主要是执行一条update sql语句，单独将语句拿出来，clp命令行执行很快，2－3s即可执行完成。执行的SP：call pdw.P_OCS_ACTIVE_UPDATE('20120304',?)存储过程主要业务SQL：/**********...
Oracle 高级技术

3,494

社区成员

18,710

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章