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

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Tips by Burleson

Chapter 4 Oracle Instance Design


shared_pool_size_factor        c2,
   estd_lc_size                  c3,
   estd_lc_memory_objects        c4,
   estd_lc_time_saved                    c5,
   estd_lc_time_saved_factor             c6,
   estd_lc_memory_object_hits            c7


                                                   Est        Est
                                                  Time      Parse
     Pool        Size        Est     Est LC      Saved      Saved          Est
   Size(M)     Factor    LC(M)    Mem. Obj.      (sec)     Factor  Object Hits
---------- ---------- ---------- ---------- ---------- ---------- ------------
        48         .5         48      20839    1459645          1  135,756,032
        64      .6667         63      28140    1459645          1  135,756,101
        80      .8333         78      35447    1459645          1  135,756,149
        96          1         93      43028    1459645          1  135,756,253
       112     1.1667        100      46755    1459646          1  135,756,842
       128     1.3333        100      46755    1459646          1  135,756,842
       144        1.5        100      46755    1459646          1  135,756,842
       160     1.6667        100      46755    1459646          1  135,756,842
       176     1.8333        100      46755    1459646          1  135,756,842
       192          2        100      46755    1459646          1  135,756,842

Here we see the statistics for the shared pool in a range from 50% of the current size to 200% of the current size.  These statistics can give you a great idea about the proper size for the shared_pool_size.  If you are automatic the SGA region sizes with automated “alter system” commands, creating this output and writing a program to interpret the results is a great way to ensure that the shared pool and library cache always have enough RAM.

Next let’s examine the most important SGA component, the internal data buffers.

Designing the data buffers

The design of the Oracle data buffers is an important aspect of Oracle tuning because the data buffer has a direct impact on disk I/O. Oracle provides an in-memory cache for data blocks that have been retrieved by earlier SQL requests. When a request is made to Oracle to retrieve data, Oracle will first check the internal memory structures to see if the data is already in the buffer. If the block is in memory, Oracle reads the data from the RAM buffer and avoids doing unnecessary disk I/O.

Before you can fully complete the design you must implement the system on a best-guess basis and develop a mechanism to monitor the data buffer hit ratio for all pools you have defined.  You can monitor all 7 data buffers with this script:

-- ****************************************************************
-- Display avg. BHR since database startup time
-- Copyright (c) 2003 By Donald K. Burleson - All Rights reserved.
-- **************************************************************** 


(1-(physical_reads/ decode(db_block_gets+consistent_gets, 0, .001, db_block_gets+consistent_gets)))*100   cache_hit_ratio

Here, we see the output from this script.  Note that the names of the sized block buffers remain DEFAULT, and you must select the block_size column to differentiate between the buffers.  Here we see al 7 data buffers.

-------- ---------- ---------------                                
DEFAULT      32,767             .97
RECYCLE      16,384             .61
KEEP         16,384            1.00
DEFAULT      16,384             .92
DEFAULT       4,096             .99
DEFAULT       8,192             .98
DEFAULT       2,048             .86       

Of course, this report is not very useful because the v$sysstat view only shows averages since the instance was started. To perform self-tuning of the data buffers, we can use Oracle’s STATSPACK utility to measure the data buffer hit ratios every hour.

It would be ideal if you could create one buffer for each database page, ensuring that Oracle would read each block only once. With Oracle8i and the very large memory (VLM) features, it's now possible to specify a data buffer that's large enough to hold an entire multi-gigabyte database, but most large databases do not have enough RAM to allow for the full caching of data pages.

In Oracle8i, we have three buffer pools for holding data blocks:

  • DEFAULT pool—Used for all data blocks that are not marked for the KEEP or RECYCLE pools

  • KEEP pool—Reserved for tables and indexes that are used very frequently

  • RECYCLE pool—Reserved for data blocks that are read when performing large, full-table scans

Because most Oracle databases do not have enough RAM to cache the whole database, the data buffers manage the data blocks to reduce disk I/O. Oracle utilizes a least recently used algorithm to determine which database pages are to be flushed from memory.

As I mentioned earlier, the measure of the effectiveness of the data buffer is called the data buffer hit ratio. This ratio computes the likelihood that a data block is present in the data buffer when the block is requested. The more data blocks that are found in the buffer, the higher the data buffer hit ratio. Oracle recommends that all databases exhibit a data buffer hit ratio of at least 90 percent.

It's important to note that the data buffer hit ratio is an elapsed-time measurement. If you use the Oracle STATSPACK utility to compute the data buffer hit ratio over short intervals (every five minutes), you will see that the buffer hit ratio varies from 50 percent to 100 percent, depending upon the type of SQL requests that are being processed.

Many Oracle shops will keep their buffer hit ratio information in the STATSPACK tables and plot it to show trends in the effectiveness of the data buffer to reduce I/O. Figure 4.1 shows an example of a plot of STATSPACK data for the data buffer hit ratio.

Figure 4.1 – A sample listing from a STATSPACK BHR report

The predictive models for Oracle RAM areas began with the v$db_cache_advice utility in Oracle9i.  The new v$db_cache_advice  view is similar to an Oracle7 utility that also predicted the benefit of adding data buffers.  The Oracle7 utility used the x$kcbrbh view to track buffer hits and the x$kcbcbh view to track buffer misses.

Oracle9i r2 now has three predictive utilities

  • PGA advice - Oracle9i has introduced a new advisory utility dubbed v$pga_target_advice.  This utility will show the marginal changes in optimal, one-pass, and multipass PGA execution for different sizes of pga_aggregate_target, ranging from 10% to 200% of the current value.

  • Shared Pool advice - This advisory functionality has been extended in Oracle9i release 2 to include a new advice called v$shared_pool_advice, and there is talk to expending the advice facility to all SGA RAM areas in future releases of Oracle.

  • Data Cache advice - The v$db_cache_advice utility show the marginal changes in physical data block reads for different sizes of db_cache_size. Bear in mind that the data from STATSPACK can provide similar data as v$db_cache_advice, and most Oracle tuning professionals use STATSPACK and v$db_cache_advice to monitor the effectiveness of their data buffers.

These advisory utilities are extremely important for the Oracle DBA who must adjust the sizes of the RAM areas to meet current processing demands.

Using v$db_cache_advice

The following query can be used to perform the cache advice function, once the db_cache_advice has been enabled and the database has run long enough to give representative results.

-- ***********************************************************
-- Display cache advice
-- ***********************************************************

column c1   heading 'Cache Size (meg)'      format 999,999,999,999
column c2   heading 'Buffers'               format 999,999,999
column c3   heading 'Estd Phys|Read Factor' format 999.90
column c4   heading 'Estd Phys| Reads'      format 999,999,999

   size_for_estimate          c1,
   buffers_for_estimate       c2,
   estd_physical_read_factor  c3,
   estd_physical_reads        c4
   block_size  = (SELECT value FROM V$PARAMETER)
   advice_status = 'ON';

The output from the script is shown below.  Note that the values range from 10 percent of the current size to double the current size of the db_cache_size.

                                Estd Phys    Estd Phys
 Cache Size (meg)     Buffers Read Factor        Reads
---------------- ------------ ----------- ------------
              30        3,802       18.70  192,317,943 <== 10% size
              60        7,604       12.83  131,949,536
              91       11,406        7.38   75,865,861
             121       15,208        4.97   51,111,658
             152       19,010        3.64   37,460,786
             182       22,812        2.50   25,668,196
             212       26,614        1.74   17,850,847
             243       30,416        1.33   13,720,149
             273       34,218        1.13   11,583,180
             304       38,020        1.00   10,282,475 Current Size
             334       41,822         .93    9,515,878
             364       45,624         .87    8,909,026
             395       49,426         .83    8,495,039
             424       53,228         .79    8,116,496
             456       57,030         .76    7,824,764
             486       60,832         .74    7,563,180
             517       64,634         .71    7,311,729
             547       68,436         .69    7,104,280
             577       72,238         .67    6,895,122
             608       76,040         .66    6,739,731 <== 2x size

From the above listing we see that increasing the db_cache_size from 304 meg to 334 meg would result in approximately 700,000 less physical reads.

These advisory utilities are very important for the Oracle9i DBA who must adjust their SGA regions to meet current processing demands.  Remember, SGA tuning is an iterative process and busy shops continually monitor and adjust the size of their data cache, PGA and shared pool.

Design with the Data Buffer Hit Ratio (DBHR)

The goal of the administrator is to keep as many of the frequently used Oracle blocks in buffer memory as possible.  The data buffer hit ratio (DBHR) measures how often requested data blocks are found in the buffer pool.  In sum, the DBHR is the ratio of logical reads to physical disk reads. As the hit ratio

approaches 100 percent, more data blocks are found in memory, resulting in fewer disk I/Os and faster overall database performance. 

On the other hand, if the data buffer hit ratio falls below 90 percent, fewer data blocks are resident in memory, requiring Oracle to perform a disk I/O to move them into the data buffer.  The formula for calculating the DBHR in Oracle8 was:

1 - (Physical Reads - Physical Reads Direct)
   (session logical reads)

It should be noted that the formula for calculating the hit ratio in Oracle7 and Oracle8 does not include direct block reads.  Direct block reads become a separate statistic in Oracle8i.

It is important to realize that the data buffer hit ratio is only one small part of Oracle tuning.  You should also use STATSPACK, interrogate system wait events, and tune your SQL for optimal execution plans.

The hit ratio for Oracle8i can be gathered from the v$ views, as shown below.  However, the value is not very useful because it shows the total buffer hit ratio since the beginning of the instance.

   1 - ((a.value - (b.value))/d.value) "Cache Hit Ratio"
   v$sysstat a,
   v$sysstat b,
   v$sysstat d
SEE THE BOOK FOR FULL SCRIPT'physical reads direct'
and'session logical reads';

Many novice DBAs make the mistake of using the DBHR from the v$ views.  The v$buffer_pool_statistics view does contain the accumulated values for data buffer pool usage, but computing the data buffer hit ratio from the v$ tables only provides the average since the database was started.

In order for the DBA to determine how well the buffer pools are performing, it is necessary to measure the hit ratio at more frequent intervals.  Calculating the DBHR for Oracle8 and beyond is more complicated than earlier versions, but the results enable the administrator to achieve a higher level of tuning than was previously possible.

In the next section, we will look at the wealth of information that STATSPACK can provide for tracking buffer pool utilization and computing the data buffer hit ratio.

Using STATSPACK for the Data Buffer Hit Ratio

STATSPACK uses the stats$buffer_pool_statistics table for monitoring buffer pool statistics. This table contains the following useful columns:

  • name - This column shows the name of the data buffer (KEEP, RECYCLE, or DEFAULT).

  • free_buffer_wait - This is a count of the number of waits on free buffers.

  • buffer_busy_wait - This is the number of times a requested block was in the data buffer but unavailable because of a conflict.

  • db_block_gets - This is the number of database block gets, which are either logical or physical.

  • consistent_gets - This is the number of logical reads.

  • physical_reads - This is the number of disk block fetch requests issued by Oracle. (Remember, this is not always a “real” read because of disk array caching.)

  • physical_writes - This is the number of physical disk write requests from Oracle. If you have a disk array, the actual writes are performed asynchronously.

These STATSPACK columns provide information that can be used to measure several important metrics, including the most important, the data buffer hit ratio.

Data Buffer Monitoring with STATSPACK

There are two ways to use STATSPACK to compute the data buffer hit ratio. In Oracle8i and beyond, we may use the stats$buffer_pool_statistics table. For Oracle 8.0, the stats$sesstat table should be used.

NOTE: There is an important difference between stats$buffer_pool_statistics   in Oracle 8.0 and Oracle8i.  If STATSPACK was back-ported into Oracle 8.0, the stats$buffer_pool_statistics view does not give accurate data buffer hit ratios for the DEFAULT, KEEP, and RECYCLE pools.  Instead, there is only one pool defined as FAKE VIEW.  This uses the stats$sysstat table and should be used for Oracle 8.0

-- ****************************************************************|
-- Display BHR for Oracle8
-- Copyright (c) 2014 By Donald K. Burleson - All Rights reserved.
-- ****************************************************************

set pages 9999;

column logical_reads  format 999,999,999
column phys_reads     format 999,999,999
column phys_writes    format 999,999,999
column "BUFFER HIT RATIO" format 999

   to_char(snap_time,'yyyy-mm-dd HH24'),
   a.value + b.value  "logical_reads",
   c.value            "phys_reads",
   d.value            "phys_writes",
   round(100 * (((a.value-e.value)+(b.value-f.value))-(c.value-g.value)) /
   perfstat.stats$sysstat a,
   perfstat.stats$sysstat b,
   perfstat.stats$sysstat c,
   perfstat.stats$sysstat d,
   perfstat.stats$sysstat e,
   perfstat.stats$sysstat f,
   perfstat.stats$sysstat g,
   perfstat.stats$snapshot   sn

  b.snap_id = sn.snap_id
   c.snap_id = sn.snap_id
   d.snap_id = sn.snap_id
   e.snap_id = sn.snap_id-1
   f.snap_id = sn.snap_id-1
   g.snap_id = sn.snap_id-1
   a.statistic# = 39
   e.statistic# = 39
   b.statistic# = 38
   f.statistic# = 38
   c.statistic# = 40
   g.statistic# = 40
   d.statistic# = 41

The method below is used for Oracle 8.1 and beyond:

-- ****************************************************************
-- Display BHR for Oracle8i & beyond
-- Copyright (c) 2014 By Donald K. Burleson - All Rights reserved.
-- ****************************************************************

column bhr format 9.99
column mydate heading 'yr.  mo dy Hr.'

   to_char(snap_time,'yyyy-mm-dd HH24')      mydate,                                  buffer_pool_name,
   (new.db_block_gets-old.db_block_gets))    bhr
   perfstat.stats$buffer_pool_statistics old,
   perfstat.stats$buffer_pool_statistics new,
   perfstat.stats$snapshot               sn

   (new.db_block_gets-old.db_block_gets)) < .90
and =
   new.snap_id = sn.snap_id
   old.snap_id = sn.snap_id-1

A sample output from this script is shown below:

yr.  mo dy Hr BUFFER_POOL_NAME       BHR
------------- -------------------- -----
2001-12-12 15 DEFAULT                .92
2001-12-12 15 KEEP                   .99
2001-12-12 15 RECYCLE                .75
2001-12-12 16 DEFAULT                .94
2001-12-12 16 KEEP                   .99
2001-12-12 16 RECYCLE                .65

This script provides us with the data buffer hit ratio for each of the buffer pools at one-hour intervals.  It is important that the KEEP pool always has a 99-100 percent DBHR.  If this is not the case, data blocks should be added to the KEEP pool to make it the same size as the sum of all object data blocks that are assigned to the KEEP pool.

To summarize, the DBA can control the data buffer hit ratio by adding blocks within the Oracle parameters.  Oracle recommends that the DBHR not fall below 90 percent.

Pinning Packages in The SGA

To prevent paging, packages can be marked as non-swappable. Marking a package as non-swappable tells a database that after the package is initially loaded, the package must always remain in memory. This is called pinning or memory fencing. Oracle provides a procedure called dbms_shared_pool.keep to pin a package. Packages can be unpinned with dbms_shared_pool.unkeep.

Note: Only packages can be pinned. Stored procedures cannot be pinned unless they are placed into a package.

The choice of whether to pin a package in memory is a function of the size of the object and the frequency of its use. Very large packages that are called frequently might benefit from pinning, but any difference might go unnoticed because the frequent calls to the procedure have kept it loaded into memory anyway. Therefore, because the object never pages out in the first place, pinning has no effect. Also, the way procedures are grouped into packages can have some influence. Some Oracle DBAs identify high-impact procedures and group them into a single package, which is pinned in the library cache.

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 can be used by the applications. However, reality dictates that the shared pool cannot grow indefinitely, and wise choices must be made in terms of which packages are pinned.

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:

Connect system/manager as sysdba;



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');

Automatic Re-pinning Of Packages

Unix users might want to add code to the /etc/rc file to ensure that the packages are re-pinned after each database startup, guaranteeing that all packages are re-pinned with each bounce of the box. A script might look like this:

[root]: more pin
su oracle -c "/usr/oracle/bin/svrmgrl /<<!
connect internal;
select * from db;

The database administrator also needs to remember to run the pin.sql script whenever restarting a database. This is done by reissuing the PIN command from inside SQL*DBA immediately after the database has been restarted.

The listing below shows a handy script to look at pinned packages in the SGA.  The output from this listing should show those packages that are frequently used by your application.


COLUMN Mem_used   FORMAT 999,999,999;

SELECT SUBSTR(owner,1,10) Owner,
       SUBSTR(type,1,12)  Type,
       SUBSTR(name,1,20)  Name,
       sharable_mem       Mem_used,
       SUBSTR(kept||' ',1,4)   "Kept?"
 FROM v$db_object_cache



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       S125_PACKAGE          285,700     3,776       NO
SYS     PACKAGE       DBMS_UTILITY          284,694     3,311       NO
SYS     PACKAGE BODY  DBMS_UTILITY          284,694     6,159       NO
HRIS    PACKAGE       HRS_COMMON_PACKAGE    258,657      3,382      NO
HRIS    PACKAGE BODY  S125_PACKAGE          248,857    30,928       NO
HRIS    PACKAGE BODY  HRS_COMMON_PACKAGE    242,155     8,638       NO
HRIS    PACKAGE       GTS_SNAPSHOT_UTILITY  168,978    11,056       NO
HRIS    PACKAGE BODY  GTS_SNAPSHOT_UTILITY   89,623     3,232       NO
SYS     PACKAGE       DBMS_STANDARD          18,953    14,696       NO
SYS     PACKAGE BODY  DBMS_STANDARD          18,872     3,432       NO
KIS     PROCEDURE     RKA_INSERT              7,067     4,949       NO
HRIS    PACKAGE       HRS_PACKAGE             5,175     3,831       NO
HRIS    PACKAGE BODY  HRS_PACKAGE             5,157    36,455       NO
SYS     PACKAGE       DBMS_DESCRIBE             718    12,800       NO
HRIS    PROCEDURE     CHECK_APP_ALERT           683     3,763       NO
SYS     PACKAGE BODY  DBMS_DESCRIBE             350     9,880       NO
SYS     PACKAGE       DBMS_SESSION              234     3,351       NO
SYS     PACKAGE BODY  DBMS_SESSION               65     4,543       NO
GIANT   PROCEDURE     CREATE_SESSION_RECOR       62     7,147       NO
HRIS    PROCEDURE     INIT_APP_ALERT              6    10,802       NO

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

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.

Designing Logon Triggers to track user activity

Starting with Oracle8i, Oracle introduced special triggers that are not associated with DML events (e.g. INSERT, UPDATE, and DELETE).   These system level triggers included database startup triggers, DDL triggers and end-user log in / log off triggers. 

While Oracle provided the functionality for these new triggers, it was not clear how they could be used in order to track system-wide usage. This article describes our work in creating end-user log in / log off procedures to facilitate tracing end-user activity.  Please be advised that the initial implementation of system level triggers for end-user tracking is quite new, and as such is still a bit lacking in robust functionality.

This pre computation done by the trigger saves time and makes for a much more informative report, as we shall see later.

create table
   user_id           varchar2(30),
   session_id           number(8),
   host              varchar2(30),
   last_program      varchar2(48),
   last_action       varchar2(32),
   last_module       varchar2(32),
   logon_day                 date,
   logon_time        varchar2(10),
   logoff_day                date,
   logoff_time       varchar2(10),
   elapsed_minutes       number(8)

Listing 4- 1 -- shows the table definition that we used.

Designing a log on trigger

Once the table is designed, the next issue was to create a system level log on trigger that would fill in as much information as possible at the time of the log on event.  Listing 4- 2 below illustrates the log on audit trigger that we created.  As you can see, we populate this table with three values that are available at log on time: 

  • User -- this is the Oracle user ID of the person establishing the Oracle session.

  • Session ID -- this uses Oracle's SYS context function in order to capture the Oracle session ID directly from the v$session table.

  • Host -- this uses Oracle’s SYS context function to capture the name of the host from which the Oracle session originated.  Please note that capturing the host name is vital for systems using Oracle parallel server or real application clusters, because we can have many sessions connecting from many different instance hosts.

  • Log on date -- this captures the date of the actual work log on, accurate to 1/1000 of a second. Notice how we partitioned log on date into two separate fields. Having a separate field for log on day and log on time produces a reader friendly report. 

create or replace trigger
insert into stats$user_log values(
   to_char(sysdate, 'hh24:mi:ss'),




Oracle performance tuning book



Oracle performance tuning software

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


email BC:

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

Oracle® is the registered trademark of Oracle Corporation.