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

 

 

   
 

Monitoring Objects Within the Library
Cache with STATSPACK


Oracle Tips by Burleson
 

Within the library cache, hit ratios can be determined for all dictionary objects that are loaded into the RAM buffer. These objects include tables/procedures, triggers, indexes, package bodies, and clusters. None of these objects should be experiencing problems within the library cache. If any of the hit ratios fall below 75 percent, you can increase the size of the shared pool by adding to the shared_pool_size Oracle parameter.

The STATSPACK table stats$librarycache is the table that keeps information about library cache activity. The table has three relevant columns: namespace, pins, and reloads. The first is the namespace, which indicates whether the measurement is for the SQL area, a table or procedure, a package body, or a trigger. The second value in this table is pins, which counts the number of times an item in the library cache is executed. The reloads column counts the number of times the parsed representation did not exist in the library cache, forcing Oracle to allocate the private SQL areas in order to parse and execute the statement.

Let's look at the STATSPACK scripts that we can use to monitor these objects inside the library cache.

STATSPACK Reports for the Library Cache

The following script reports on the details within the objects inside the library cache. While it is often useful to see the specifics for each object, we must remember that the only objects that can be pinned into storage are PL/SQL packages. We will be covering the pinning of packages into the SGA later in this chapter.

rpt_lib.sql

set lines 80;
set pages 999;

column mydate heading 'Yr.  Mo Dy  Hr.' format a16
column reloads       format 999,999,999
column hit_ratio     format 999.99
column pin_hit_ratio format 999.99

break on mydate skip 2;

select
   to_char(snap_time,'yyyy-mm-dd HH24')  mydate,
   new.namespace,
   (new.gethits-old.gethits)/(new.gets-old.gets) hit_ratio,
   (new.pinhits-old.pinhits)/(new.pins-old.pins) pin_hit_ratio,
   new.reloads
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
and
   new.gets-old.gets > 0
and
   new.pins-old.pins > 0
;

One nice feature of the resulting STATSPACK report is that it shows the activity within the library cache between each snapshot period.

Yr.  Mo Dy  Hr.  NAMESPACE       HIT_RATIO PIN_HIT_RATIO      RELOADS
---------------- --------------- --------- ------------- ------------
2001-12-20 10    BODY                 1.00          1.00            5
                 PIPE                 1.00          1.00            0
                 SQL AREA              .99           .96        2,957
                 TABLE/PROCEDURE      1.00           .91          212
                 TRIGGER              1.00          1.00            0
                 BODY                 1.00          1.00            5
                 INDEX                1.00          1.00            0

2001-12-20 11    BODY                  .99           .99            5
                 CLUSTER              1.00          1.00            1
                 INDEX                1.00          1.00            0
                 PIPE                 1.00          1.00            0
                 SQL AREA              .98           .99        2,999
                 TABLE/PROCEDURE       .99          1.00          221
                 TRIGGER              1.00          1.00            0

From this report, the DBA can track the loading of each type of object, and see the balance of the different object types inside the library cache.

Now let's look at the how to pin PL/SQL packages into the library cache.

Pinning Packages in the SGA

It has long been known that placing SQL inside stored procedures has numerous advantages over external SQL. By placing all SQL in packages, system consistency is easy to maintain, all SQL resides inside the data dictionary, and, best of all, the packages can be pinned into the library cache.

As more shops begin encapsulating their SQL into stored procedures, more application code will move away from external programs and into the database engine. Application vendors are delivering their PL/SQL in packages, and more developers are encapsulating their SQL into stored procedures. This has a benefit of having a complete application stored inside the data dictionary.

When a request is made to Oracle to parse an SQL statement or PL/SQL block, Oracle will first check the internal memory structures to see if the parsed object is already in the library cache buffer. In this fashion, Oracle avoids doing unnecessary reparsing of SQL statements. In an ideal world, it would be wonderful if we could allocate memory to hold all SQL, thereby ensuring that Oracle would never reparse a statement.

Library cache objects are paged-out based on a least recently used (LRU) algorithm. Once loaded into the RAM memory of the shared pool, stored procedures will execute very quickly, and even though the stored procedure will move to the head of the list each time it is reexecuted, there is still the potential that the stored procedure could age-out of the library cache and need to be reloaded.

To prevent reparsing of SQL inside packages, you can mark packages as nonswappable, telling the database that after their initial load they must always remain in memory. This is called “pinning” or “memory fencing.” Oracle provides the procedure dbms_shared_pool.keep for pinning a package. You can unpin packages by using dbms_shared_pool.unkeep.

NOTE: Packages can only be pinned after the instance is started, and they must be repinned each time the database is started. Most DBAs write a script to pin their packages immediately after startup time.

The choice of whether to pin a procedure in memory is a function of the size of the object and the frequency with which it is used. Very large procedures that are called frequently might benefit from pinning, but you might never notice any difference in that case because the frequent calls to the procedure will have kept it loaded into memory anyway.

In an ideal world, the Oracle shared_pool parameter would be large enough to accept every package, stored procedure, and trigger your applications might invoke. Reality, however, dictates that the shared pool cannot grow indefinitely, and you need to make wise choices regarding which objects you pin.

Some Oracle DBAs actively work to encapsulate SQL into stored procedures and the stored procedures into packages. In this fashion, many DBAs identify high-impact procedures and group them into a single package, which is pinned in the library cache.

Because of their frequent usage, Oracle recommends that the standard, dbms_standard, dbms_utility, dbms_describe, and dbms_output packages always be pinned in the shared pool. The following snippet demonstrates how a stored procedure called sys.standard can be pinned:

Svrmgrl> connect internal;

@/$ORACLE_HOME/rdbms/admin/dbmspool.sql

EXECUTE dbms_shared_pool.keep('sys.standard');

A standard procedure can be written to pin all of the recommended Oracle packages into the shared pool. Here is a sample of such a script:

pin.sql

EXECUTE dbms_shared_pool.keep('DBMS_ALERT');
EXECUTE dbms_shared_pool.keep('DBMS_DDL');
EXECUTE dbms_shared_pool.keep('DBMS_DESCRIBE');
EXECUTE dbms_shared_pool.keep('DBMS_LOCK');
EXECUTE dbms_shared_pool.keep('DBMS_OUTPUT');
EXECUTE dbms_shared_pool.keep('DBMS_PIPE');
EXECUTE dbms_shared_pool.keep('DBMS_SESSION');
EXECUTE dbms_shared_pool.keep('DBMS_SHARED_POOL');
EXECUTE dbms_shared_pool.keep('DBMS_STANDARD');
EXECUTE dbms_shared_pool.keep('DBMS_UTILITY');
EXECUTE dbms_shared_pool.keep('STANDARD');

Oracle Corporation recommends that you always pin the same packages in the shared pool. For Oracle applications, there is a list of several hundred packages, and the DBA must ensure that these get pinned each time the database starts.

Automated Repinning of Packages

UNIX users might want to add a script to their database startup procedure to ensure that the packages are repinned after each database startup, thereby guaranteeing that all packages are repinned with each bounce of the box. A pinning script might look like this:

root> more pin_packs.ksh
ORACLE_SID=prodedi
export ORACLE_SID
su oracle -c "/usr/oracle/bin/svrmgrl /<<!
connect internal;
@pin.sql
exit;
!"

Now let's take a look at how we monitor packages inside the library cache and identify candidates for pinning.

Monitoring Packages for Pinning

The following script shows how to look at all packages in the SGA. The output from this listing should show those packages that are frequently used by your application.

memory.sql

memory.sql - Display used SGA memory for triggers, packages, & procedures

SET PAGESIZE 60;

COLUMN EXECUTIONS FORMAT 999,999,999;
COLUMN Mem_used   FORMAT 999,999,999;

SELECT SUBSTR(owner,1,10) Owner,
       SUBSTR(type,1,12)  Type,
       SUBSTR(name,1,20)  Name,
       executions,
       sharable_mem       Mem_used,
       SUBSTR(kept||' ',1,4)   "Kept?"
 FROM v$db_object_cache
 WHERE TYPE IN ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
 ORDER BY EXECUTIONS DESC;

The next output shows the output of memory.sql. Here we see that the packages are ordered by the number of executions (in descending order). In this example, we see that dbms_alert is a frequently referenced package and should be added to the pinning script.

SQL> @memory

OWNER   TYPE          NAME               EXECUTIONS   MEM_USED    KEPT
----    ------        ------             ----------   --------   -----
SYS     PACKAGE       STANDARD              867,600   151,963      YES
SYS     PACKAGE BODY  STANDARD              867,275    30,739      YES
SYS     PACKAGE       DBMS_ALERT            502,126     3,637       NO
SYS     PACKAGE BODY  DBMS_ALERT            433,607    20,389       NO
SYS     PACKAGE       DBMS_LOCK             432,137     3,140      YES
SYS     PACKAGE BODY  DBMS_LOCK             432,137    10,780      YES
SYS     PACKAGE       DBMS_PIPE             397,466     3,412       NO
SYS     PACKAGE BODY  DBMS_PIPE             397,466     5,292       NO
HRIS    PACKAGE       S333_PACK             285,700     3,776       NO

Now that we know how to monitor the library cache, lets look at another important component of the shared pool, the data dictionary 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