?? ch20.htm
字號:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself Oracle 8 In 21 Days -- Ch 20 -- Effectively Tuning and Optimizing the Database</TITLE>
</HEAD>
<BODY TEXT="#000000" BGCOLOR="#FFFFFF">
<CENTER>
<H1><IMG SRC="../buttonart/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR>
<FONT COLOR="#000077">Teach Yourself Oracle 8 In 21 Days</FONT></H1>
</CENTER>
<CENTER>
<P><A HREF="../ch19/ch19.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch21/ch21.htm"><IMG
SRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A>
<HR>
</CENTER>
<CENTER>
<H1><FONT COLOR="#000077">- Day 20 -<BR>
Effectively Tuning and Optimizing the Database</FONT></H1>
</CENTER>
<P>Performance can be one of the most fun or most frustrating aspects of administering
a database. It is almost always extremely challenging, and forces you to use all
your experience to isolate the bottleneck and determine a response. I find performance
tuning and system optimization to be extremely rewarding and challenging; I enjoy
it, and I hope you do too.</P>
<P>I hate to refer to performance bottlenecks as <I>bugs</I> or <I>problems</I> because
those terms suggest something is broken. Sometimes bottlenecks occur because you
have reached the capacity of your system, and no tuning or changes can alter that
fact; other solutions must be found. The capacity planner must try to anticipate
both when the system will reach capacity and what new resources are needed before
this happens.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Oracle instance and OS tuning should
be performed in conjunction with application tuning. Because much of the instance
tuning is based on the data-access patterns and memory usage of the application,
changes to the application might result in the opportunity to retune the instance
for greater optimization. A perfectly optimized Oracle instance cannot make up for
a poorly tuned application. Unfortunately, this book does not cover application tuning
in detail, but several good books on the subject are available. In fact, I have written
just such a book: <I>Oracle Performance Tuning and Optimization</I> (Sams Publishing).
<HR>
</BLOCKQUOTE>
<P>Performance tuning and capacity planning are closely related. They rely on and
play off of each other. The capacity-planning stage assumes that you have a well-tuned
system, and bases most of its recommendations on this. The system-tuning stage assumes
you have a system that can support the number of users and the number of tasks being
performed. The capacity-planning and system-performance engineers often work together
to determine where the bottleneck is and how to resolve it.
<H2><FONT COLOR="#000077"><B>What Is a Bottleneck?</B></FONT></H2>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>The term <I>bottleneck</I>
refers to the component or components that have a limiting factor on the system.
Some bottlenecks result from inherent limitations of your system, but others can
be fixed via parameter tuning. Many times, additional components are needed to fix
bottlenecks. System tuning typically involves finding system bottlenecks and eliminating
them. Examples of bottlenecks that are typically found in an Oracle system include
<UL>
<LI>Disk drive bottleneck--A disk drive can handle only so many I/Os per second before
latencies increase to unacceptable levels. The systemwide I/Os per second is fixed,
so if your CPU and memory system requests I/Os more quickly than the I/O subsystem
can deliver, a disk bottleneck will occur.
<P>
<LI>Memory bottleneck--If you have inadequate memory, you won't have a good cache-hit
rate. A high cache-hit rate indicates that most of the data you need is in the Oracle
buffer cache. This reduces the number of needed I/Os.
<P>
<LI>CPU bottleneck--In the case of a CPU bottleneck, you are running your system
as quickly as the CPUs can run.
<P>
<LI>Network bottleneck--A network bottleneck occurs when so much traffic is over
the network, you can't transfer data any faster. This is important to backup and
recovery.
<P>
<LI>Bus bottleneck--A bus bottleneck occurs when your hardware cannot transfer data
across the CPU to memory or I/O bus. This is a hardware limitation.
<P>
<LI>Other bottlenecks--Numerous other bottlenecks, either hardware, software, or
a combination of the two, can occur.
</UL>
<P>Some bottlenecks can be fixed easily, and others are difficult to overcome. Later
today you will see how to determine the location of a bottleneck and receive some
hints on how to fix bottlenecks.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Every system is different. There
is no magic diagnostic tool that can tell you how to tune your system. It is an acquired
skill that you will develop with practice.
<HR>
</BLOCKQUOTE>
<H2><FONT COLOR="#000077"><B>Performance Tuning Methodology</B></FONT></H2>
<P>Determining what, if anything, is a bottleneck in your system can be difficult.
I have found that following a tuning methodology or plan can help you to reach the
correct conclusion in the shortest amount of time. By <I>tuning methodology,</I>
I mean a way of looking at performance issues and determining of the root of those
issues. Here is the performance tuning and optimization methodology I use:
<DL>
<DD><B>1.</B> Analyze the system--Determine whether a performance issue actually
exists. You might determine this by running transactions and checking response times
or by monitoring the system statistics. You should look at OS stats as well as Oracle
stats.
<P><B>2.</B> Determine the cause--Here you form a hypothesis about where the bottleneck
is by looking at the analysis from step 1.</P>
<P><B>3.</B> Develop a solution or a test--If you are certain you have determined
where the bottleneck is, develop a solution for it. If not, develop a test to verify
your hypothesis. When you develop the test, you should know what the results of the
test will tell you.</P>
<P><B>4. </B>Run the test or implement the solution--If you have hit upon a solution,
implement it. If not, run your test under carefully controlled conditions. This test
will be used to determine whether you are right or wrong.</P>
<P><B>5.</B> Analyze the results--After the test has been run, you must analyze the
result. Always view your results with skepticism. If something is too good to be
believed, perhaps you should not believe it.
</DL>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>You should know what you expect
from the test you have developed. Many times a test result can provide good data
if the result is positive, but no useful information if the result is negative. For
example, adding another disk drive will tell you that you had a disk bottleneck if
this addition increases performance, but does not tell you what the problem is if
performance does not increase. These tests are still valuable, even though only one
result gives you good data.
<HR>
</BLOCKQUOTE>
<P>Although you don't need to use my system, it helps if you develop some system
for <FONT COLOR="#000000">Troubleshooting</FONT>.
<H3><FONT COLOR="#000077"><B>Analyze the System</B></FONT></H3>
<P>Analyzing the system gives you a good place to start. The first step in analyzing
a system is to determine whether you have a problem. You should periodically monitor
the following:
<UL>
<LI>Response times--Do users complain that the system seems slower? Periodically
ask your user community how the performance of the system seems to them; some-times
end users are reluctant to tell you about prospective problems until they reach a
critical state. It is a good idea to have a test query that you can run to compare
current response times with previous response times. If your test query takes significantly
longer to run than previously, you are seeing some kind of system slowdown.
<P>
<LI>Throughput--Look at the system as a whole by examining the system performance
monitor (perfmon for NT, sar for UNIX). Compare current and past results. Is your
CPU utilization significantly higher than in previous samples? What about disk usage?
You will look at these in more detail later today.
<P>
<LI>Your intuition--Sometimes the system just feels wrong. If you think something
isn't quite right, check it out. You know your system better than anyone.
</UL>
<P>After you analyze the system, you might determine that there are no performance
issues, that everything is running fine. Great! But if you do come across performance
problems, you can start determining the cause and possible solutions.
<H3><FONT COLOR="#000077"><B>Determine the Cause</B></FONT></H3>
<P>The next stage is to form a hypothesis of what you think the problem is. This
should be based on analysis of the system and what you know of the system's design
and capacity. For example, if you have a system with five 4.3GB SCSI disk drives
for datafiles, the system should be able to support up to 350 I/Os per second before
you see large disk latencies. If you are driving those disk drives at 450 I/Os per
second, you could hypothesize that you are having a disk I/O problem. This hypothesis
should be specific enough to help lead you to a solution to the bottleneck.
<H3><FONT COLOR="#000077"><B>Develop a Solution or a Test</B></FONT></H3>
<P>Develop a solution to the bottleneck or some sort of test to confirm your hypothesis.
Whether you need to develop a solution or a test depends on how certain you are about
having discovered the bottleneck and how expensive the solution is. If you are certain
you have a disk bottleneck and have more disk drives available, it is not necessary
to test your theory. But if you are not certain or think that other issues might
be involved, it is a good idea to develop a test. Be sure your test will give you
meaningful information.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>Remember the difference between
<I>cause</I> and <I>effect</I>. You might see something that looks like a disk bottleneck
when in fact a lack of memory is causing unnecessary I/O.
<HR>
</BLOCKQUOTE>
<H4><FONT COLOR="#000077"><B>Anticipate Your Results</B></FONT></H4>
<P>The test you devise to confirm or rule out a certain bottleneck should lead to
expected results. If you are unsure what positive and negative results would indicate,
the test is not worth running. I often run tests where a positive result is meaningful
but a negative tells me nothing. This test is still worth running because that positive
result might either solve the problem or rule out a cause. Some examples of the kinds
of testing you can do are shown later today.
<H3><FONT COLOR="#000077"><B>Run the Test or Implement the Solution</B></FONT></H3>
<P>Either run a test that will give you valuable information about the bottleneck
or try to fix the problem. Carefully monitor the system in both cases. Even if it
seems that the performance of the system has immediately and dramatically improved,
you should still monitor the system and collect some data. If you run into a performance
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -