ch20.htm

Teach yourself Oracle8 in 21 day

bottleneck in the future, this data can be valuable.
<H3><FONT COLOR="#000077"><B>Analyze the Results</B></FONT></H3>
<P>Monitor the system after a change has been made or a test has been run to determine
whether the bottleneck has been eliminated. In the case of a capacity issue, the
bottleneck might appear to have been eliminated, but the fix might be only temporary.
Carefully analyze your system so you can anticipate when you will run out of capacity
again.</P>
<P>You will sometimes find that the results of your test prove that your hypothesis
was incorrect. If this is the case, return to step 2 to determine the cause of the
bottleneck.
<H4><FONT COLOR="#000077"><B>Question Your Results</B></FONT></H4>
<P>Sometimes testing does not afford a valid result. This typically occurs when you
make a change that you expect to either increase or decrease performance, and neither
occurs. This indicates that one of two things has occurred:

<UL>
	<LI>The bottleneck is somewhere else--If you have a bus bandwidth problem and add
	more memory, there will be no improvement because you are limited by the speed of
	the bus.
	<P>
	<LI>The test was flawed--This could occur for any number of reasons. Perhaps you
	modified the wrong parameter file, or perhaps the change requires a system reboot.
</UL>

<P>If your results are flawed, be skeptical. Check the results. It's okay if you
don't believe the results and want to try another test. Only rarely does a test turn
out to be flawed or do other problems occur.
<H2><FONT COLOR="#000077"><B>Operating System Overview</B></FONT></H2>
<P>If you understand the basic concepts of how the operating system works, you will
have a better feel for how to monitor and tune the operating system.
<H3><FONT COLOR="#000077"><B>Windows NT</B></FONT></H3>
<P>Microsoft Windows NT, a relatively new operating system, has quickly gained popularity.
Windows NT has file and print services similar to those offered by NetWare, but Windows
NT is used primarily as an application server or client operating system. Windows
NT comes in two varieties:

<UL>
	<LI>NT Workstation--NT Workstation is designed for the client users and does not
	contain many of the management pieces that come standard with NT Server.
</UL>


<UL>
	<LI>NT Server--NT Server includes the management tools necessary for maintaining
	a server.
</UL>

<P>Windows NT, a multiprocessor operating system, can take advantage of scaleable
performance increases via the addition of CPUs. Windows NT is also a server operating
system; there is no facility within Windows NT to provide login functionality from
terminals. From the beginning, Windows NT was designed to support clients over network
connections. The Windows NT operating system provides functionality such as 16-bit
Windows application support and a GUI, but because of this functionality, some overhead
is associated with Windows NT.
<H4><FONT COLOR="#000077"><B>Architectural Overview of Windows NT</B></FONT></H4>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>Windows NT is based on a
microkernel architecture. In a <I>microkernel</I> <I>architecture</I>, the core kernel
is small because most of the OS functionality is removed from the kernel. Because
of this, the system is modular; large pieces of the OS can be easily replaced. Although
a microkernel does not provide additional performance, it does provide a flex-ibility
that OS and device-driver developers can exploit. Because of the flexible subsystem
architecture, OS changes such as file systems, hardware architecture, and memory
subsystems can easily be replaced.</P>
<P>In Windows NT, hardware support is provided through the Hardware Abstraction Layer
(HAL). The HAL is employed by hardware vendors to support their systems' base architectures
and to provide device-driver support. Because of the microkernel and HAL, Windows
NT can support different architectures such as Intel, DEC Alpha, MIPS, PowerPC, and
so on. The microkernel is the common code base in which each of these architectures
is rooted.</P>
<P>Most 16-bit applications written for Windows can run on non-Intel architectures
through the use of a compatibility mode driver. Applications written specifically
for Windows NT can be ported to non-Intel platforms with minimal effort. However,
applications that have not been written to take advantage of a particular architecture
might not run optimally. Oracle is currently supported only on the Intel architecture.
As with all Oracle ports, Oracle has been optimized for Windows NT and specifically
optimized for Windows NT on Intel platforms.</P>
<P>The Windows NT architecture provides for the use of <I>threads</I>, sometimes
known as <I>lightweight processes</I>. Through the use of threads instead of processes,
much of the overhead associated with process switching is reduced. Threads are automatically
created when the Oracle instance is started (see Table 20.1).
<H4><FONT COLOR="#000077"><B>Table 20.1. Oracle service threads.</B></FONT></H4>
<P>
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><B>Thread number</B></TD>
		<TD ALIGN="LEFT"><B>Oracle process</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">0, 1</TD>
		<TD ALIGN="LEFT">Oracle Service</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">2</TD>
		<TD ALIGN="LEFT">PMON</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">3</TD>
		<TD ALIGN="LEFT">DBWR</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">4</TD>
		<TD ALIGN="LEFT">LGWR</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">5</TD>
		<TD ALIGN="LEFT">SMON</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">6</TD>
		<TD ALIGN="LEFT">RECO</TD>
	</TR>
</TABLE>
</P>
<P>A shadow thread is created on behalf of each user accessing the Oracle database.
Remember, The shadow processes communicate with the user and interact with Oracle
to carry out the user's requests. For example, if the user process requests a piece
of data not already in the SGA, the shadow process is responsible for reading the
data blocks from the datafiles into the SGA. Under Windows NT, these processes are
invoked as kernel threads. Each of these threads has its own thread number.
<H3><FONT COLOR="#000077"><B>UNIX</B></FONT></H3>
<P>UNIX, born in 1969, predates NetWare, Windows NT, and OS/2 by many years. UNIX
is different from NetWare, Windows NT, and OS/2 in that it was not designed as a
client/server operating system. That is not to say that UNIX is not now used as a
network server, but that network service was not the original intent of the operating
system.</P>
<P>Even though UNIX is fairly standard, a divergence in UNIX has occurred over the
years. Because almost every major computer company develops and sells a version of
UNIX, there are differences in the UNIX offerings from the different vendors.</P>
<P>The PC server market boasts three main UNIX versions: SCO UNIX, SCO UnixWare,
and Solaris from SunSoft. IBM calls its UNIX offering AIX. Hewlett-Packard has a
UNIX operating system called HP-UX; AT&amp;T Global Information Solutions simply
calls its product UNIX System V. SunSoft produces versions of UNIX for both Intel
and Sun SPARC processors; its products are called Solaris and Solaris X86. Currently,
most vendors (with the exception of SCO) base their versions of UNIX on one common
core: UNIX System V Release 4, which is sometimes referred to as UNIX SVR4.</P>
<P>In many cases, applications are binarily compatible between operating systems
on the same platform, but it is not uncommon for the vendor to add special enhancements
in the operating system for performance. Oracle always takes advantage of OS-specific
features, even though it would be simpler to ship just one binary.
<H4><FONT COLOR="#000077"><B>Architectural Overview of UNIX</B></FONT></H4>
<P>UNIX consists of a core piece called the kernel surrounded by applications and
tools. The kernel contains all hardware support, device drivers, scheduling routines,
and the network stack. Unlike the microkernel architecture used in Windows NT, the
UNIX kernel contains all the core operating system functionality.</P>
<P>UNIX is a virtual-memory operating system but is very configurable. In the UNIX
operating system, not all memory is allocated and used in the same manner. Shared
memory used for the Oracle SGA is treated differently from normal memory. Shared
memory is allocated at boot time, is not available to general user processes, and
must be allocated through shared memory system calls in the operating system.</P>
<P>The fact that shared memory is treated differently allows certain functions to
occur. Some varieties of UNIX allow you to allocate shared memory using a 4MB memory
page size. This arrangement cuts down on page entries that must be maintained by
the operating system and guarantees larger chunks of contiguous memory. Shared memory
is also unique in that it is nonpageable. This means that if you have enough shared
memory to create the SGA at instance startup, you don't ever have to worry about
the SGA being paged out.</P>
<P>Other features that have been added over the years to the UNIX operating system
to improve database performance include realtime processing, high-speed semaphores,
and asynchronous I/O. Not all these features are in every implementation of UNIX.
Check with your OS vendor to see what is available for your environment.
<H2><FONT COLOR="#000077"><B>Finding a Performance Bottleneck</B></FONT></H2>
<P>Finding a performance bottleneck can be difficult. There is no proven formula
or list of things to look for that immediately tells you what the problem is. Finding
a performance bottleneck is an investigative task, and the more experience you get,
the better at it you will be.
<H3><FONT COLOR="#000077"><B>Knowing Your System's Limitations</B></FONT></H3>
<P>To determine whether a system component is prompting a performance bottleneck,
ascertain the limitations of that component when possible. Doing so allows you to
determine whether you are exceeding that limitation. Unfortunately, these limitations
are difficult to gauge in most cases, but it is possible to characterize the component
in some cases. The limitations of the I/O subsystem and in the networking components
are ascertainable.
<H4><FONT COLOR="#000077"><B>The I/O Subsystem</B></FONT></H4>
<P>Some pieces of the I/O subsystem, such as the disk drive, have been well tested
and documented over the last few years; limitations of the controller and bus are
not as well known. The disk drive is a physical piece of hardware that usually comes
with a specification. This specification tells you certain things about the disk
drive, such as

<UL>
	<LI>Average seek time--This is the average time it takes for the disk arm to move
	from one location to another. This relates to the number of random I/Os per second
	that you can perform.
	<P>
	<LI>Average track-to-track seek time--This is the time it takes to move to the next
	adjacent track on the disk drive. This relates to the number of sequential I/Os per
	second that you can perform.
	<P>
	<LI>Transfer time--This is the time it takes to move the data from the disk to the
	system bus.
	<P>
	<LI>Rotations per second--This is how quickly the disk spins. This relates to how
	quickly you can rotate to the next bit of data on the disk.
</UL>

<P>A typical disk drive might have an average seek time of 9 milliseconds; for random
I/Os, it takes 9 milliseconds to move to the next data. Add 1 millisecond for data
transfer to make 10 milliseconds per random I/O. Divide 1 second by 10 milliseconds
to see how many times per second you can perform a random seek:</P>
<P>Seeks = 1 Sec / 0.01 Sec/Seek = 100 Seeks</P>
<P>On Day 4, &quot;Properly Sizing Your Database and Planning for Growth,&quot; you
saw the &quot;knee of the curve&quot; theory. According to that theory, you don't
want to exceed 60-70% of your available capacity; otherwise, times increase. This
is why you should not exceed 60-70 seeks per second for random I/O.
<H4><FONT COLOR="#000077"><B>The Network Subsystem</B></FONT></H4>
<P>The network subsystem is another area where you can compare your throughput against
a known maximum. Ethernet networks are available in a number of different throughput
levels. This is usually indicated in the name of the network hardware. A few of the
network hardware specifications include

<UL>
	<LI>10BaseT--This is a 10Mb/second ethernet. With this hardware you can have a peak
	throughput of 10Mb, or approximately 1.25MB/second.
	<P>
	<LI>100BaseT--This is a 100Mb/second ethernet. With this hardware you can have a
	peak throughput of 100Mb, or approximately 12.5MB/second.
</UL>

<P>Monitor your system to you determine whether you are close to meeting or exceeding
the available throughput. Remember that smaller packets are not as efficient, so
if you send many small requests (common in database applications), you might not
realize the full potential of the network.</P>