Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter