Cursor slowing down a process

[satishpc]

Hi,

i have written a procedure that uses a cursor. the cursor has a query contains a minus and a few joins. One of the tables has more than a million rows. the problem is that when i open the cursor and start the loop, it is fetching only some rows at a time (anywhere between 1 and 200) and then again fully scans this huge tables. This is causing a huge performance bottleneck. i tried using various joins (merge, NL, hash) but it goes the same way. I also have the right indexes. any suggestions ? can i change any parameter to make it fecth more rows per table scan ?

Regards
satish

[chrisrlong]

I have no idea what you are talking about with the 1 to 200 bit, but your first problem is the use of a CURSOR. This should be avoided if possible. If you provide the SQL, PL/SQL and EXPLAIN PLAN, and a little more description, maybe we can help you out.

- Chris