| Slow Execution Stored Procedure with cursors [message #350472] |
Thu, 25 September 2008 03:15  |
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 1682 times)
|
|
|
|
| Re: Slow Execution Stored Procedure with cursors [message #350595 is a reply to message #350472] |
Thu, 25 September 2008 09:36  |
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; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
