February 12th, 2004, 07:04 AM
cursor fetch performance
I have performance problems with Oracle 9i.
A stored procedure returns a cursor as an OUT parameter :
The query is optimized, the select alone takes less than a second to execute.
CREATE my_proc (
curs OUT TYPES.type_my_curs
OPEN curs FOR
ORDER BY ... ;
Here is an extract from the package :
My problem is, when I try to FETCH results from my cursor, the time to execute climbs to more than a minute...
CURSOR my_curs IS
TYPE type_my_curs IS REF CURSOR RETURN my_curs%ROWTYPE;
Anyone has an idea ? Thanks in advance !
exit when my_c%NOTFOUND;
fetch my_c into my_var;