February 12th, 2004, 06:04 AM
|
|
Junior Member
|
|
Join Date: Jun 2000
Posts: 2
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
cursor fetch performance
Hello,
I have performance problems with Oracle 9i.
A stored procedure returns a cursor as an OUT parameter :
Code:
CREATE my_proc (
curs OUT TYPES.type_my_curs
)
AS
BEGIN
OPEN curs FOR
SELECT DISTINCT...
FROM ...
WHERE ...
ORDER BY ... ;
END;
The query is optimized, the select alone takes less than a second to execute.
Here is an extract from the package :
Code:
CURSOR my_curs IS
SELECT ...
FROM ...;
TYPE type_my_curs IS REF CURSOR RETURN my_curs%ROWTYPE;
My problem is, when I try to FETCH results from my cursor, the time to execute climbs to more than a minute...
Code:
declare
my_c TYPES.type_my_curs;
my_var ...
begin
my_proc(my_c);
loop
exit when my_c%NOTFOUND;
fetch my_c into my_var;
end loop;
Anyone has an idea ? Thanks in advance !
|
Dev Shed Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
