 |
|
Moving Toward a Self-Tuning
Oracle Database
Oracle Tips by Burleson
|
With these new dynamic SGA features in
Oracle, we are moving toward an architecture where the Oracle DBA
can monitor UNIX RAM memory usage and reconfigure the SGA and PGA
regions according to existing usage patterns.
Oracle offers a degree of self-tuning
capability with the new pga_aggregate_target parameter. By
allowing Oracle to manage RAM memory demands according to the
demands of each task, Oracle has been able to use sophisticated
algorithms to improve the speed of RAM intensive tasks such as hash
joins and large sorts.
However, the Oracle DBA is now able to
dynamically deallocate RAM memory from one area and reallocate the RAM
to another area of the SGA.
Changing RAM Configuration with UNIX Scripts
In a UNIX environment, it is very easy to
schedule a task to change the RAM memory configuration when the
processing needs change. For example, many Oracle databases operate in
OLTP mode during normal work hours, and the database services
memory-intensive batch reports at night.
As we have noted, an OLTP database should have
a large value for db_cache_size. Conversely,
memory-intensive batch tasks require additional RAM in the
pga_aggregate_target.
The UNIX scripts that follow can be used to
reconfigure the SGA between OLTP and DSS without stopping the
instance. In this example, we assume that you have an isolated Oracle
server with 8 gigabytes of RAM. We also assume that you reserve 20
percent of RAM for UNIX overhead, leaving a total of 6 gigabytes for
Oracle and Oracle connections. These scripts are for HP-UX or Solaris,
and accept the $ORACLE_SID as an argument.
The dss_config.ksh script will be run at
6:00 p.m. each evening
to reconfigure Oracle for the memory-intensive batch tasks that run
each night:
dss_config.ksh
#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
#ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2
-d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
$ORACLE_HOME/bin/sqlplus –s /nologin<<!
connect system/manager as sysdba;
alter system set db_cache_size=1500m;
alter system set shared_pool_size=500m;
alter system set pga_aggregate_target=4000m;
exit
!
The oltp_config.ksh script will be run
at 6:00 a.m. each
morning to reconfigure Oracle for the OLTP usage during the day:
oltp_config.ksh
#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
#ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2
-d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
$ORACLE_HOME/bin/sqlplus –s /nologin<<!
connect system/manager as sysdba;
alter system set db_cache_size=4000m;
alter system set shared_pool_size=500m;
alter system set pga_aggregate_target=1500m;
exit
!
Note: You can also use the dbms_job package to
schedule these types of reconfiguration events.
Now that you see a generic way to change the
Oracle configuration, it should be clear that you can develop a
mechanism to constantly monitor the processing demands on Oracle and
issue the alter system commands according to existing database
demands.
Approaches to Self-tuning Oracle Databases
Until Oracle evolves into a complete
self-tuning architecture, the Oracle DBA is responsible for adjusting
the RAM memory configuration according to the types of connections. In
general, you can use queries against the v$ structures and STATSPACK
to locate those times when Oracle connections change their processing
characteristics. There are three types of approaches to automated
tuning:
-
Normal
scheduled reconfiguration A bimodal instance that performs OLTP
and DSS during regular hours will benefit from a scheduled task to
reconfigure the SGA and PGA.
-
Trend-based
dynamic reconfiguration You can use STATSPACK to predict those
times when the processing characteristics change and use the
dbms_job package to fire ad-hoc SGA and PGA changes.
-
Dynamic
reconfiguration Just as Oracle dynamically redistributes RAM
memory for tasks within the pga_aggregate_target region, the
Oracle DBA can write scripts that steal RAM from an underutilized
area and reallocate these RAM pages to another RAM area.
Rules for Changing Memory Sizes
There are three conditions that affect the
decision to resize the Oracle RAM regions: one for the data buffer
cache, another for the shared pool, and the third for PGA memory
usage.
-
db_cache_size You
may want to add RAM to the data buffer cache when the data buffer
hit ratio falls below a predefined threshold.
-
shared_pool_size A high value for any of the library cache miss
rations may signal the need to allocate more memory to the shared
pool.
-
pga_aggregate_target When you see high values for multi-pass
executions, you may want to increase the available PGA memory.
Let’s take a close look at each of these
conditions.
Adjusting the
pga_aggregate_target Parameter
You may want to dynamically change the
pga_aggregate_target parameter when any one of the following
conditions is true:
-
Whenever the value
of the v$sysstat statistic “estimated PGA memory for one-pass”
exceeds pga_aggregate_target, you want to increase
pga_aggregate_target.
-
Whenever the value
of the v$sysstat statistic “workarea executions – multipass” is
greater than 1 percent, the database may benefit from additional RAM
memory.
-
It is possible to
over-allocate PGA memory, and you may consider reducing the value of
pga_aggregate_target whenever the value of the v$sysstat row
“workarea executions—optimal” consistently measures 100 percent.
Changing the shared_pool_size
Parameter
We all know from Oracle8 that Oracle offers
several queries for determining when the Oracle shared pool is too
small. The library cache miss ratio tells the DBA whether to add space
to the shared pool, and it represents the ratio of the sum of library
cache reloads to the sum of pins.
In general, if the library cache ratio is over
1, you should consider adding to the shared_pool_size. Library
cache misses occur during the parsing and preparation of the execution
plans for SQL statements. The compilation of an SQL statement consists
of two phases: the parse phase and the execute phase. When the time
comes to parse an SQL statement,
Oracle first checks to see if the
parsed representation of the statement already exists in the library
cache. If not, Oracle will allocate a shared SQL area within the
library cache and then parse the SQL statement. At execution time,
Oracle checks to see if a parsed representation of the SQL statement
already exists in the library cache. If not, Oracle will reparse and
execute the statement.
The following STATSPACK script will compute the
library cache miss ratio. Note that the script sums all of the values
for the individual components within the library cache and provides an
instance-wide view of the health of the library cache.
rpt_lib_miss.sql
set lines
80;
set pages 999;
column mydate heading 'Yr. Mo Dy Hr.' format a16
column c1 heading "execs" format 9,999,999
column c2 heading "Cache Misses|While Executing" format 9,999,999
column c3 heading "Library Cache|Miss Ratio" format 999.99999
break on mydate skip 2;
select
to_char(snap_time,'yyyy-mm-dd HH24') mydate,
sum(new.pins-old.pins) c1,
sum(new.reloads-old.reloads) c2,
sum(new.reloads-old.reloads)/
sum(new.pins-old.pins) library_cache_miss_ratio
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
group by
to_char(snap_time,'yyyy-mm-dd HH24')
;
Here is the output. This report can easily be customized
to alert the DBA when there are excessive executions or library cache
misses.
Cache Misses
Yr. Mo Dy Hr. execs While Executing
LIBRARY_CACHE_MISS_RATIO
---------------- ---------- --------------- ------------------------
2001-12-11 10 10,338 3 .00029
2001-12-12 10 182,477 134 .00073
2001-12-14 10 190,707 202 .00106
2001-12-16 10 2,803 11 .00392
Once this report identifies a time period where there
may be a problem, STATSPACK provides the ability to run detailed
reports to show the behavior of the objects within the library cache.
In the preceding example, you see a clear RAM shortage in the shared
pool between 10:00 a.m.
and 11:00 a.m. each day.
In this case, you could dynamically reconfigure the shared pool with
additional RAM memory from the db_cache_size during this
period.
Adjusting the Data Buffer Cache
Size
The following STATSPACK report alerts the DBA
to those times when the data buffer hit ratio falls below the preset
threshold. It is very useful for locating times when decision support
type queries are being run, since a large number of large-table
full-table scans will make the data buffer hit ratio drop. This script
also reports on all three data buffers, including the KEEP and RECYCLE
pools, and it can be customized to report on individual pools.
Remember, the KEEP pool should always have enough data blocks to cache
all table rows, while the RECYCLE pool should get a very low buffer
hit ratio, since it seldom re-reads data blocks. If the data buffer
hit ratio is less than 90 percent, you may want to increase
db_cache_size (db_block_buffers in Oracle8i and
earlier).
***********************************************************
When the data buffer hit ratio falls below 90%, you
should consider adding to the db_cache_size parameter
***********************************************************
yr. mo dy Hr. Name bhr
------------- -------- -----
2001-01-27 09 DEFAULT 45
2001-01-28 09 RECYCLE 41
2001-01-29 10 DEFAULT 36
2001-01-30 09 DEFAULT 28
2001-02-02 10 DEFAULT 83
2001-02-02 09 RECYCLE 81
2001-02-03 10 DEFAULT 69
2001-02-03 09 DEFAULT 69
Here you will note those times when you might want to
dynamically increase the value of the db_cache_size parameter.
In the case of the preceding output, you could increase the
db_cache_size each day between 8:00
a.m. and 10:00
a.m., stealing RAM
memory from pga_aggregate_target.
Using the Oracle
v$db_cache_advice View
Starting in Oracle 9i, there is a new view that
can predict the benefit of additional data buffers in the data buffer
cache. This view shows the estimated miss rates for 20 potential
buffer cache sizes, ranging from 10 percent of the current size to 200
percent of the current size.
This new feature is very similar to the Oracle7
utility to predict the benefit from adding additional data buffers.
This utility used a view called x$kcbrbh to track buffer
hits and the x$kcbcbh to track buffer misses.
Just like the Oracle7 model, you must
preallocate the RAM memory for the data buffers in order to use this
Oracle utility. The cache advice feature is enabled by setting the
Oracle parameter db_cache_advice to the values of ON or READY.
These values can be set dynamically with the alter system
command, so the DBA can turn on the predictive model while the
database is running.
Caution: When the DBA sets dba_cache_advice=on,
Oracle will steal RAM pages from the shared pool, often with
disastrous result to the library cache. For example, if the existing
setting for db_cache_size is 500m, Oracle will steal a
significant amount of RAM from the shared pool. To avoid this problem,
the DBA should set db_cache_advice=ready in the Oracle file.
When this is done, Oracle will preallocate the RAM memory at database
startup time.
Once the db_cache_advice is enabled and
the database has run for a representative time period, the following
query can be run to perform the prediction:
column
size_for_estimate
format 999,999,999,999
heading 'Cache Size (m)'
column buffers_for_estimate
format 999,999,999
heading 'Buffers'
column estd_physical_read_factor
format 999.90
heading 'Estd Phys|Read Factor'
column estd_physical_reads
format 999,999,999
heading 'Estd Phys| Reads'
select
size_for_estimate,
buffers_for_estimate,
estd_physical_read_factor,
estd_physical_reads
from
v$db_cache_advice
where
name = 'DEFAULT'
and
block_size = (SELECT value FROM V$PARAMETER
WHERE name = 'db_block_size')
and
advice_status = 'ON';
Here is the output from this script. Note that
the range of values is from 10 percent of the current size up to
double the current size.
Estd Phys Estd Phys
Cache Size (MB) 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
Here, you can see no peak in total disk I/O and
no marginal trends with the addition of more RAM buffers. This is very
typical of data warehouse databases that read large tables with
full-table scans. Consequently, there is no specific “optimal” setting
for the db_cache_size parameter. In other words, Oracle has an
insatiable appetite for data buffer RAM, and the more you give to
db_cache_size, the less disk I/O will occur.
As a general
rule, all available memory on the host should be tuned, and Oracle
should be given to db_cache_size up to a point of
diminishing returns (see Figure 9-23). There is a point where the
addition of buffer blocks will not significantly improve the buffer
hit ratio, and these tools give the Oracle DBA the ability to find the
optimal amount of buffers.
Figure 9-77: Determining the optimal
db_cache_size
The general rule for adding blocks to
db_cache_size is simple: as long as marginal gains can be achieved
from adding buffers and you have the memory to spare, you should
increase the value of db_cache_size. Increases in buffer blocks
increase the amount of required RAM memory for the database, and it is
not always possible to “hog” all of the memory on a processor for the
database management system. Therefore, a DBA should carefully review
the amount of available memory and determine the optimal amount of
buffer blocks.
Tip: Since you must preallocate the additional RAM
data buffers for the db_cache_size to use db_cache_advice,
you may only want to use this utility once to determine an optimal
size. Remember, you can also use the data buffer cache hit ratio to
gather similar data.
For more sophisticated Oracle databases, you
can control not only the number of buffer blocks, but also the block
size for each data buffer. For example, you might want to make some of
the buffer blocks very large so that you can minimize I/O contention.
Remember, the cost for an I/O for a 32KB block is not significantly
more expensive than an I/O for a 4KB block. A database designer might
choose to make specific data buffers large to minimize I/O if the
application “clusters” records on a database block, while keeping
other data blocks small.
When to Trigger a Dynamic Reconfiguration
When your scripts detect a condition where a
RAM memory region is overstressed, you are faced with a choice about
which region will shrink to provide the RAM for the overstressed area.
Table 9-3 provides the threshold condition for triggering a dynamic
memory change.
|
RAM Area |
Overstressed Condition |
Overallocated Condition |
|
Shared pool |
Library cache misses |
No misses |
|
Data buffer cache |
Hit ratio < 90% |
Hit ratio > 95% |
|
PGA aggregate |
High multi-pass executions |
100% optimal
executions |
Table 9-5: Threshold Conditions for Dynamic RAM
Reallocation
In practice,
the choice of which area to reduce in size is a choice between the
shared pool and the PGA aggregate memory (see Figure 9-24). This is
because the shared pool is almost always a small region when compared
to the regions for the data buffers and PGA session memory.
Now that we understand Oracle dynamic tuning,
let’s conclude this chapter with a review of the major topics and
points.
|
|
 |
|
Get the Complete
Oracle Tuning Details
The landmark book
"Oracle
Tuning: The Definitive Reference Second Edition" has been
updated with over 1,150 pages of expert performance tuning tips.
It's packed with scripts and tools to hypercharge Oracle 11g
performance and you can
buy it for 40% off directly from the publisher.
|
|
|
|
