Home » RDBMS Server » Performance Tuning » Slow Execution Stored Procedure with cursors (Oracle, 8, Windows 2003)
Slow Execution Stored Procedure with cursors [message #350472] Thu, 25 September 2008 03:15 Go to next message
sabishs
Messages: 5
Registered: February 2007
Junior Member
Hi All,

I have a stored procedure with many cursors. The related tables contains nearly 600000 records. It is taking many hours(5 hours) for executing the procedure. Proper indexes are there. But still it is taking long time. But if I check the server performance at the time of execution, the CPU usage is showing only 2% and memory usage is also very low.
Is there any way to increase the performance of the procedure?

Why the CPU and memory usage is low? Will any configuration settings change in oracle will utilize more memory and CPU?

Please help.

The procedure is attached alongwith for reference

Regards
Sabish
  • Attachment: SP_Asset.sql
    (Size: 5.05KB, Downloaded 326 times)
Re: Slow Execution Stored Procedure with cursors [message #350595 is a reply to message #350472] Thu, 25 September 2008 09:36 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As I expected - loop within loop within loop.
You copuld ry using Dbms_Profiler to see how long each individual component is taking, but the only way to get a real performance boost is to rewrite it to use SQL instead of pl/sql loops.

For example, the final loop/insert can be rewritten by combining the Cursor and the insert as something like:
insert into FT0ASTLDGR (LOCNID,DOCNO,DOCDT,DOCID,ASSETCD,FM_CSTCD,FM_ASTLOCNCD,
                        TO_CSTCD,TO_ASTLOCNCD,CONSUME_DAYS,CONSUME_AMT,OPN_TYPE,PUR_DATE)
select p_locnid,tmp_docno,p_month,tmp_docid,rec1.assetcd,rec.cstcd,rec.astlocncd,
        rec.cstcd,rec.astlocncd,null,null,'Y',cum_date
from fm0asset
where locnid = p_locnid
and  assetcd = rec.assetcd;    
Previous Topic: Index Creation with Parallel Option
Next Topic: How to delete records without generating redo logs?
Goto Forum:
  


Current Time: Sat Dec 03 06:09:26 CST 2016

Total time taken to generate the page: 0.09811 seconds