Deploying， Managing， and Administering the Oracle Internet Platform
Paper #224/ Page 1
DBA PERFORMANCE TUNING FOR THE EXPERT ONLY：
BEGINNERS WILL BE SMOKED！
Richard J. Niemiec， TUSC
ABSTRACT
Version8 of the Oracle database has brought on a whole new level of issues for the DBA. While the queries for
tuning the database and individual queries has not changed much， the data retrieved by these queries has changed and
must be analyzed for partitioned tables and other cost-based optimizer functions. This paper will serve to give you
the individual queries to be successful.
WHAT WILL BE COVERED （GOALS FOR TUNING）
Goal#1： Have enough memory allocated to Oracle - The first goal should be to get enough memory （from your
physical hardware） allocated to “key” Oracle parameters. We will look at how to see what the current settings of a
given system are set to and also look at the “key” parameters： DB_BLOCK_BUFFERS， SHARED_POOL_SIZE，
and SORT_AREA_SIZE.
Goal#2： Get the data loaded into memory - Once you have enough memory allocated to Oracle， the focus must shift
to ensuring that the most important information is getting into memory and staying there. We will look at using x$bh
and using the ‘cache’ parameter of ‘alter table……’ to investigate this area.
Goal#3： Find queries that are clogging memory and causing I/O - Finding problem areas is， at times， the most
difficult problem. We will investigate a method for easily identifying the bottlenecks by using v$sqlarea.
Goal#4： Tune the Problem Queries - Tuning the problem queries could easily encompass an entire training course. I
will focus on a couple of key areas： What you need to know before you tune my system， using the Parallel Query
Option and general tuning tips.
Function Based Indexes - This new feature in Oracle8.1 can be a big help.
Materialized Views - This feature in Oracle8.1 can help with large tables.
GOAL#1： HAVE ENOUGH MEMORY ALLOCATED TO ORACLE
Even if the system that you are working on has 10 Gig of memory available， this doesn‘t help much if only a small
portion of it is allocated to Oracle. We allocate memory to Oracle through the INITsid.ORA file. Some of the key
parameters are listed below. We will cover each of these parameters in the following sections. By going to
“v$parameter” or by using the either Server Manager or Oracle Enterprise Manager， we can find the parameters that
affect Oracle‘s performance.
A. FINDING THE VALUES OF ‘KEY’ INIT.ORA PARAMETERS
select name， value
from v$parameter
where name in （‘db_block_buffers’，……etc）；
NAME VALUE
-------------------------------------------------- ----------------
db_block_buffers 4000
db_block_size 4096
shared_pool_size 7000000
sort_area_size 262144 .
You can also view the init.ora parameters in Oracle‘s Enterprise Manager as shown below：
Deploying， Managing， and Administering the Oracle Internet Platform
Paper #224/ Page 2
B. LOOK AT DB_BLOCK_BUFFERS
The first parameter to look at is the INITsid.ORA parameter： DB_BLOCK_BUFFERS. This is the area of the SGA
that is used for the storage and processing of data in memory. As users request information， the information is put
into memory. If the DB_BLOCK_BUFFERS parameter is set too low， then the least recently used data will be
flushed from memory. If the data flushed is recalled with a query， it must be re-read from disk （causing I/O and
CPU resources to be used）。 If DB_BLOCK_BUFFERS is too low， users will not have enough memory to operate
efficiently. If DB_BLOCK_BUFFERS is too high， your system may begin to swap and may come to a halt.
DETERMINE IF THE DATA BLOCK BUFFERS IS SET HIGH ENOUGH
select 1-（sum（decode（name， 'physical reads'， value，0））/
（sum（decode（name， 'db block gets'， value，0）） +
（sum（decode（name， 'consistent gets'， value，0））））） * 100
"Read Hit Ratio"
from v$sysstat；
Read Hit Ratio
98.415926
Although hit ratios below 90-95% are usually a sign of poor indexing； Distortion of the hit ration numbers is possible.
See the next section for more information.
Response Time in Minutes
Buffers at
200% of
Optimum
Buffers at
Optimum
Buffers at
50% of
Optimum
Buffers at
20% of
Optimum
Buffers at
5% of
Optimum
0
100
200
300
400
Figure 1： Response Time for a Memory Intensive Report with given SGA （Buffer） settings
HIT RATIO DISTORTION
Even though the equations for finding a problems seems easy， sometimes the results are not accurate. Many third
party products also receive this misinformation， yet some go to other areas to get the correct information. Below， I
show one such case where misinformation is returned.
Deploying， Managing， and Administering the Oracle Internet Platform
Paper #224/ Page 3
There are also false hit ratio distortions. SQL*Forms can cause a false high hit ratio， rollback segments can cause a
false high hit ratio impact and indexes can have hit ratios as high as 86% when none of the blocks were cached prior
to the query executing.
C. IT IS IMPORTANT TO LOOK AT THE SHARED_POOL_SIZE FOR PROPER SIZING
With a greater amount of procedures， packages and triggers being utilized with Oracle， the SHARED_POOL_SIZE
makes up a much greater portion of the Oracle SGA. This is the memory allocated for the library and data dictionary
cache. If the SHARED_POOL_SIZE is set too low then you will not get the full advantage of your
DB_BLOCK_BUFFERS.
DETERMINE DICTIONARY CACHE MISS RATIO
select sum（gets） “Gets”， sum（getmisses） “Misses”，
（1 - （sum（getmisses） / （sum（gets） +
sum（getmisses））））*100 “HitRate”
from v$rowcache；
Gets Misses HitRate
10233 508 95.270459
This would be a good Ratio and would probably not require action in this area.
DETERMINE LIBRARY CACHE HIT RATIO
select sum（pins） Executions， sum（pinhits） “Execution Hits”，
（（sum（pinhits） / sum（pins）） * 100） phitrat，
sum（reloads） Misses，
（（sum（pins） / （sum（pins） + sum（reloads））） * 100） hitrat
from v$librarycache；
Executions Execution Hits PHITRAT Misses HITRAT
3，582 3，454 96.43 6 99.83
If the hit ratio or reloads is high， increase the shared_pool_size INIT.ora parameter.
HOW MUCH MEMORY IS LEFT FOR SHARED_POOL_SIZE
col value for 999，999，999，999 heading “Shared Pool Size”
col bytes for 999，999，999，999 heading “Free Bytes”
select to_number（v$parameter.value） value， v$sgastat.bytes，
（v$sgastat.bytes/v$parameter.value）*100 “Percent Free”
from v$sgastat， v$parameter
where v$sgastat.name = 'free memory'
and v$ parameter .name = ‘shared_pool_size；
Shared Pool Size Free Bytes Percent Free
100，000，000 82，278，960 82.27896
Deploying， Managing， and Administering the Oracle Internet Platform
Pa
