Donald K. Burleson

Oracle and Expert Systems Technology

Oracle - Understanding the Library Cache
 

The library cache is sometimes referred to as the "shared SQL area".  As the name implies, the shared SQL area is used to keep and process SQL statements and PL/SQL code.

Included in the library cache are all shared structures.  This includes the following:

• The SQL or PL/SQL source statements (SQL, stored procedures, packages)

• The parse tree for the SQL statements

• Cursors for SQL statements

• Parse trees for the SQL statements

• The execution plan for each SQL statement

The objects in the library cache functions just like any other in-memory buffer, and uses a least-recently-used algorithm.  Objects age out of the library cache in the same fashion that data blocks age out of the data buffer cache.  SQL is re-used in the library cache by declaring private cursor for each task.  In this fashion, many tasks may be executing the same SQL statement, but with different host variables and different results.

The tuning of the library cache involves the following tasks:

• Minimizing the re-parsing of SQL statements.  Oracle will only re-use SQL statements that are absolutely identical.  For example, if we issued the SQL "select * from customer;", Oracle will scan the library cache to see if this statement has already been parsed.  It would request the statement "select * from Customer;" because the capitalization of the SQL statements is different.

• Reducing contention within the library cache.  Contention is caused because latches and enqueues are used to synchronize Oracle operations.

• Preventing re-loads of SQL and PL/SQL - This occurs when the shared_pool_size is not large enough to hold all of the SQL and PL/SQL statements.  Oracle provides for the "pinning" of PL/SQL packages in the library cache and we will be covering this in a later lesson.

Detecting library cache contention:

The library cache miss ratio tells the DBA whether or not to add space to the shared pool, and it represents the ratio of the sum of library cache reloads to the sum of pins. In general, if the library cache ratio is over 1, you should consider adding to the shared_pool_size. Library cache misses occur during the compilation of SQL statements. The compilation of an SQL statement consists of two phases: the parse phase and the execute phase. When the time comes to parse an SQL statement, Oracle first checks to see if the parsed representation of the statement already exists in the library cache. If not, Oracle will allocate a shared SQL area within the library cache and then parse the SQL statement. At execution time, Oracle checks to see if a parsed representation of the SQL statement already exists in the library cache. If not, Oracle will re-parse and execute the statement.

Within the library cache, hit ratios can be determined for all dictionary objects that are loaded. These include table/procedures, triggers, indexes, package bodies, and clusters. If any of the hit ratios fall below 75 percent, you should add to the shared_pool_size.

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.  Click here for more books by Donald K. Burleson.