oracle - Is a pl/sql refcursor meant to return few records only? -

i have query performs , tuned too. put in procedure. when execute query sql set of values bind variables used in result produced in 3-4 seconds max.

the same resultset coming refcursor taking on 2 minutes give result. understand open fetch , close of cursor might taking time.

i have verified nothing else in procedure consuming time ruled out.

the number of records returned around 9000+ brings me question - ref cursor somehow less suitable when recordset of size beyond limit?

is ram size problem? have used toad execute both query , procedure compare. , yes have gone last record, not query returned first few.

what else can done improve refcursor speed? appreciated.

are using bulk collect grab multiple rows @ once?

  open c_cursor;   loop     fetch c_cursor     bulk collect l_tab limit 1000; -- or no limit fetch @ once     in 1 .. l_tab.last loop       -- process each row     end loop;     exit when c_cursor%notfound;   end loop   close c_cursor;