Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    46

    Question Unanswered: Ref Cursor Vs Collection Type

    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
    Last edited by prashant_bharti; 05-26-06 at 09:51.

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    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
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  3. #3
    Join Date
    Mar 2004
    Posts
    46
    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

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > 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.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by prashant_bharti
    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
    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
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •