| Lesson 9 || Re-use of SQL in the library cache |
| Objective || Understand SQL re-use in the library cache. |
Re-use of SQL in the Library Cache
Reduce 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
SELECT COUNT(*) FROM CUSTOMER WHERE STATUS = 'NEW';
SELECT COUNT(*) FROM CUSTOMER WHERE STATUS = 'PREFERRED';
This problem is easily alleviated by using an identical bind variable, such as:
SELECT COUNT(*) FROM CUSTOMER
WHERE STATUS = :var1;
Bundling stored procedures
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.
Re-use Sql Library Cache - Exercise