SQL & PL/SQL

SQL and PL/SQL Articles

Irfan Haq's picture

PL/SQL Best Practices

articles: 

As the discipline of software development evolves, software products grow in size and the code grows in complexity, the need for a set of guidelines and standards becomes increasingly evident. In this article I will enumerate some of the industry best practices that the experts have proposed and emphasized, in the context of PL/SQL programming.

Donald K. Burleson's picture

Optimizing Oracle Optimizer Statistics

articles: 

Many Oracle professional do not realize the important of having good statistics for the Oracle cost-based SQL optimizer (CBO). The CBO has received a "bum-rap" from some Oracle professionals who wonder why the CBO is generating sub-optimal execution plans for their SQL statements.

Donald K. Burleson's picture

Oracle SQL tuning with parameters

articles: 

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.

Oracle REF CURSORs

articles: 

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.

Pages

Subscribe to RSS - SQL & PL/SQL