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. The library cache contains all shared structures. This includes:

1. The SQL or PL/SQL source statements (SQL, stored procedures, packages)
2. The parse tree for the SQL statements
3. Cursors for SQL statements
4. The execution plan for each SQL statement

The objects in the library cache function just like any other in-memory buffer, and use 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 reused 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.

• Tuning the library cache Tuning the library cache involves the following activities:Minimizing the reparsing 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.

Analyze the following uploaded image and 1) print off the text and 2) describe any relevant features.

Oracle's library cache, a part of the shared pool in the System Global Area (SGA), plays a significant role in ensuring database performance. The cache stores executable forms of SQL cursors, PL/SQL programs, and Java classes, reducing the need to re-parse repeated SQL statements or PL/SQL blocks. However, contention within the library cache can degrade performance. Here are some strategies to reduce library cache contention while tuning the Oracle instance:

1. Utilize Bind Variables: Using bind variables significantly decreases the parsing load on the server by allowing the sharing of similar SQL statements that only differ in some literal values. Without bind variables, each SQL statement is treated as unique, leading to increased parsing and reduced efficiency. Therefore, make sure applications use bind variables where appropriate.
2. Use Cursor Sharing Wisely: The CURSOR_SHARING parameter determines the conditions under which identical text cursors will be shared. The parameter can take the values EXACT, FORCE, or SIMILAR. While FORCE and SIMILAR may increase cursor sharing, they can also lead to suboptimal execution plans in some cases. Therefore, use these settings judiciously.
3. Increase Shared Pool Size: If the shared pool is not large enough to store the frequently used SQL statements and PL/SQL blocks, they will be aged out, leading to additional parsing when they are reused. Increasing the size of the shared pool may decrease library cache contention.
4. Pin Frequently Used Packages: Use the DBMS_SHARED_POOL package to pin frequently used packages, sequences, and triggers in the shared pool. Pinned objects are not aged out of the shared pool, reducing the need for reparsing and reloads.
5. Avoid Invalidations: Changes to database objects can cause dependent SQL cursors to be invalidated. These cursors will then need to be reparsed the next time they are executed, leading to increased library cache latching. Minimize changes to database objects during high-load periods to avoid unnecessary invalidations.
6. Use PL/SQL Packages: PL/SQL packages encapsulate related procedures and functions into a single unit. The whole package is loaded into memory upon the first call to any procedure or function in the package, reducing disk I/O and library cache latch contention.
7. Optimize SQL Statements: Unnecessarily complex SQL statements may require extensive parsing and use more library cache resources. Use EXPLAIN PLAN and other SQL tuning methods to ensure SQL statements are as efficient as possible.
8. Tune the Application: Often, the application's behavior is the root cause of library cache contention. Minimize the parse-to-execute ratio by using techniques such as session pooling, statement caching, and result-set caching.

By using these strategies, database administrators can significantly reduce contention within the library cache, thereby improving the performance and scalability of Oracle instances. Contention is caused because latches and enqueues are used to synchronize Oracle operations.

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, which you will be learning about later in this course.

• Tuning Tips Whenever an SQL statement is processed by Oracle, the library cache is checked first to see if the SQL is already been parsed. This checking is done in-memory so it is very fast, but performance can degrade and cause excessive shared pool fragmentation if the library cache is full of hundreds of non-reusable SQL statements. In these cases, use the
  

  ALTER SYSTEM FLUSH SHARED POOL
  

  
  command to empty the shared pool. Here are some other tuning strategies for the library cache that you will be learning about later in the course:

1. Pin all frequently accessed PL/SQL packages in the shared pool with dbms_shared_pool.keep.
2. Try to write all SQL so that it can be shared by multiple tasks.
3. A low pin hit ratio or too many reloads indicate a need to increase the shared_pool_size init.ora parameter.

In the next lesson, you will learn how to address problems in the data dictionary cache.