SQL & PL/SQL
While all SQL tuning professionals advocate tuning each individual SQL statement to reduce logical I/O, there are many cases where you do not have the luxury of tuning each-and-every SQL statement in an application. In these cases, the best you can hope to do is adjust the global optimizer parameters to optimizer as many SQL statements as possible.
Below is an example of how to setup a REF CURSOR and how to call it from a PL/SQL block.
Why would you want to use REF CURSOR's? REF CURSORs can often provide greater performance when working with data. Much of these comes from the fact that packages are stored into the SHARED POOL of the Oracle database or otherwise known as PINNED to memory.
What I did find interesting was that the process that executes this procedure never needs to know what tables the view touches or even be able to see the view. The procedure simply returns the requested columns.
Also: It would be entirely feasible, using REF CURSOR design, to have queries stored in a LONG column in a table. Remembering that the length of the data cannot exceed 32K. These queries can then be retrieved at package execution time. This could often reduce the number of production moves regarding packages. You could further enhance it by versioning your queries, so that only the most recent would be retrieved.