This discussion is archived
0 Replies Latest reply: Dec 20, 2012 3:03 AM by Jaissy RSS

Arrays vs Ref Cursors

Jaissy Newbie
Currently Being Moderated
Hi,
Did anybody worked on comparing performance of using Arrays Vs Ref Cursor for returning data to application. Which one has more preference? I'm trying to find any new findings.


I have following details :

http://stackoverflow.com/questions/9639646/oracle-stored-procedure-returning-ref-cursor-vs-associative-arrays

"In your case, however, since you're returning the data to a client application, the use of bulk operations is much less significant. Any decent client-side API is going to have functionality that lets the client specify how many rows need to be fetched from the cursor in each network round-trip and those fetch requests are going to go directly to the SQL VM, not through the PL/SQL VM, so there are no SQL to PL/SQL context shifts to worry about. Your application has to worry about fetching an appropriate number of rows in each round-trip-- enough that the application doesn't become too chatty and bottleneck on the network but not so many that you have to wait too long for the results to be returned or to store too much data in memory.
Returning PL/SQL collections rather than a REF CURSOR to a client application isn't going to reduce the number of context shifts that take place. But it is going to have a bunch of other downsides not the least of which is memory usage. A PL/SQL collection has to be stored entirely in the process global area (PGA) (assuming dedicated server connections) on the database server. This is a chunk of memory that has to be allocated from the server's RAM. That means that the server is going to have to allocate memory in which to fetch every last row that every client requests. That, in turn, is going to dramatically limit the scalability of your application and, depending on the database configuration, may steal RAM away from other parts of the Oracle database that would be very useful in improving application performance. And if you run out of PGA space, your sessions will start to get memory related errors. Even in purely PL/SQL based applications, you would never want to fetch all the data into collections, you'd always want to fetch it in smaller batches, in order to minimize the amount of PGA you're using.
In addition, fetching all the data into memory is going to make the application feel much slower. Almost any framework is going to allow you to fetch data as you need it so, for example, if you have a report that you are displaying in pages of 25 rows each, your application would only need to fetch the first 25 rows before painting the first screen. And it would never have to fetch the next 25 rows unless the user happened to request the next page of results. If you're fetching the data into arrays like your DBA proposes, however, you're going to have to fetch all the rows before your application can start displaying the first row even if the user never wants to see more than the first handful of rows. That's going to mean a lot more I/O on the database server to fetch all the rows, more PGA on the server, more RAM on the application server to buffer the result, and longer waits for the network.
Definition of a ref cursor
The name "ref cursor" can be confusing because a ref cursor variable is not a cursor, but a variable that points to a cursor. In essence, a ref cursor allows you to store the result of a "bulk collect" from a table (or any PL/SQL cursor return set) into a PL/SQL table (RAM array) and then reference the array with the ref cursor as a pointer. Below is a simple example of a ref cursor.
Once you load a PL/SQL table (an in-RAM array), a ref cursor allows you to reference the array within your PL/SQL code without having to pass the array to a new procedure or function. Instead of passing the "baggage" of the actual RAM array, you simple pass the ref cursor, which points to the array values."

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points