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
Paper #224/ Page 4
A BETTER QUERY
select sum（ksmchsiz） Bytes， ksmchcls Status
from x$ksmsp
group by ksmchcls；
BYTES STATUS
350，000 R-free
40 R-freea
25，056 free
2，571，948 freeabl
4，113，872 perm
1，165，504 recr
If there is free memory then there is no need to increase this parameter.
You can also view the init.ora parameters in Oracle‘s Enterprise Manager as shown below. The add/modify chart
and the result of this query are shown in the two displays below.
D. TRY TO SORT IN MEMORY INSTEAD OF IN TEMPORARY
The INIT.ora parameter SORT_AREA_SIZE will allocate memory for sorting （per user / as needed）。 This is the
area that is the space allocated in main memory for each process to perform sorts. If the sort cannot be performed in
memory， temporary segments are allocated on disk to hold intermediate runs. Increasing the value of sort_area_size
will reduce the total number of disk sorts， thus reducing disk I/O. This can cause swapping， if to little memory is left
over for other processes. Statements that will generate Temporary Segments include： Create Index， Select …… Order
By， Distinct， Group By， Union， Unindexed Joins， Some Correlated Subqueries. Since temporary segments are created to
handle sorts that cannot be handled in memory， the initial extent default for temporary segments should be at least as large as the value of
sort_area_size. This will minimize extension of the segment.
Deploying， Managing， and Administering the Oracle Internet Platform
Paper #224/ Page 5
GOAL#2： GET DATA “CACHED” 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 below：
A. TO SEE HOW FAST THE SGA GETS USING X$BH
select state， count（*）
from x$bh
group by state；
STATE COUNT（*）
--------- -----------------
0 371
1 429
In the above result：
Total DB_BLOCK_BUFFERS = 800
Total that have been used = 429
Total that have NOT been used = 371
A BETTER QUERY：
select decode（state，0， ''FREE''， 1， decode（lrba_seq，0，''AVAILABLE''，''BEING USED''），
3， ''BEING USED''， state） "BLOCK STATUS"， count（*）
from x$bh
group by decode（state，0，''FREE''，1，decode（lrba_seq，0，
''AVAILABLE''，''BEING USED''），3， ''BEING USED''， state）；
BLOCK STATUS COUNT（*）
AVAILABLE 779
BEING USED 154
FREE 167
You can also view the init.ora parameters in the Performance Manager inside Oracle‘s Enterprise Manager as shown
below：
B. USING THE ‘CACHE’ PARAMETER OF ‘ALTER TABLE……’）
If you find that “key” tables are being pushed out of memory， you may need to “pin” them into memory using the
CACHE parameter. When you use this parameter， full table scans result in being placed on the “Most recently used”
list instead of the “Least recently used” list. This keeps them in memory for future use. The following examples
investigate the syntax and uses of this command：
EXAMPLE 1 （CREATE A TABLE WITH CACHE）
CREATE TABLE TEST_TAB （COL1 NUMBER）
TABLESPACE USERS
CACHE；
Deploying， Managing， and Administering the Oracle Internet Platform
Paper #224/ Page 6
NOCACHE is the Default！
EXAMPLE 2 （ALTER A TABLE TO CACHE）
ALTER TABLE TEST_TAB
CACHE；
EXAMPLE 3 （THE CACHE HINT）
SELECT /*+ CACHE（CUST） */ ENAME， JOB
FROM CUST
WHERE TABLE_NAME = ''EMP''；
EXAMPLE 4 （THE NOCACHE HINT）
SELECT /*+ FULL（CUST） NOCACHE（CUST） */ ENAME， JOB
FROM CUST
WHERE TABLE_NAME = ''EMP''；
GOAL#3： FIND PROBLEM QUERIES “HURTING” MEMORY
A single index or a single query can bring an entire system to a near standstill. By using v$sqlarea， you can find the
problem queries on your system. Below， the example shows how to find the problem queries. I am searching for
queries where the disk reads are greater than 10，000. If your system is much larger， you may need to set this to a
higher number.
EXAMPLE 5 （FINDING THE LARGEST AMOUNT OF PHYSICAL READS BY QUERY）
select disk_reads， sql_text
from v$sqlarea
where disk_reads > 10000
order by disk_reads desc；
DISK_READS SQL_TEXT
------------------ ------------------------------------------------------------
-----
12987 select order#，columns，types from orders
where substr（orderid，1，2）=：1
11131 select custid， city from customer
where city = ‘CHICAGO’
EXAMPLE 6 （FINDING THE LARGEST AMOUNT OF LOGICAL READS BY QUERY）
select buffer_gets， sql_text
from v$sqlarea
where buffer_gets > 200000
order by buffer_gets desc；
BUFFER_GETS SQL_TEXT
------------------ ------------------------------------------------------------
-----
300219 select order#，cust_no， from orders
where division = ‘1’
Deploying， Managing， and Administering the Oracle Internet Platform
Paper #224/ Page 7
GOAL#4： TUNE THE PROBLEM QUERIES
A. WHAT YOU NEED TO KNOW BEFORE YOU TUNE YOUR SYSTEM
The first thing you need to know is the data. The volume of data and the distribution of data will affect how you
tune individual queries. You also need to have a “shopping cart" full of tuning methods to try. Multiple approaches
must be made to cover all types of queries. A single method of tuning or a single tuning product is not enough. You
also need to know where the system is slow. Many DBAs and developers spend endless hours finding problem
queries instead of asking the users of the system. Users will almost always be happy to volunteer this information.
You also need to network with other developers that work on a similar system. Sharing information at user groups is
a great way to network.
B. USING “KEY” HINTS FOR OPTIMIZATION
Eventually， you will find a query that requires specific tuning attention. When the query is found， you must take
advantage of the “hints” that Oracle offers for tuning individual queries.
FULL - Force a Full Table Scan
SELECT /*+ FULL（table_name） */ column1， column2 ……
INDEX - Force an Indexed Search
SELECT /*+ INDEX（table_name index_name1 index_name2……） */
ORDERED - Force the driving table as in FROM clause
SELECT /*+ ORDERED */ column1， column2 ……
FROM table1， table2
ALL_ROWS - Explicitly chooses the cost-based approach with a goal of best throughput.
Select /*+ ALL_ROWS */ ……
FIRST_ROWS - Explicitly chooses the cost-based approach with a goal of best response time.
Select /*+ FIRST_ROWS */ ……
？ Note： The optimizer ignores this hint in ‘delete’ and ‘update’ statements， and in select statements that contain any
of the following： set operators， group by clause ， for update， group functions and distinct operators.
C. THE DRIVING TABLE
In v7， the cost-based approach uses various factors in determining which tables should be the driving table （the table
that drives the query） in a multi-table join query. The best thing to remember is to realize that you have control over
which table will drive the query through the use of the ORDERED hint. No matter what the order is from the
optimizer， that order can be overridden by the ORDERED hint. The key is to use the ORDERED hint and vary the
order of the tables to get the correct order from a performance standpoint.
select tabA.col_1， tabB.col2
from tabA， tabB
where tabB.col2 = ‘ANL’；
select /*+ ORDERED */
tabA.col_1， tabB.col2
from tabA， tabB
where tabB.col2 = ‘ANL’；
Sometimes the optimizer “goes to lunch”：
Rule based explain plan：
186 Lines （4 hours）
Use of the “ORDERED” HINT：
7 Lines （35 sec.）
？ By using the ORDERED hint and varying the order of the tables in the FROM clause of the query， you can
effectively find out which driving table is best for your query.
D. PARALLEL QUERY
Oracle‘s parallel query option has opened up a new avenue for performance enhancements. DBAs can now spread a
CPU intensive report across many processors， taking advantage of the full speed of the box. You can also use the
Deploying， Managing， and Administering the Oracle Internet Platform
Paper #224/ Page 8
PARALLEL=TRUE with DIRECT=TRUE with SQL*Loader. On the down side， you can also take down a ten
processor box with a single query using this. The queries listed below should give you the general syntax and uses for
the PARALLEL hint.
EXAMPLE7 （USING THE PARALLEL HINT； PARALLELISM DEGREE IS 4）
SELECT /*+ FULL（CUST） PARALLEL（CUST， 4） */
ENAME， JOB
FROM CUST
WHERE TABLE_NAME = ''EMP''；
FUNCTION-BASED INDEXES （ORACLE8I）
One of the largest problems with indexes is that the indexes are often suppressed by developers. Developers using
the UPPER function can suppress an index on a column for a given query. In Oracle8i， there is now a way to combat
this problem. Function-based indexes allow you to create an index based on a function or expression. The value of
the function or expression is specified by the person creating the index and is stored in the index. Function-based
indexes can involve multiple columns， arithmetic expressions or may be a PL/SQL function or C callout. The
following example shows an example of a function based index.
Creating the Function-based Index
CREATE INDEX emp_idx ON emp （UPPER（ename））；
An index has been created on the ename column when the UPPER function is used on this column.
Query the emp table using the Function-based Index：
select ename， job， deptno
from emp
where upper（ename） = ‘ELLISON’；
The function-based index （emp_idx） can be used for the query above. For large tables where the condition retrieves a
small amount of records， the query yields substantial performance gains over a full table scan.
？ 8i Tip： Function-based indexes can lead to dramatic performance gains when used to create indexes on functions
often used on selective columns. See Chapter 13 for additional Oracle8i performance enhancements.
To comprehend the advantages of function-based indexes consider the following queries.
EXAMPLE8 （USING THE FUNCTION-BASED INDEXES）
select count（*）
from sample
where ratio（balance，limit） >.5；
Elapse time： 20.1 minutes
We create a functional index.
create index ration_idx on sample （ ratio（balance， limit））；
We re-run the query using the function-based index.
select count（*）
from sample
where ratio（balance，limit） >.5；
Elapse time： 7 seconds！！！
Note that the function RATIO simply divides argument 1 by argument 2.
ORACLE8I： MATERIALIZED VIEWS AND QUERY REWRITE
The combination of Materialized Views and Query Rewrite are power tools for the Oracle data warehouse in
Oracle8i. Materialized views can be used to create and automatically refresh summary fact tables （the central table in
a data warehouse）。 Query Rewrite allows the Oracle optimizer to modify queries against the larger detail tables that
can be completely satisfied by a smaller summary table. Oracle uses the summary table instead of going to the larger
detail table which can improve performance substantially.
Deploying， Managing， and Administering the Oracle Internet Platform
Paper #224/ Page 9
In the example below， the detail table contains a count of households at a zip code and zip+4 level. The materialized
view， ZIP， summarizes the household count at a zip code level. As the explain plans show， Oracle will access the ZIP
materialized view rather then the ZIP4_COUNT table for the following query：
EXAMPLE9 （USING MATERIALIZED VIEWS）
Create the larger ZIP4_COUNT table：
CREATE TABLE ZIP4_COUNT
AS
SELECT ZIP， ZIP4， SUM（HH_CNT） HH_CNT
FROM TEST2
GROUP BY ZIP， ZIP4；
Create the smaller ZIP materialized view：
CREATE MATERIALIZED VIEW ZIP
BUILD IMMEDIATE
ENABLE QUERY REWRITE
AS
SELECT ZIP， SUM（HH_CNT）
FROM ZIP4_COUNT
GROUP BY ZIP；
In the preceding query， we have created a materialized view called zip. This materialized view is a summary of the
ZIP4_COUNT table. We have also enabled Oracle to rewrite a query （unless overriden with a NOREWRITE hint）
that can take advantage of this view. In the following two queries， we will query the table using the NOREWRITE
and REWRITE hints.
Query the ZIP4_COUNT table disallowing rewrites of the query：
SELECT /*+ NOREWRITE */ ZIP， SUM（HH_CNT）
FROM ZIP4_COUNT
GROUP BY ZIP；
SELECT STATEMENT Optimizer=CHOOSE
TABLE ACCESS （FULL） OF ''ZIP4_COUNT''
Elapsed Time： 0.28 seconds
In the query above， we disallow Oracle''s ability to rewrite the query. Hence， the ZIP4_COUNT （the larger nonsummarized）
table is accessed.
SELECT /*+ REWRITE */ ZIP， SUM（HH_CNT）
FROM ZIP4_COUNT
GROUP BY ZIP；
SELECT STATEMENT Optimizer=CHOOSE
TABLE ACCESS （FULL） OF ''ZIP''
Elapsed Time： 0.03 seconds
In the preceding example， Oracle rewrites the query to go to the smaller ZIP materialized view which improves the
performance of query substantially.
As the example above shows， Query Rewrite can improve performance by several orders of magnitude. If your
database makes use of summary tables， building Materialized Views to take advantage of Oracle''s Query Rewrite
capability is a feature you will want to investigate when you upgrade to the Oracle8i database engine. Author''s note：
This section was added to this chapter rather than the Oracle8i chapter on the final day of edits （this is the only
chapter they would let me edit - remember Casablanca）。 My apologies for inconveniences in its placement.
The following init.ora parameters must be set to use materialized views and function-based indexes.
query_rewrite_enable = true
query_rewrite_integrity = trusted
OTHER TUNING TIPS
The FIRST_ROWS hint will generally force the use of an index where it normally would not have been used by the
Optimizer （But it definitely depends on the query）。 The ALL_ROWS hint will generally NOT use an index where it
normally would have been used by the Optimizer （But it definitely depends on the query）。 Which index the optimizer
Deploying， Managing， and Administering the Oracle Internet Platform
Paper #224/ Page 10
uses may depend on which one was created first. Although this seems unbelievable， it has been validated by a
multitude of developers and DBAs. Build the most unique index FIRST （future versions will probably correct this）！
Moving the .DLLs to the Client Machine will almost always make a client-server application faster， but it also makes
the client “fatter.” In a multiple database environment， it is important to use views to access remote tables （keeps
Oracle from moving the entire table between databases）。
TUNING USING SIMPLE MATHEMATICAL TECHNIQUES
This section （which is covered in detail in Chapter 9） discusses some simple but effective mathematical techniques
you can use to significantly improve the performance of some Oracle SQL-based systems. These techniques can
leverage the effectiveness of Oracle performance diagnostic tools and uncover hidden performance problems that can
be overlooked by other methods. It also makes it easier to make performance predictions at higher loads. This
section was provided by Joe A. Holmes. I am extremely grateful for his contribution as I believe it ties all of tuning
together.
The methodology， called Simple Mathematical Techniques， involves isolating and testing the SQL process in question
under ideal conditions， graphing the results of rows processed versus time， deriving equations using simple methods
（without regression）， predicting performance， and interpreting and applying performance patterns directly to tuning
SQL code.
SIMPLE QUADRATIC EQUATION DETERMINATION
The following is a simple three-point method for determining a quadratic best performance equation：
y = a0 + a1x + a2x2
This equation can be calculated for any query using the techniques detailed in Chapter 9 of the book so that you can
retrieve one of several possible graphs for a given query. Consider some of the graphs in the figure below and
problems that are detailed in the table which follows.
Pattern in Figure 3 Possible Problem Possible Solution
A Missing Index on a query
SELECTing values
Create an index. Fix a suppressed
index
A Over-indexed table suffering
during an INSERT
Delete some of the indexes or
index less columns （or smaller
columns） for the current indexes.
B No Problem. Don‘t touch it！
Deploying， Managing， and Administering the Oracle Internet Platform
Paper #224/ Page 11
C Missing Index on a query
SELECTing values
Create an index. Fix a suppressed
index
C Over-indexed table suffering
during an INSERT
Delete some of the indexes or
index less columns （or smaller
columns） for the current indexes.
D Doing a FULL table scan or using
the ALL_ROWS hint when you
shouldn‘t be.
Try to do an indexed search. Try
using the FIRST_ROWS hint to
force the use of indexes.
E The query was fine until some
other limitation （such as disk I/O
or memory） was encountered.
You need to find which ceiling
that you hit to cause this problem.
Increasing the SGA may solve the
problem， but this could be many
things.
PATTERN INTERPRETATION
Graphical performance patterns provide clues to underlying SQL problems and solutions. Our ultimate goal in using
these methods is to convert a steep linear or quadratic best performance line to one that is both shallow and linear by
optimizing the SQL process. This may involve experiments with indexes， TEMP tables， optimizer HINT commands，
or other methods of Oracle SQL performance tuning.
With pattern interpretation， it is important to do your own application specific SQL experiments to develop an
expertise at using these methods. The following are more specific interpretations based on my personal experience
that provide a basic idea of how to apply what is observed directly to tuning SQL code. Provided the scale is correct，
pattern interpretation will often provide a more accurate picture of what is actually happening to a process and may
support or even contradict what a diagnostic tool may tell you.
An upward sloping （concave） quadratic curve almost always indicates a problem with the process because， as more
rows are added the time to process each additional row increases. If the sloping is very small， the equation may be
more linear. However， a very slight bowing may be an indicator of something more insidious under much higher
volumes.
In rare cases a quadratic curve might appear downward sloping （convex） indicating a process where as more rows are
added the time to process each additional one decreases， i.e. economies of scale. This is desirable and may occur at a
threshold where a full table scan is more efficient than using an index.
？ Tip： If you want an Oracle symphony as great as Beethoven‘s， you must learn and know how to apply
mathematical techniques to your tuning efforts. You don‘t have to learn everything that you learned in college
calculus， simply apply the simple equations in this chapter to tie everything in this book together. Thank you Joe
Holmes for doing the math for us （detailed with examples in Chapter 9 of the book）！
NIEMIEC‘S 7 RULES OF TUNING
Rule 1： The level of tuning achieved can be directly attributable to the number of straight hours that you can work
and how much junk food is available.
Rule 2： The level of tuning achieved is tremendously increased if user input is solicited and those users are NOT of
the type that try to be politically correct （i.e. You need users that are not afraid to say that this report runs horribly！）。
Rule 3： The level of tuning achieved can be directly attributable to the security access to the system that the tuning
professional has.
Rule 4： The level of tuning achieved is severely hampered by the level of theoretical knowledge required by the tuning
professional.
Rule 5： The level of tuning achieved is severely hampered by the amount of time that a manager is present.
Rule 6： The level of tuning achieved by the number of keyboards， terminals， monitors and PC‘s that are within the
reach of the tuning professional.
Deploying， Managing， and Administering the Oracle Internet Platform
Paper #224/ Page 12
Rule 7： The usual attributes of a good tuning professional （outside of actual performance） can usually be spotted by
the person who； calculates the shortest line at McDonalds； calculates the most efficient method for getting each task
done yet still leaves at 1am； has coupons for every pizza place that stays open 24 hours at their desk； tends to use
twice as much coffee grounds when making the coffee or uses caffeine enhanced water when making the coffee； asks
if you would like to go to lunch when it is time for dinner； answers email with a single or half sentence （never aparagraph）； has an occasional triple digit weekly hours reported； has no time to be political； and when they have one
hour left to go with a problem， you can guarantee that you better multiply by at least four.
TUNING SUMMARY
Since a single query or a poorly setup INIT.ora can bring system to its knees， the key to tuning often comes down to
how effectively you can tune the database memory and also those single problem queries. You must remember to
tune both the INIT.ora parameters as well as the actual queries. To tune effectively， you must know your DATA
since your system is UNIQUE. You must adjust methods to suit your system. A single index or a single query can
bring an entire system to a near standstill. Find those queries with v$sqlarea！
Deploying， Managing， and Administering the Oracle Internet Platform
Paper #224/ Page 13
REFERENCES
Performance Tuning Tips and Techniques； Richard J. Niemiec， Oracle Press： ISBN： 0-07-882434-6
PL/SQL Tips and Techniques； Joseph C. Trezzo， Oracle Press
Oracle Application Server； Bradley D. Brown， Oracle Press
Joe Holmes； Oracle Query Tuning using Mathematical Techniques， Select Magazine
TUSC Internal Oracle DBA Guide， TUSC 1993，1994，1995
Server Manager ； Brad Brown， TUSC
Tuning Oracle； Corey， Abbey， Dechichio
Performance Tuning； Now YOU are the Expert， Undocumented Index Suppression， Rich Niemiec， TUSC； 1991
Get the most for your Money： Utilize the V$ Tables； Joseph C. Trezzo； TUSCTuning an Oracle Database； Sue Jang；
Oracle Corporation
Version 6 & 7 DBA， Migration and Performance Tuning Guides， Oracle Corporation
IOUG Proceedings； Multiple Downsizing and Distributed Database Articles
Oracle7 Internals； Oracle Corp.； Craig A. Shallahamer
Oracle 7.1 Release Features Parallel Everything； Integrator； Summer 1994
Tuning Oracle for Batch and On-Line Processing； Eyal Aronoff； Select Magazine
Tuning Oracle in the Land of Expert Systems； Monty Carolan， Richard Niemiec， Dave Kaufman， TUSC
SPECIAL THANKS TO
Brad Brown， Joe Trezzo， Randy Swanson， Burk Sherva， Jake Van der Vort， Greg Pucka and the TUSC Team who
have all made contributions to this document. Dave Kaufman， Sean McGuire and Mike Henderson for help in the
INIT.ORA section of this article
ABOUT THE AUTHOR：
Richard J. Niemiec （niemiecr@tusc.com） is the Executive Vice President of The Ultimate Software Consultants
（TUSC）， a Lombard， Illinois based database consulting company. TUSC specializes in the full cycle of database
development including Business Modeling， Design， Development， Implementation and Support. Richard has been
giving lectures and presentations on Oracle for the past 8 years and is the current President of the Midwest Oracle
Users Group （MOUG）。 Rich can be reached at TUSC at （630） 960-2909 （www.tusc.com）。
Please report errors in this article to TUSC. Neither TUSC nor the author warrant that this document is error-free.
