The Oracle shared pool contains Oracle's library cache, which is responsible for collecting, parsing, interpreting, and executing all of the SQL statements that go against the Oracle database. Hence, the shared pool is a key component which can experience frequent contention, so it's necessary for the Oracle database administrator to check for shared pool contention.
Oracle library cache contention is easy to understand once you know how Oracle processes SQL statements. The library cache is used like the data buffers, a place to store ready-to-run SQL which has already been parsed, semantically checked, optimized and has a load-and-go execution plan in-place.
Library cache contention occurs when Oracle places a "latch" on a SQL statement in order to manage concurrency. There is a dedicated latch-related wait event for the more popular latches that often generate significant contention. For those events the name of the latch appears in the name of the wait event; such as latch: library cache or latch: cache buffers chains. This enables you to quickly figure out if a particular type of latch is responsible for most of the latch-related contention.
Oracle library cache pin wait contention is caused by contention with the library cache, the area used to store SQL executables for re-use. The library cache pin wait usually happens when you are compiling or parsing a PL/SQL object or a view.
The following query can show whether Oracle has contention for common library cache activities:
select
sid,
event,
p1raw,
seconds_in_wait,
wait_time
from
v$session_wait
where
event = 'library cache pin'
and
state = 'WAITING';
Library cache latches function to protect all cached SQL
statements as well as the associated object definitions contained
within the library cache region in the shared pool of the Oracle
SGA. Whenever new statements are added to the library cache area,
this type of latch must be acquired by Oracle as part of the
operation. Oracle scans the library cache area during the parse
phase for matching SQL statements. If one is not found, then Oracle
will complete the task, obtain the library cache latchfor the SQL statement and then insert it into the
library cache area for future usage by the Oracle database.
There is a hidden Oracle database initialization parameter called
kgl_latch_countwhich controls
behavior in terms of the number of library cache latches created
within the Oracle database. By default, this value should not be
changed as it is sufficiently set in most cases. In the event that
there should be a library cache contention issue and no other
recourse is available to resolve the contention with the library
cache latch, this value can be increased.
By the way, the default value for kgl_latch_countis set to the following prime number plus value of the
database initialization parameter for cpu_count.
The maximum allowable value for kgl_latch_count is no greater than
66 according to Oracle support per Bug # 1381824. Now move on to
learning more about shared pool latches within Oracle.
and include the URL for the page.
