>Hi,
>
>I have written some Stored Proc in ORACLE for making some calculations, in that i am invoking CURSORs for LOOPing.
>It takes more than 30 mins (1/2 hr) to excute the PROCEDURE for less than 700 records.
>I have tried to reduced the execution time by making the INDEX on the tables, but that also doesn't work.
>
>How should i reduced the execution time in procedures.
>
>
>Thanx in advance,

Amol,
In addition to what Tinm posted (we would like to see the procedure), you need to narrow down what i staking all the time. To help with this, limit the initial query that retuns 700 records so it only returns 1 to 3 rows. You can do this by adding:

and rownum <= 3

to your WHERE clause.

Next I recommend adding a series interval checks and displaying them. SOmething like the following:

t1 := sysdate;
-- code block 1 here
t2 := sysdate;
cinterval := to_char((t2-t1)*86400)
dbms_output.put_line('Interval 1: ' || cinterval);

Do this in a few places where you suspect the code is slow. Once you find out where the slow parts are, then you can determine whether adding indexes will help.

Note: For DBMS_OUTPUT to work, remember you need to have issued a SET SERVEROUTPUT ON prior to running your procedure.

HTH.