ch20.htm

Teach yourself Oracle8 in 21 day

<P>A good way to determine how much network bandwidth you are using is by employing
a network monitor. Network monitors can be hardware or software devices. Most operating
systems do not provide any way to monitor the network from your system because your
network card normally passes only packets addressed to that machine. Other packets
are ignored.
<H4><FONT COLOR="#000077"><B>Memory</B></FONT></H4>
<P>Memory problems sometimes manifest themselves as other problems. For example,
a lack of memory might cause excessive I/O, which will appear as an I/O problem.
Of prime importance is the fact that the system is not paging or swapping. Paging
and swapping occur when no physical memory is available for code processing. The
CPU pages, or moves data out of memory, onto a paging area on disk. This operation
can be quite expensive. If you are seeing significant paging, add more memory to
the system or reduce the Oracle DB block buffer cache to free more memory.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>If your system is paging or swapping,
	you have a severe performance problem. No amount of tuning will make up for the performance
	degradation caused by this paging or swapping. If you are seeing excessive paging,
	get more memory. If you are at the limit of memory that your system can support,
	start looking for a new system. 
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077"><B>Monitoring the System</B></FONT></H3>
<P>After you know your limitations, you can determine whether you are exceeding them
by monitoring the system. There are several ways to monitor the system; one of the
best ways under Windows NT is with the Performance Monitor, or <I>perfmon</I>. On
a UNIX system, you can monitor many objects with the sar utility. Most operating
systems also offer a graphical monitoring tool. These tools are good, but are specific
to the brand of hardware you purchase. Look in your OS documentation for instructions
on how to use these tools.</P>
<P>Oracle provides a set of useful scripts for monitoring the performance of the
RDBMS. These scripts are called <TT>UTLBSTAT</TT> (begin) and <TT>UTLESTAT</TT> (end).
<TT>UTLBSTAT</TT> should be run first, followed shortly thereafter by <TT>UTLESTAT</TT>.
<TT>UTLESTAT</TT> displays a large number of statistic and counter values based on
the time since <TT>UTLBSTAT</TT> was run.
<H4><FONT COLOR="#000077"><B>Using perfmon</B></FONT></H4>
<P>Select Programs | Administrative Tools (Common) | Performance Monitor to invoke
perfmon. The Performance Monitor appears in chart mode, which is fine for small numbers
of items (such as a trend over an extended period of time). If you are looking at
a large amount of data, use report mode; in this mode, values are displayed onscreen
as numerical values. Invoke report mode by selecting it via the View drop-down menu
or by clicking the Report button.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>perfmon adds some CPU overhead
	to the system. If you extend the report inverval, perfmon will run less often, thus
	reducing CPU overhead. Increasing the report interval also increases the accuracy
	of the report. I recommend setting the report interval to 15 or 30 seconds. 
<HR>


</BLOCKQUOTE>

<P><FONT COLOR="#000077"><B>Looking for I/O Bottlenecks with perfmon</B></FONT></P>
<P>To use perfmon to view events in the system, you must select counters. Each of
these counters represents a different event that is monitored. To view counter information,
do the following:

<DL>
	<DD><B>1. </B>Click the + button to add a new counter.
	<P><B>2. </B>From the Add to Report screen, choose the PhysicalDisk object from the
	Object drop-down menu, as shown in Figure 20.1.
</DL>

<P><A NAME="01"></A><A HREF="01.htm"><B>Figure 20.1.</B></A></P>
<P><I>Choose the PhysicalDisk object.</I></P>

<DL>
	<DD><B>3. </B>You are presented with a number of objects from which you can select.
	You can monitor many useful objects, but my opinion is that the following items are
	most important in monitoring the disk I/O subsystem:
</DL>


<UL>
	<LI>Disk Reads/Sec--The number of reads per second to that disk or volume.
	<P>
	<LI>Disk Writes/Sec--The number of writes per second to that disk or volume.
	<P>
	<LI>Disk Transfers/Sec--The total number of reads and writes per second to that disk
	or volume.
	<P>
	<LI>Avg. Disk Sec/Read--The average time it takes for the read operation to occur.
	<P>
	<LI>Avg. Disk Sec/Write--The average time it takes for the read operation to occur.
	<P>
	<LI>Avg. Disk Sec/Transfer--The average time it takes for the read and write operations
	to occur.
	<P>
	<LI>Avg. Disk Queue Length--The average number of I/Os in the disk I/O subsystem.
</UL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>A hardware disk array will appear
	to the NT system as a single disk. Divide the number of I/Os per second by the number
	of disk drives to get the I/Os per second per disk drive. This is not 100% accurate
	because the I/O balance may not be perfect, but it is the best that you can do. 
<HR>


</BLOCKQUOTE>

<P>Of particular note should be the number of I/Os per second per disk drive and
the disk latency. A typical disk drive or disk array should complete I/Os in 20-30
milliseconds (0.020-0.030 seconds). If any volume is much higher than that (more
than 0.060 seconds), you are probably seeing some sort of disk bottleneck. Consider
adding more disk drives.</P>
<P>If you are running on UNIX, you can get this information using sar or other monitoring
tools.</P>
<P><FONT COLOR="#000077"><B>Looking for Memory Bottlenecks with perfmon</B></FONT></P>
<P>In Windows NT, the Pages/Sec counter under the Memory objects can be used to indicate
that the system is paging. If you see significant activity with this counter, you
are using too much memory. If you cannot add more memory, you should reduce the memory
used by Oracle and other processes or reduce the number of users on the system.
<H4><FONT COLOR="#000077"><B>Using </B>UTLBSTAT<B> and </B>UTLESTAT</FONT></H4>
<P>Within the directory <TT>\ORANT\RDBMS80\ADMIN</TT> are a number of scripts used
internally within Oracle. You ran <TT>CATPROC.ORA</TT> and <TT>CATALOG.ORA</TT> from
this directory when you created a database. <TT>UTLBSTAT</TT> and <TT>UTLESTAT</TT>
are also run from here.</P>
<P><TT>UTLBSTAT</TT> sets up some internal tables and takes a snapshot of some of
the internal Oracle counters. After you run under load for a time, <TT>UTLESTAT</TT>
takes another snapshot and compares the results. <TT>UTLESTAT</TT> displays a large
number of statistic and counter values based on the time since <TT>UTLBSTAT</TT>
was run.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The <TT>UTLBSTAT.SQL</TT> and <TT>UTLESTAT.SQL</TT>
	scripts each have a <TT>CONNECT INTERNAL</TT> string at the top. Most likely, this
	will not work under NT. Simply comment out this line and run the script from the
	<TT>INTERNAL</TT> user account. Place the word <TT>Rem</TT> at the beginning of those
	lines. 
<HR>


</BLOCKQUOTE>

<P>The <TT>UTLBSTAT</TT> and <TT>UTLESTAT</TT> scripts can be run from the SVRMGR30
utility. After you connect in as <TT>INTERNAL</TT> or <TT>SYS</TT>, you can run the
<TT>UTLBSTAT</TT> script with the following syntax:</P>
<PRE><FONT COLOR="#0066FF">
@D:\ORANT\RDBMS80\ADMIN\UTLBSTAT;
</FONT></PRE>
<P>After you run for a while under load, you can finish monitoring Oracle and view
the results by running the <TT>UTLESTAT</TT> script with the following syntax:</P>
<PRE><FONT COLOR="#0066FF">
@D:\ORANT\RDBMS80\ADMIN\UTLESTAT;
</FONT></PRE>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The usefulness of the data retrieved
	from <TT>UTLBSTAT</TT> and <TT>UTLESTAT</TT> might depend on the length of time for
	which the data is collected. When looking at specific events, five minutes might
	be enough. When looking at long-term performance, you might need to run for hours
	or more. If you are interested in a specific event such as a long-running query,
	the runtime of the event will be sufficient. 
<HR>


</BLOCKQUOTE>

<P>The result of <TT>UTLESTAT</TT> is, in its entirety, quite long. Following is
the resulting data from <TT>UTLESTAT</TT> only; the echo of the SQL statements has
been removed:</P>
<P><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></P>
<PRE><FONT COLOR="#0066FF">LIBRARY      GETS       GETHITRATI PINS       PINHITRATI RELOADS    INVALIDATI
------------ ---------- ---------- ---------- ---------- ---------- ----------
BODY                  0          1          0          1          0          0
CLUSTER              97          1         50          1          0          0
INDEX                 0          1          0          1          0          0
OBJECT                0          1          0          1          0          0
PIPE                  0          1          0          1          0          0
SQL AREA            120       .808        364       .874          0          0
TABLE/PROCED        235       .923        265       .879          0          0
TRIGGER               0          1          0          1          0          0
Statistic                    Total       Per Transact  Per Logon    Per Second
---------------------------- ----------- ------------  -----------  -----------
DBWR buffers scanned                 601          601       150.25         2.86
DBWR free buffers found              581          581       145.25         2.77
DBWR lru scans                        35           35         8.75          .17
DBWR make free requests               35           35         8.75          .17
DBWR summed scan depth               601          601       150.25         2.86
DBWR timeouts                         68           68           17          .32
SQL*Net roundtrips to/from           129          129        32.25          .61
background timeouts                  210          210         52.5            1
buffer is not pinned count          2175         2175       543.75        10.36
buffer is pinned count              6343         6343      1585.75         30.2
bytes received via SQL*Net         11212        11212         2803        53.39
bytes sent via SQL*Net to c         8270         8270       2067.5        39.38
calls to get snapshot scn:           145          145        36.25          .69
calls to kcmgas                        1            1          .25            0
calls to kcmgcs                       10           10          2.5          .05
calls to kcmgrs                      181          181        45.25          .86
cleanouts only - consistent            5            5         1.25          .02
cluster key scan block gets          801          801       200.25         3.81
cluster key scans                    506          506        126.5         2.41
commit cleanouts                      16           16            4          .08
commit cleanouts successful           16           16            4          .08
consistent gets                     4557         4557      1139.25         21.7
cursor authentications                 6            6          1.5          .03
db block changes                     104          104           26           .5
db block gets                        141          141        35.25          .67
enqueue releases                      26           26          6.5          .12
enqueue requests                      20           20            5           .1