pl/sql存储过程...执行时间在哪里? [英] pl/sql Stored procedure... where does the execution time go?

问题描述

我当前正在跟踪存储过程中的性能泄漏. 在最初的开始"之后紧接着放一个时间戳，而在最后的结束"之前紧紧放一个时间戳(我正在做一个 before )，该过程占用了abt. 10秒完成. 但是，我必须等待2分钟以上才能结束.

I am currently tracing a performance leak in a stored procedure. Having a timestamp put out right after the initial "begin" and one right before the final "end" (I am doing a commit before) says the procedure takes abt. 10 secs to finish. However, I have to wait 2mins+ for it to end.

有人能告诉我其余时间在哪里吗?我正在使用Oracle SQL dev，但在其余时间中似乎并没有空闲，该过程似乎在各自的表上保持了锁:(

Can anybody tell me where the rest of the times goes? I am using Oracle SQL dev but It doesn't seem to be idleing for the rest of the time, the procedure seems to hold a lock on the respective table :(

非常感谢您的启发....

Thanks a lot in advance for enlightening....

编辑:再次感谢您的输入:) 这是该过程的剥离代码，具体取决于要处理的项目数，第1部分当前为abt. 10至40秒，第二节几毫秒.但是，该过程需要2到8分钟才能运行. 同样，包含要删除的数据的表似乎比所需的锁定时间更长，从而导致插入操作被推迟. 按计划的作业启动它并没有什么区别，相同的行为.

thanks again for your input :) here's the stripped code for the procedure, depending on the number of items to be processed, the 1st section currently takes abt. 10 to 40 seconds, the 2nd section a few millisecs. however the procedure takes 2 to 8 mins to run. also, the table containing the data to delete seem to be locked somewhat longer than needed, causing inserts to be deferred. starting it as scheduled job makes no difference btw, same behavior.

create or replace
procedure MY_PROCEDURE is
start_procedure number;
start_delete number;
end_procedure number;
begin

  start_procedure :=dbms_utility.get_time;

  begin
  -- stripped: doing some selects/updates here
  end;
  commit;

  start_delete :=dbms_utility.get_time ;

  begin
  -- stripped: cleanig up some other data here
  end;
  commit;
  end_procedure :=dbms_utility.get_time ;

  dbms_output.put_line('procedure took: '||to_char((end_procedure- start_procedure)/1000));
  dbms_output.put_line('updates took: '||to_char((start_delete- start_procedure)/1000));
  dbms_output.put_line('delete took: '||to_char((end_procedure-start_delete)/1000));

end;

推荐答案

问题已解决，但我还是不明白...

Problem solved, but I still do not understand...

泄漏是由埋在另一个过程中的错误的select语句引起的，该另一个过程被另一个过程调用.我对其进行了优化，现在它就像魅力一样运行.

The leak was caused by a bad select statement buried in another procedure which is called by the other procedure. I optimized it, now it runs like charm.

我发现了困难的方法，逐行注释...尝试并尝试.

I found out the hard way, commenting line by line... trying and trying.

但是，我仍然想知道为什么时间戳绝对不准确.这是非常令人误解的，使我花费了很多时间. 那么，我创建时间戳的方式有什么问题吗?

However, I'd still like to know why the timestamps were absolutely inaccurate. This was very misleading and cost me quite some time... So, is there anything wrong with the way I create the timestamps?

如果有人对此有任何信息，我将很高兴听到它.

If anyone has info on this, I'd be glad to hear it.

感谢大家在此问题上的帮助.

Thanks to all of you for your help with this issue.

这篇关于pl/sql存储过程...执行时间在哪里?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！