Question: How does SQL re-use in the library cache work in Oracle?
In Oracle, SQL reuse in the library cache forms an integral part of its shared SQL area to enhance efficiency and performance. The library cache, a component of the System Global Area (SGA), stores executable versions of SQL statements, PL/SQL blocks, and other significant pieces of reusable code.
The key idea behind SQL reuse in the library cache is the concept of "soft parsing" as opposed to "hard parsing".
When a SQL statement is submitted for execution, Oracle first checks in the library cache to see if a parsed representation of the same statement already exists. This is done by comparing the hash values of the new SQL statement with those of the existing statements in the library cache. The SQL text must be exactly the same, including case and white space, to be considered identical. If a match is found, the existing parsed SQL statement is reused. This process is referred to as a "soft parse" or "library cache hit". It reduces the overhead of parsing, thereby improving performance.
If a parsed version of the SQL statement doesn't exist in the library cache, Oracle must perform a "hard parse" or a "library cache miss". During a hard parse, Oracle performs syntax checking, semantic checking, and optimization, generating an execution plan for the SQL statement. This process is CPU-intensive and can slow down execution, especially if performed frequently.
The following practices can maximize SQL reuse in the library cache:
- Use Bind Variables: Bind variables are placeholders in a SQL statement that must be filled in before the database can execute the statement. When you use bind variables, Oracle can match new SQL statements to already parsed statements in the library cache, even if the specific values in the statements are different. This greatly increases the chances of a soft parse.
- Use Stored Procedures: When you encapsulate SQL code within a stored procedure, the procedure is parsed and stored in the library cache when first executed. Subsequent executions can then reuse this parsed version, leading to soft parsing.
- Use SQL Tuning Advisor: The SQL Tuning Advisor can analyze high-load SQL statements and provide recommendations to improve efficiency, including ways to improve SQL reuse.
To monitor SQL reuse in the library cache, you can use views such as V$SQLAREA, V$SQL, and V$SQLSTATS. For example, the V$SQL view provides information on the parsing, execution, and fetching of a SQL statement, including the number of times the statement was parsed (hard or soft).
In conclusion, proper utilization of SQL reuse in the library cache is a critical aspect of Oracle database performance tuning. Using techniques such as bind variables and stored procedures, and monitoring with appropriate views, can significantly enhance the efficiency and performance of your Oracle database.
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 because of differences in case:
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:
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.