 |
|
Tuning the Library Cache
Oracle Tips by Burleson
|
The library cache is arguably the most
important area of the SGA. The shared SQL areas and the PL/SQL areas
reside in the library cache, and this is the true center of activity
within Oracle.
The activity of SQL within the library cache is
critical to the performance of Oracle. We already discussed the use of
cursor_sharing to make SQL reusable, but there are some other
types of SQL that are always reparsed.
One of the biggest problems with Oracle SQL
prior to Oracle8i was that execution plans for SQL could not be
stored. In Oracle8i, we have the ability to use the outline
procedure to store the execution plan for an SQL statement, but Oracle
still has problems recognizing “similar” SQL statements. For example,
Oracle library cache will examine the following SQL statements and
conclude that they are not identical:
SELECT *
FROM customer;
Select * From Customer;
While capitalizing a single letter, adding an
extra space between verbs, or using a different variable name might
seem trivial, the Oracle software is not sufficiently intelligent to
recognize that the statements are identical. Consequently, Oracle will
reparse and execute the second SQL statement, even though it is
functionally identical to the first SQL statement.
Note that starting in Oracle the new parser
uppercases all non-literals and compresses whitespace before doing the
hash, this will result in better identification of identical SQL.
The best way to prevent SQL reloads is to
encapsulate all SQL into stored procedures, and place these stored
procedures into packages. This removes all SQL from application
programs and moves them into Oracle's data dictionary. This method
also has the nice side effect of making all calls to the Oracle
database look like a logical function. For example, instead of having
a complex SQL statement inside a program, you would have a single call
to a stored procedure.
There are other ways to make storage reusable
within the library cache. The cursor_space_for_time Oracle
parameter can be used to speed executions within the library cache.
Setting cursor_space_for_time to FALSE tells Oracle that a
shared SQL area can be deallocated from the library cache to make room
for a new SQL statement. Setting cursor_space_for_time to TRUE
means that all shared SQL areas are pinned in the cache until all
application cursors are closed. When set to TRUE, Oracle will not
bother to check the library cache on subsequent execution calls
because it has already pinned the SQL in the cache. This technique can
improve the performance for some queries, but cursor_space_for_time
should not be set to TRUE if there are cache misses on execution
calls. Cache misses indicate that the shared_pool_size is
already too small, and forcing the pinning of shared SQL areas will
only aggravate the problem.
Another way to improve performance on the
library cache is to use the Oracle session_cached_cursors
parameter. As you probably know, Oracle checks the library cache for
parsed SQL statements, but session_cached_cursors can be used
to cache the cursors for a query. This is especially useful for tasks
that repeatedly issue parse calls for the same SQL statement—for
instance, where a 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:
select
sum(dollars_sold)
from
sales_table
where
region = :var1;
Now that we have reviewed techniques for
efficiently using library cache storage, let's look at a STATSPACK
report that will show us what is happening inside Oracle. There are
several metrics that address the inner workings of the library cache.
Monitoring the Library Cache
Miss Ratio
The library cache miss ratio tells the DBA whether or
not to add space to the shared pool, and it represents the ratio of
the sum of library cache reloads to the sum of pins. In general, if
the library cache ratio is over 1, you should consider adding to the
shared_pool_size. Library cache misses occur during the parsing
and preparation of the execution plans for SQL statements. The
compilation of a SQL statement consists of two phases: the parse phase
and the execute phase. When the time comes to parse a SQL statement,
Oracle first checks to see if the parsed representation of the
statement already exists in the library cache. If not, Oracle will
allocate a shared SQL area within the library cache and then parse the
SQL statement. At execution time, Oracle checks to see if a parsed
representation of the SQL statement already exists in the library
cache. If not, Oracle will reparse and execute the statement.
The following STATSPACK script will compute the
library cache miss ratio. Note that the script sums all of the values
for the individual components within the library cache and provides an
instance-wide view of the health of the library cache.
rpt_lib_miss.sql
set lines
80;
set pages 999;
column mydate heading 'Yr. Mo Dy Hr.' format a16
column c1 heading "execs" format 9,999,999
column c2 heading "Cache Misses|While Executing" format 9,999,999
column c3 heading "Library Cache|Miss Ratio" format 999.99999
break on mydate skip 2;
select
to_char(snap_time,'yyyy-mm-dd HH24') mydate,
sum(new.pins-old.pins) c1,
sum(new.reloads-old.reloads) c2,
sum(new.reloads-old.reloads)/
sum(new.pins-old.pins) library_cache_miss_ratio
from
stats$librarycache old,
stats$librarycache new,
stats$snapshot sn
where
new.snap_id = sn.snap_id
and
old.snap_id = new.snap_id-1
and
old.namespace = new.namespace
group by
to_char(snap_time,'yyyy-mm-dd HH24')
;
Here is the output. The following report can easily be
customized to alert the DBA during times when there are excessive
executions or library cache misses.
Cache Misses
Yr. Mo Dy Hr. execs While Executing
LIBRARY_CACHE_MISS_RATIO
---------------- ---------- --------------- ------------------------
2001-12-20 10 10,338 3 .00029
2001-12-20 11 182,477 134 .00073
2001-12-20 12 190,707 202 .00106
2001-12-20 13 2,803 11 .00392
Once this report identifies a time period where there
may be a problem, STATSPACK provides the ability to run detailed
reports to show the behavior of the objects within the library cache.
This is an excerpt from "Oracle
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts. |
|
