Re-use of SQL in the Library Cache

One of the most important measures a developer can take to reduce the use of the library cache is to ensure that all SQL is written within stored procedures. For example, Oracle library cache will examine the following SQL statements and conclude that they are not identical:

SELECT * FROM customer;
SELECT * FROM Customer;

While capitalizing a single letter, adding an extra space between verbs, or using a different variable name might seem trivial, the Oracle software is not sufficiently intelligent to recognize that the statements are identical. Consequently, Oracle will re-parse and execute the second SQL statement, even though it is functionally identical to the first SQL statement.
Another problem occurs when values are hard coded into SQL statements. For example, Oracle considers the following statements to be different:

SELECT COUNT(*) FROM CUSTOMER WHERE STATUS = 'NEW';
SELECT COUNT(*) FROM CUSTOMER WHERE STATUS = 'PREFERRED';

SELECT COUNT(*) FROM CUSTOMER 
WHERE STATUS = :var1;

To prevent reloads from happening, encapsulate all SQL into stored procedures, bundling the stored procedures into packages. This removes all SQL from application programs and moves them into Oracle's data dictionary. The next lesson concludes this module.

Before moving on to the next lesson, click the Exercise link below to practice identifying library cache problems.
Re use Sql Library Cache - Exercise