||Oracle Tips by Burleson
Chapter 4 Oracle Instance
Pool Size Est Est LC Saved
Size(M) Factor LC(M) Mem. Obj. (sec) Factor
---------- ---------- ---------- ---------- ---------- ----------
48 .5 48 20839 1459645 1
64 .6667 63 28140 1459645 1
80 .8333 78 35447 1459645 1
96 1 93 43028 1459645 1
112 1.1667 100 46755 1459646 1
128 1.3333 100 46755 1459646 1
144 1.5 100 46755 1459646 1
160 1.6667 100 46755 1459646 1
176 1.8333 100 46755 1459646 1
192 2 100 46755 1459646 1
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
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,
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.
NAME BLOCK_SIZE CACHE_HIT_RATIO
DEFAULT 32,767 .97
RECYCLE 16,384 .61
KEEP 16,384 1.00
DEFAULT 16,384 .92
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
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
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
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
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
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
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.
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
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
SEE THE BOOK FOR FULL SCRIPT
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
---------------- ------------ ----------- ------------
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.
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
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
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
1 - ((a.value - (b.value))/d.value) "Cache Hit Ratio"
SEE THE BOOK FOR FULL SCRIPT
b.name='physical reads direct'
d.name='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.
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
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.
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
-- Display BHR for Oracle8
-- Copyright (c) 2014 By Donald K. Burleson -
All Rights reserved.
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
a.value + b.value "logical_reads",
round(100 * (((a.value-e.value)+(b.value-f.value))-(c.value-g.value))
"BUFFER HIT RATIO"
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 mydate heading 'yr. mo dy Hr.'
to_char(snap_time,'yyyy-mm-dd HH24') mydate,
(new.db_block_gets-old.db_block_gets)) < .90
new.name = old.name
new.snap_id = sn.snap_id
old.snap_id = sn.snap_id-1
A sample output from this script is shown
yr. mo dy Hr
------------- -------------------- -----
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
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;
A standard procedure can be written to pin all
of the recommended Oracle packages into the shared pool. Here is the
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:
su oracle -c "/usr/oracle/bin/svrmgrl /<<!
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.
EXECUTIONS FORMAT 999,999,999;
COLUMN Mem_used FORMAT 999,999,999;
SUBSTR(kept||' ',1,4) "Kept?"
ORDER BY EXECUTIONS DESC;
TYPE NAME EXECUTIONS MEM_USED KEPT
---- ------ ------ ---------- --------
SYS PACKAGE STANDARD 867,600 151,963
SYS PACKAGE BODY STANDARD 867,275 30,739
SYS PACKAGE DBMS_ALERT 502,126 3,637
SYS PACKAGE BODY DBMS_ALERT 433,607 20,389
SYS PACKAGE DBMS_LOCK 432,137 3,140
SYS PACKAGE BODY DBMS_LOCK 432,137 10,780
SYS PACKAGE DBMS_PIPE 397,466 3,412
SYS PACKAGE BODY DBMS_PIPE 397,466 5,292
HRIS PACKAGE S125_PACKAGE 285,700 3,776
SYS PACKAGE DBMS_UTILITY 284,694 3,311
SYS PACKAGE BODY DBMS_UTILITY 284,694 6,159
HRIS PACKAGE HRS_COMMON_PACKAGE 258,657 3,382
HRIS PACKAGE BODY S125_PACKAGE 248,857 30,928
HRIS PACKAGE BODY HRS_COMMON_PACKAGE 242,155 8,638
HRIS PACKAGE GTS_SNAPSHOT_UTILITY 168,978 11,056
HRIS PACKAGE BODY GTS_SNAPSHOT_UTILITY 89,623 3,232
SYS PACKAGE DBMS_STANDARD 18,953 14,696
SYS PACKAGE BODY DBMS_STANDARD 18,872 3,432
KIS PROCEDURE RKA_INSERT 7,067 4,949
HRIS PACKAGE HRS_PACKAGE 5,175 3,831
HRIS PACKAGE BODY HRS_PACKAGE 5,157 36,455
SYS PACKAGE DBMS_DESCRIBE 718 12,800
HRIS PROCEDURE CHECK_APP_ALERT 683 3,763
SYS PACKAGE BODY DBMS_DESCRIBE 350 9,880
SYS PACKAGE DBMS_SESSION 234 3,351
SYS PACKAGE BODY DBMS_SESSION 65 4,543
GIANT PROCEDURE CREATE_SESSION_RECOR 62 7,147
HRIS PROCEDURE INIT_APP_ALERT 6 10,802
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
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
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
computation done by the trigger saves time and makes for a much more
informative report, as we shall see later.
Listing 4- 1 -- shows the table definition that
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.
AFTER LOGON ON DATABASE
insert into stats$user_log values(