Shared Pool   «Prev 

Improve library Cache Performance through Oracle SQL Tuning

Using session_cached_cursors is especially useful for tasks that repeatedly issue parse calls for the same SQL statement, for instance, where an SQL statement is repeatedly executed with a different variable value. An example would be the following SQL request that performs the same query 50 times, once for each state:
DBMS_LIBCACHE: Prepares the library cache on an Oracle instance by extracting SQL and PL/SQL from a remote instance and compiling this SQL locally without execution 

SELECT SUM(sale_amount) 
FROM SALES
WHERE
state_code = :var1;

  1. library cache: An area of memory in the shared pool. This cache includes the shared SQL areas, private SQL areas (in a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles.
  2. library cache hit: The reuse of SQL statement code found in the library cache.
  3. library cache miss: During SQL processing, the act of searching for a usable plan in the library cache and not finding it.

Library Cache Concepts

The library cache holds executable forms of SQL cursors, PL/SQL programs, and Java classes. This section focuses on tuning as it relates to cursors, PL/SQL programs, and Java classes. These are collectively referred to as application code. When application code is run, Oracle Database attempts to reuse existing code if it has been executed previously and can be shared. If the parsed representation of the statement does exist 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 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. Search this website for details on when a SQL and PL/SQL Library cache misses can 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. This is a hard parse. You might be able to reduce library cache misses on parse calls by ensuring that all sharable SQL statements are in the shared pool whenever possible.