Author: Burleson

Oracle - Performance Goals:

In my previous article Oracle: Achieving performance goal - Part I I discussed as objects such as stored procedures and triggers become more popular, more application code will move away from external programs and into the database engine. The Oracle DBA must be conscious of the increasing memory demands of stored procedures, and carefully plan for the days when all of the database access code resides within the database. So here is the continuation of the previous article.

Paging:

The shared pool utilizes a "least recently used" algorithm to determine which objects get paged-out of the shared pool. As this paging occurs, discontiguous chunks of memory are created within the shared pool.

Imagine the SHARED_POOL being similar to a tablespace. While you may get ora-1547 when you cannot get sufficient contiguous free space in the tablespace, similarly, you will get ora 4031 when you cannot get contiguous free space in the SHARED_POOL(sga).

This means that a large procedure that initially fit into memory, may not fit into contiguous memory when it is reloaded after paging-out. Consider a problem that occurs when the body of a package has been paged out of the instance's SGA because of other more recent/frequent activity. Fragmentation then occurs, and the server cannot find enough contiguous memory to re-load the package body, resulting in an ORA-4031 error.

Prevention of Paging by Pinning/Memory Fencing:

To prevent paging, packages can be marked as non-swappable, telling the database that after their initial load, they must always remain in memory. This is called "pinning," or "memory fencing." Oracle provides a procedure dbms_shared_pool.keep to pin a package. Packages can be unpinned with dbms_shared_pool.unkeep. note that only packages can be pinned. Stored procedures should be placed into a package if they are to be pinned.

Pinning the Procedure – Yes or No?

The choice of whether to "pin" a procedure in memory is a function of the size of the object and the frequency that it is used. Very large procedures that are called frequently might benefit from pinning, but you might never notice any difference because the frequent calls to the procedure have kept it loaded into memory. Therefore, since the object never pages-out, the pinning has no effect.

Pinning the Objects:

In an ideal world, the shared_pool parameter of the init.ora should be large enough to accept every package, stored procedure and trigger that may be invoked by the applications. Reality, however, dictates that the shared pool cannot grow indefinitely, and wise choices must be made regarding which objects are fenced.

Oracle recommends that the STANDARD, DBMS_STANDARD, DBMS_UTILITY, DBMS_DESCRIBE and DBMS_OUTPUT packages always be pinned in the shared pool.
connect internal;
@/usr/oracle/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 the script:
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');

Repining packages after each database startup:

Unix users may want to add code to the /etc/rc file to ensure that the packages are re-pinned after each database startup. This ensures that all packages are re-pinned with each bounce of the box. A script might look like this:
[root]: more pin
  ORACLE_SID=mydata
  export ORACLE_SID
  su oracle -c "/usr/oracle/bin/sqldba mode=line /<<!
  connect internal;
  select * from db;
  @/usr/local/dba/sql/pin.sql
exit;
!"

The DBA also needs to remember to run pin.sql whenever they must bounce a database from SQL*DBA.

Pinned packages in the SGA:

Here is a handy script to look at pinned packages in the SGA:

MEMORY.SQL
  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;

OUTPUT: SQL> @memory  

Memory measurement:

There is an easy way to tell the number of times that a non-pinned stored procedure was swapped-out of memory and required a reload. To effectively measure memory, two methods are recommended.

• Regularly run the estat-bstat utility (usually located in ~/rdbms/admin/utlbstat.sql and utlestat.sql) for measuring SGA consumption over a range of time.
• Write a snapdump utility to interrogate the SGA and note any exceptional information relating to the library cache. This would include the following measurements: Data dictionary hit ratio, Library cache miss ratio, Individual hit ratios for all namespaces

Also, be aware that the relevant parameter, shared_pool_size, is used for other objects besides stored procedures. This means that one parameter fits all, and Oracle offers no method for isolating the amount of storage allocated to any subset of the shared pool.

Gathering information relating to shared_pool_size:

Here is a sample report for gathering information relating to shared_pool_size. As you can see, the data dictionary hit ratio is more than 95 percent, and the library cache miss ratio is very low. However, we see more than 125,000 reloads in the SQL area namespace and may want to increase the shared_pool_size. Always remember when running this type of report that the statistics are gathered since startup, and the numbers may be skewed. For example, for a system that has been running for six months, the data dictionary hit ratio will be a running average over six months. Of course, this is meaningless if you want to measure today's statistics.

Some DBA's will run utlbstat.sql, wait one hour, and run utlestat.sql. This produces a report that shows the statistics over the elapsed time interval.

=========================
DATA DICT HIT RATIO
=========================
(should be higher than 90 else increase shared_pool_size in init.ora)


Data Dict.      Gets Data Dict.     cache misses DATA DICT CACHE HIT RATIO
------------    ------------------       -------------------------------------------------
41,750,549      407,609                         99

=========================
LIBRARY CACHE MISS RATIO
=========================
(If > 1 then increase the shared_pool_size in init.ora)

Executions Cache misses while executing LIBRARY CACHE MISS RATIO
------------     ------------------     ------------------
22,909,643       171,127                .0075

=========================
Library Cache Section
=========================
hit ratio should be > 70, and pin ratio > 70 ...

Here is the SQL*Plus script that generated the report:

prompt
prompt
prompt         =========================
prompt         DATA DICT HIT RATIO
prompt         =========================
prompt (should be higher than 90 else increase shared_pool_size in init.ora)
prompt

column "Data Dict. Gets"            format 999,999,999
column "Data Dict. cache misses"    format 999,999,999
select sum(gets) "Data Dict. Gets,"
       sum(getmisses) "Data Dict. cache misses,"
       trunc((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT RATIO"
from v$rowcache;

prompt
prompt         =========================
prompt         LIBRARY CACHE MISS RATIO
prompt         =========================
prompt (If > 1 then increase the shared_pool_size in init.ora)
prompt
column "LIBRARY CACHE MISS RATIO" format 99.9999
column "executions"    format 999,999,999
column "Cache misses while executing"    format 999,999,999
select sum(pins) "executions", sum(reloads) "Cache misses while executing",
    (((sum(reloads)/sum(pins)))) "LIBRARY CACHE MISS RATIO"
from v$librarycache;
prompt
prompt         =========================
prompt          Library Cache Section
prompt         =========================
prompt hit ratio should be > 70, and pin ratio > 70 ...
prompt

column "reloads" format 999,999,999
select namespace, trunc(gethitratio * 100) "Hit ratio",
trunc(pinhitratio * 100) "pin hit ratio", reloads "reloads"
from v$librarycache;

As memory becomes cheaper, 500 MB Oracle regions will not be uncommon, but until that time, the DBA must carefully consider the ramifications of pinning a package in the SGA.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Effective Tips for avoiding Oracle Hot Block Contention!!
   Great Tips on Optimizing Oracle Network Configuration!!
   The Power of Oracle External Tables
   The power of Oracle table index rebuilding!!