 |
|
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. |
|
