A cache miss on the library cache or data dictionary cache is more expensive than a miss on the buffer cache. For this reason, the shared pool should be sized to ensure that frequently-used data is cached.
Allocation of memory from the shared pool is performed in chunks. This chunking enables large objects (over 5 KB) to be loaded into the cache without requiring a single contiguous area. In this way, the database reduces the possibility of running out of contiguous memory due to fragmentation.
Oracle Library Cache Activity Sql Area
Download Zip: https://jinyurl.com/2vIDdC
The library cache stores executable forms of SQL cursors, PL/SQL programs, and Java classes, which are collectively referred to as the application code. This section focuses on tuning as it relates to the application code.
When the application code is executed, Oracle Database attempts to reuse existing code if it has been executed previously and can be shared. If the parsed representation of the SQL statement exists in the library cache and it can be shared, then the database reuses the existing code. This is known as a soft parse, or a library cache hit. If Oracle Database cannot use the existing code, then the database must build a new executable version of the application code. This is known as a hard parse, or a library cache miss. For information about when SQL and PL/SQL statements can be shared, see "SQL Sharing Criteria".
In order to perform a hard parse, Oracle Database uses more resources than during a soft parse. Resources used for a soft parse include CPU and library cache latch gets. Resources required for a hard parse include additional CPU, library cache latch gets, and shared pool latch gets. A hard parse may occur on either the parse step or the execute step when processing a SQL statement.
When an application makes a parse call for a SQL statement, if the parsed representation of the statement does not exist in the library cache, then Oracle Database parses the statement and stores the parsed form in the shared pool. To reduce library cache misses on parse calls, ensure that all sharable SQL statements are stored in the shared pool whenever possible.
When an application makes an execute call for a SQL statement, if the executable portion of the SQL statement is aged out (or deallocated) from the library cache to make room for another statement, then Oracle Database implicitly reparses the statement to create a new shared SQL area for it, and executes the statement. This also results in a hard parse. To reduce library cache misses on execution calls, allocate more memory to the library cache.
Reuse of shared SQL for multiple users running the same application avoids hard parsing. Soft parses provide a significant reduction in the use of resources, such as the shared pool and library cache latches.
When sizing the shared pool, the goal is to cache SQL statements that are executed multiple times in the library cache without over-allocating memory. To accomplish this goal, examine the following library cache statistics:
The INVALIDATIONS column in V$LIBRARYCACHE view shows the number of times library cache data was invalidated and had to be reparsed. This statistic should have a value near zero, especially on OLTP systems during peak loads. This means SQL statements that can be shared were invalidated by some operation (such as a DDL).
The library cache hit ratio is a broad indicator of the library cache health. This value should be considered along with the other statistics, such as the rate of hard parsing and if there is any shared pool or library cache latch contention.
Each row in this view contains statistics for one type of item kept in the library cache. The item described by each row is identified by the value of the NAMESPACE column. Rows with the following NAMESPACE values reflect library cache activity for SQL statements and PL/SQL blocks:
11,204 of these executions resulted in a library cache miss, requiring the database to implicitly reparse a statement or block, or reload an object definition because it aged out of the library cache.
The amount of memory available for the library cache can drastically affect the parse rate of Oracle Database. To help you correctly size the library cache, Oracle Database provides the following shared pool advisory views:
These shared pool advisory views provide information about library cache memory, enabling you to predict how changing the size of the shared pool can affect aging out of objects in the shared pool. The shared pool advisory statistics in these views track the library cache's use of shared pool memory and predict how the library cache will behave in shared pools of different sizes. Using these views enable you to determine:
The V$LIBRARY_CACHE_MEMORY view displays information about memory allocated to library cache memory objects in different namespaces. A memory object is an internal grouping of memory for efficient management. A library cache object may consist of one or more memory objects.
The V$JAVA_POOL_ADVICE and V$JAVA_LIBRARY_CACHE_MEMORY views contain Java pool advisory statistics that track information about library cache memory used for Java and predict how changing the size of the Java pool affects the parse rate.
Increasing the amount of memory for the shared pool increases the amount of memory available to the library cache, the dictionary cache, and the result cache. Before doing so, review the shared pool statistics and examine:
If there are no library cache misses, then consider setting the value of the CURSOR_SPACE_FOR_TIME initialization parameter to TRUE to accelerate execution calls. This parameter specifies whether a cursor can be deallocated from the library cache to make room for a new SQL statement.
Library cache misses indicate that the shared pool is not large enough to hold the shared SQL areas of all concurrently open cursors. If the shared pool does not have enough space for a new SQL statement and the value for this parameter is set to TRUE, then the statement cannot be parsed and Oracle Database returns an error indicating that there is not enough shared memory.
If the shared pool does not have enough space for a new SQL statement and the value of this parameter is set to FALSE, then Oracle Database deallocates an existing cursor. Although deallocating a cursor may result in a subsequent library cache miss (if the cursor is re-executed), this is preferable to an error halting the application because a SQL statement cannot be parsed.
Oracle Database checks the library cache to determine whether more than three parse requests have been issued on a given statement. If a cursor has been closed three times, then Oracle Database assumes that the session cursor associated with the statement should be cached and moves the cursor into the session cursor cache.
The best practice is to write sharable SQL and use the default value of EXACT for the CURSOR_SHARING initialization parameter. By default, Oracle Database uses adaptive cursor sharing to enable a single SQL statement that contains bind variables to use multiple execution plans. However, for applications with many similar statements that use literals instead of bind variables, setting the value of the CURSOR_SHARING parameter to FORCE may improve cursor sharing, resulting in reduced memory usage, faster parses, and reduced latch contention. Consider this approach when statements in the shared pool differ only in the values of literals, and when response time is poor because of a high number of library cache misses. In this case, setting the value of the CURSOR_SHARING parameter to FORCE maximizes cursor sharing and leverages adaptive cursor sharing to generate multiple execution plans based on different literal value ranges.
As Oracle Database allocates shared pool memory to shared server sessions, the amount of shared pool memory available for the library cache and data dictionary cache decreases. If you allocate the shared server session memory from a different pool, then the shared pool can be reserved for caching shared SQL.
Viewed 1000+ timesYou Asked select sum(pins), sum(reloads) from V$LIBRARYCACHE SUM(PINS) SUM(RELOADS) 287980714 640661 This means that only a small amount of sql is not being held in the library cache.(which is a good)As we are not fully using bind variables (ie our queries are not being cached) why is this so high?Bryan and Tom said...Well, while 640k sounds big (ask bill gates, I think he said once "640k should be enought for anybody") -- in light of the number of pins, its really rather small.As a pct of pins, it is very very small.You must either:o have some procedures you run infrequently. they get called in, aged out and called in again.o do things that force a flush (invalidation of code, grants, etc)In light of the fact that you do not make effective use of bind variables, this is quite simply "noise", your problem is bind variables, not that 0.22% of all PINS resulted in a reload. Rating (14 ratings)Is this answer out of date? If it is, please let us know via a Comment Comments Comment Pending question...Andre Whittick Nasser, November 28, 2001 - 12:24 pm UTC
To monitor and gain an understanding of how your currentlibrary cache has performed since your last startup of the Oracle database youcan issue the following SQL. Obviously if you are experiencing immediateperformance problems, you will want to look at the results of this query over aperiod of time. Regardless, each of the rows returned represents a specifictype of code (namespace) kept in the library cache and their particularperformance activity.
select sum(pins) pins, sum(pinhits) pinhits, sum(reloads) reloads, sum(invalidations) invalidations, 100-(sum(pinhits)/sum(pins)) *100 reparsing from v$librarycache;
As the name states, the librarycache load lock is concerned with obtaining a lock for a database object sothat it can load it into the library cache. This lock is always obtained in anEXCLUSIVE mode so that no one else can load the same object at the same time.If your session is trying to obtain this lock and someone else has alreadyobtained the lock, you will wait until they are done loading that object in thelibrary cache. 2ff7e9595c
Comments