Hi,

I am writing some procedures/functions in 10G. Now I have to decide between using refernce cursor, or returning collection type (for example, array of some rec type). What are advantages/disadvantages of each? I am mainly looking to performance.

Thanks

Prashant

I suppose you are talking about returning the result of a query.

In that case, the main difference is that when returning a collection, you will have to fetch ALL data from your cursor so as to fill your arrays (or you will have to limit yourself for, say, the 100 first rows), and only then return to the caller. If you have many results to fetch, this solution may need a LOT of memory on the server as well as on the client program. On the contrary, when using a ref cursor, it's up to the client to fetch data as he desires : just the first ten rows, rows between 11 and 50, or all the rows - one row at a time (generally not a good idea), ten by ten, 100 by 100...

In some cases collections might be useful for OUT parameters, but they are mainly useful for IN parameters. In general, prefer to return REF CURSORS. Moreover, REF CURSORS will always be recognized by the client program, whatever the language (which language with an Oracle API could not get the result of a SELECT query ? ), whereas you may have bad surprises with collections support...

HTH & Regards,

rbaraer

Thanks rbaraer,

But wouldn't using ref cursor (when we expect 1000s of quries per min) will end up having so many open cursors on server. They will be open until client closes them, and hence will need lot of memory.

Thanks

Prashant

> They will be open until client closes them, and hence will need lot of memory.
If "collection" is implemented, hence you will need lot of bandwidth between server & client to pass all the data from server to client.
If the collection is large & number of clients is large, application may not scale.

That should be fine as long as :

- you allow sufficient cursors to be open (see the OPEN_CURSORS parameter). Tune this parameter so that you have enough open cursors, but don't put 1000000 here . We have 1000 and it's fine for us.

- clients release their cursors as soon as they're finished with them (of course if you forget to close cursors... you will run out of openable cursor before running out of memory ).

As anacedent said, using collections could be much worse in terms of memory consumption.

If you often re-use your statements (with bind variables), then also consider setting the SESSION_CACHED_CURSORS to a non-zero value, as it will improve response time as well as concurrency (if a statement is in the session cache, then there is no need to go and find it in the shared pool, which ends up in less latches).

You can search AskTom for more info on statement parsing, re-use and caching.

HTH & Regards,

rbaraer