ch11.htm

Teach yourself Oracle8 in 21 day

beneficial; this is not to say that the option is not beneficial on uniprocessor
machines as well.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>As larger and larger disks are produced
	at lower and lower prices, many installations end up with I/O problems caused by
	not having the required I/O bandwidth. Two disk drives are faster than one disk drive
	if your I/O is balanced.<BR>
	The Parallel Query option can help only in systems where I/O is not a bottleneck.
	When I/O is not a problem, you'll see significant gains from parallel queries. 
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077"><B>Monitoring Processes</B></FONT></H2>
<P>The processes or threads used by Oracle can be monitored via the OS facilities
of your operating system or from within the Oracle instance itself. Each of these
has its own advantages and disadvantages. By looking at the processes from within
Oracle, you can gain more insight as to what these processes are actually doing,
but you cannot determine exactly how many resources they are currently consuming.
It takes a combination of external and internal monitoring to get the complete picture.
Let's look at some of the ways you can monitor these processes and resources.
<H3><FONT COLOR="#000077"><B>Monitoring Processes from the NT Task Manager</B></FONT></H3>
<P>The NT Task Manager is an ideal tool to get a quick view of what is happening
in your system. It is not a very complex tool, nor does it give you a great deal
of information, but for a quick look it is very useful. To invoke the Task Manager,
right-click the NT 4.0 toolbar and select Task Manager, as shown in Figure 11.12.</P>
<P><A NAME="12"></A><A HREF="12.htm"><B>Figure 11.12.</B></A></P>
<P><I>Select Task Manager from the context menu.</I></P>
<P>Once the Task Manager is running, you'll see the performance screen (see Figure
11.13). This screen shows the CPU and memory usage in the system. In the lower part
of the screen, you'll see numerical information relating to thread and process statistics
and memory usage.</P>
<P><A NAME="13"></A><A HREF="13.htm"><B>Figure 11.13.</B></A></P>
<P><I>Viewing performance.</I></P>
<P>By clicking the Processes tab, you can view the Processes screen. From here you
can see CPU, physical memory, and virtual memory used by the various processes in
the system. The Processes screen is shown in Figure 11.14.</P>
<P><A NAME="14"></A><A HREF="14.htm"><B>Figure 11.14.</B></A></P>
<P><I>Viewing processes.</I></P>
<P>This information is quite useful and easy to get to, but it is quite coarse. As
you can see, Oracle shows up as one process named <TT>oracle80.exe</TT>. This monitor
gives you no information on the individual threads that make up this process.
<H3><FONT COLOR="#000077"><B>Monitoring Processes from perfmon</B></FONT></H3>
<P>The NT Performance Monitor (<I>perfmon</I>) can also be used to monitor threads,
but as with the Task Manager, not much useful information can be gleaned from this.
Once the Performance Monitor is invoked, you can select thread information, as shown
in Figure 11.15. This information can be in terms of CPU time, context switches per
second, and so on. This information has some use, but without knowing what session
it is, it is not worth much.</P>
<P><A NAME="15"></A><A HREF="15.htm"><B>Figure 11.15.</B></A></P>
<P><I>An example of the perfmon.</I></P>
<P>For viewing process and thread information, I think the NT Performance Monitor
is of little use. However, as you'll see on Day 20, the NT Performance Monitor is
great for viewing I/O and CPU information.
<H3><FONT COLOR="#000077"><B>Monitoring Sessions with the Instance Manager</B></FONT></H3>
<P>The Instance Manager has some fundamental information that you can view about
sessions. This information essentially concerns connections and activities. Open
the Instance Manager and click the Sessions icon. Here you'll see the Sessions screen,
which is shown in Figure 11.16.</P>
<P>You can glean a lot of information from this screen. The right portion of the
screen provides the following session information:

<UL>
	<LI>Session ID--The session's internal ID number
	<P>
	<LI>Status--Whether the session is active
	<P>
	<LI>Username--The Oracle user ID
	<P>
	<LI>Schema Name--The schema to which the user is attached
	<P>
	<LI>OS User--The OS user account that is connected
	<P>
	<LI>Terminal--The connection name (if applicable)
	<P>
	<LI>Machine Name--The machine on which this user is running
	<P>
	<LI>Program--The program that is connected to the instance
</UL>

<P><A NAME="16"></A><A HREF="16.htm"><B>Figure 11.16.</B></A></P>
<P><I>Viewing sessions with the Instance Manager.</I></P>
<P>This information can be quite useful if you are looking for a particular session.
It is also possible to disconnect a session from the instance using the Instance
Manager. Right-click the session and select Disconnect, as shown in Figure 11.17.
This disconnects that session from the Oracle instance.</P>
<P><A NAME="17"></A><A HREF="17.htm"><B>Figure 11.17.</B></A></P>
<P><I>Disconnecting a session.</I></P>
<P>
<H3><FONT COLOR="#000077"><B>Monitoring Oracle Processes from the Command Line</B></FONT></H3>
<P>You can gather a huge amount of information about the Oracle processes by querying
the internal performance tables using the <TT>V$</TT> views. These views, predefined
by Oracle, look into the dynamic performance tables. This information can be quite
useful. Some <TT>V$</TT> views that are of most interest when you are monitoring
processes are as follows:

<UL>
	<LI><TT>V$CIRCUIT</TT>--Contains information about virtual circuits that are created
	through the dispatcher and server processes
	<P>
	<LI><TT>V$DISPATCHE</TT>--Contains dispatcher information
	<P>
	<LI><TT>V$QUEU</TT>--Contains information pertaining to the multithreaded message
	queues
	<P>
	<LI><TT>V$SESS_I</TT>--Contains I/O information for each session
	<P>
	<LI><TT>V$SHARED_SERVE</TT>--Contains shared server information
	<P>
	<LI><TT>V$SYSSTA</TT>--Contains miscellaneous system statistics
</UL>

<P>If you want more information about the dynamic performance tables or performance
in general, I suggest you read my first book, <I>Oracle Performance Tuning and Optimization</I>
(Sams Publishing), in which I go into this and more in great detail.
<H2><FONT COLOR="#000077"><B>Summary</B></FONT></H2>
<P>Today you were introduced to two types of processes: user processes (also known
as the shadow or server processes) and Oracle processes (also known as background
processes). In some operating systems, such as Windows NT, these processes are actually
threads.</P>
<P>As you saw, it is only through the server or shadow processes that the user communicates
with the database. The user process manipulates the user's input and communicates
with the Oracle server process through the Oracle program interface. The user process
is also used to display the information requested by the user and, if necessary,
can process this information into a more useful form. As you saw, this can happen
on a one-to-one basis with the dedicated server process, or on a shared basis with
the multithreaded server.
<H2><FONT COLOR="#000077"><B>What's Next?</B></FONT></H2>
<P>Day 12, &quot;Working with Tables, Views, and Synonyms,&quot; introduces the Oracle
schema. The next three days cover the various schema objects such as tables, views,
clusters, and indexes. The partitioned table, which is new in Oracle8, is covered
tomorrow. Also new in Oracle8 is the index only table, which is covered in the next
three lessons.</P>
<P>The Oracle schema is a very important concept to both the Oracle DBA and to the
application developer. The next three chapters finish the basics on building an Oracle
database.
<H2><FONT COLOR="#000077"><B>Q&amp;A</B></FONT></H2>

<DL>
	<DD><B>Q What are the server processes?</B>
	<P><B>A</B> The server processes are the background processes or threads that perform
	tasks on behalf of Oracle. They consist of the following: DBWR, LGWR, PMON, SMON,
	RECO, ARCH, CKPT, LCK<I>n</I>, and D<I>nnn</I>.</P>
	<P><B>Q What is a dedicated server process?</B></P>
	<P><B>A</B> A dedicated server process has a one-to-one correlation between the user
	process and the server process. Each user process gets one server process.</P>
	<P><B>Q What is a shared server process?</B></P>
	<P><B>A</B> The shared server process handles more than one user process. The dispatcher
	queues the job and the shared server process executes it.</P>
	<P><B>Q What is the Parallel Query option?</B></P>
	<P><B>A</B> The Parallel Query option is an Oracle option that allows large database
	operations to be parallelized and run simultaneously.
</DL>

<H2><FONT COLOR="#000077"><B>Workshop</B></FONT></H2>
<P>The workshop provides quiz questions to help you solidify your understanding of
the material covered and exercises to provide you with experience in using what you've
learned. Find the answers in Appendix A, &quot;Answers.&quot;
<H3><FONT COLOR="#000077"><B>Quiz</B></FONT></H3>

<DL>
	<DD><B>1. </B>What is the DBWR process used for?
	<P><B>2.</B> What is the LGWR process used for?</P>
	<P><B>3.</B> What is the CKPT process used for?</P>
	<P><B>4.</B> Under what circumstances will the LCK<I>n</I> process be used?</P>
	<P><B>5.</B> What type of system includes a Transaction Monitor (TM)?</P>
	<P><B>6.</B> What component is usually the limiting factor in your system?</P>
	<P><B>7.</B> What is the order of precedence (that is, parameters, hints, table definitions,
	and so on) for Oracle choosing the degree of parallelism?</P>
	<P><B>8. </B>Does the Parallel Query option help you only if you have multiple processors?</P>
	<P><B>9. </B>With Oracle on NT, does the Oracle server use processes or threads?</P>
	<P><B>10. </B>Where does the multithreaded server get its memory from?
</DL>

<H3><FONT COLOR="#000077"><B>Exercises</B></FONT></H3>

<DL>
	<DD><B>1. </B>Use the Instance Manager to see how many sessions are active in your
	system.
	<P><B>2.</B> Using perfmon, look at the Oracle8 threads.</P>
	<P><B>3. </B>Try running a table scan both with and without the Parallel Query option.</P>
	<P><B>4.</B> Compare the disk activity with and without the Parallel Query option.
</DL>

<CENTER>
<P>
<HR>
<A HREF="../ch10/ch10.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch12/ch12.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> <BR>
<BR>
<BR>
<IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>

<P>&#169; <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>


</BODY>

</HTML>