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:
The SQL or PL/SQL source statements (SQL, stored procedures, packages)
The parse tree for the SQL statements
Cursors for SQL statements
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.
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, which you will be learning about later in this course.
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:
Pin all frequently accessed PL/SQL packages in the shared pool with dbms_shared_pool.keep.
Try to write all SQL so that it can be shared by multiple tasks.
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.