ch11.htm

Teach yourself Oracle8 in 21 day

<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>Because the multithreaded server
	uses the shared pool for queuing requests and returning data, it is important that
	the shared pool be large enough. 
<HR>


</BLOCKQUOTE>

<P>The main advantage of using the multithreaded server is the reduction of server
processes. This can greatly reduce CPU and memory usage. As you might guess, however,
the multithreaded server does add overhead to the system. This is why a dedicated
server process is recommended for long-running batch jobs.
<H4><FONT COLOR="#000077"><B>Tuning the Multithreaded Server</B></FONT></H4>
<P>To configure and tune the multithreaded server for use, you'll need to tune the
following parameters in your parameter file. You should also monitor the shared pool
very carefully to make sure you are not running out of space.</P>
<P>Try monitoring the shared session memory with a small number of users to determine
how much memory they are using. You can then extrapolate how much memory all the
sessions will require. This can be accomplished by using the following SQL statement:</P>
<PRE><FONT COLOR="#0066FF">SELECT SUM(value) || `bytes' &quot;Memory&quot;
FROM v$sesstat, v$statname
WHERE name = `session memory'
AND v$sesstat.statistic# = v$statname.statistic#;
</FONT></PRE>
<P>This tells you how much memory you are using. Dividing this by the number of connections
tells you the amount of memory per session. You can determine from this how much
memory you'll need in the shared pool for all the sessions you'll be supporting.</P>
<P>If you think the shared-pool size might be too small, you can increase it by tuning
the parameter <TT>SHARED_POOL_SIZE</TT>. Remember, the shared pool is also used for
the library cache and the data dictionary. Learn to tune these on Day 20, &quot;Effectively
Tuning and Optimizing the Database.&quot;</P>
<P>The number of dispatchers per protocol is determined by the initialization parameter
<TT>MTS_DISPATCHERS</TT>. If you increase this number, each session will potentially
see greater performance because it will not have to wait on a dispatcher to become
available. Here is an example of how you would set five dispatcher processes for
the TCP/IP protocol:</P>
<PRE><FONT COLOR="#0066FF">MTS_DISPATCHERS = &quot;TCP, 5&quot;
</FONT></PRE>
<P>Each network protocol is set separately. For protocols with fewer users, set the
number lower. The more concurrent sessions per dispatcher, the greater the possibility
you'll have to wait for a dispatcher when you need one.</P>
<P>Other parameters related to the multithreaded server are

<UL>
	<LI><TT>MTS_MAX_DISPATCHERS</TT>--The maximum number of dispatcher processes that
	can be created in the instance. This includes the dispatchers for all protocols combined.
	<P>
	<LI><TT>MTS_SERVER</TT>--The initial number of shared server processes. If this value
	is set to <TT>0</TT>, Oracle will not use shared server processes. The number of
	shared server processes will increase dynamically to meet the needs of the system.
	<P>
	<LI><TT>MTS_MAX_SERVER</TT>--This value specifies the maximum number of shared server
	processes.
</UL>

<P>The number of dispatcher processes and the minimum number of shared server processes
can be changed dynamically with the <TT>ALTER SYSTEM</TT> parameter.
<H2><FONT COLOR="#000077"><B>The Oracle Parallel Query Option</B></FONT></H2>
<P>The Oracle Parallel Query option is by far my favorite enhancement that Oracle
has made in the last few years. By using the Parallel Query option, you can greatly
improve the performance of some database operations.</P>
<P>The Oracle Parallel Query option makes it possible for some Oracle functions to
be processed by multiple streams of execution. These functions include some queries,
index creation, data loading, and recovery. In each of these functions, the general
principle is the same: Keep the CPUs busy while Oracle is waiting for I/O.</P>
<P>For most queries, the time spent waiting for the data to be retrieved from disk
usually overshadows the amount of time spent processing the results. With the Parallel
Query option, you can compensate for this by using several server processes to execute
the query. While one process is waiting for I/Os to complete, other processes can
be executing.</P>
<P>Many processes working together can simultaneously process a single SQL statement,
a situation known as parallel query processing. The other functions are known as
parallel index creation, parallel loading, and parallel recovery, each of which is
discussed in the following sections.
<H3><FONT COLOR="#000077"><B>Parallel Query Processing</B></FONT></H3>
<P>Parallel query processing allows certain Oracle statements to be run in parallel.
The Oracle server can process the following statements in parallel:

<UL>
	<LI><TT>SELECT</TT> statements
	<P>
	<LI>Subqueries in <TT>UPDATE</TT> and <TT>DELETE</TT> statements
	<P>
	<LI><TT>CREATE TABLE</TT>tablename<TT> as SELECT</TT> statements
	<P>
	<LI><TT>CREATE INDE</TT> statements
</UL>

<P>Parallel queries are effective on large operations such as table scans and sorts.
<H4><FONT COLOR="#000077"><B>Parallel Query Operation</B></FONT></H4>
<P>With traditional queries such as table scans, the server process reads the data
sequentially, as shown in Figure 11.8. Much of the time spent in this query is spent
waiting for I/Os to complete.</P>
<P><A NAME="08"></A><A HREF="08.htm"><B>Figure 11.8.</B></A></P>
<P><I>A table scan without parallel query.</I></P>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>A parallel query divides
the query into several different pieces, each processed by a different server process.
These processes are called <I>query servers</I>. The query servers are dispatched
by a process known as the <I>query coordinator</I>. The query coordinator dispatches
the query servers and coordinates the results from all the servers to send back to
the user. The result of this arrangement is that many smaller table scans take place
under the hood (transparent to the user). From the user's standpoint, it is simply
a much faster table scan. Figure 11.9 shows a parallel query.</P>
<P><A NAME="09"></A><A HREF="09.htm"><B>Figure 11.9.</B></A></P>
<P><I>A parallel query table scan.</I></P>
<P>The query coordinator is given a SQL statement and a degree of parallelism, and
is responsible for dividing the query among the query servers and integrating the
individual results into one result. The degree of parallelism is the number of query
servers assigned to the particular query.</P>
<P>The Oracle server can make parallel the following operations:

<UL>
	<LI>Joins
	<P>
	<LI>Sorts
	<P>
	<LI>Table scans
</UL>

<P>Each of these operations has requirements that determine how the query is parallelized.
The performance achieved by the parallel query is determined by the amount of data
to be accessed and the degree of parallelism achieved.</P>
<P>How the query is parallelized (if at all) is determined by the query coordinator.
The decision is made in this order:

<DL>
	<DD><B>1. </B>The optimizer determines the execution plan of the statement.
	<P><B>2. </B>The query coordinator determines which operations can be performed in
	parallel.</P>
	<P><B>3. </B>The query coordinator determines how many query servers to enlist.</P>
	<P><B>4. </B>The query coordinator enlists query servers that perform the query.</P>
	<P><B>5. </B>The query coordinator reassembles the resulting data and passes it back
	to the user.
</DL>

<P>The degree of parallelism is determined using the following precedence:

<DL>
	<DD><B>1. </B>Query hints--User-defined hints included in the SQL statement have
	the highest precedence.
	<P><B>2. </B>Table definition--The default degree of parallelism defined for the
	table has second precedence.</P>
	<P><B>3. </B>Initialization parameters--The Oracle initialization parameters are
	used.
</DL>

<P>Regardless of how these values are set, the number of query servers cannot exceed
the number of query servers available in the query server pool. This number is specified
by the Oracle initialization parameter <TT>PARALLEL_MAX_SERVERS</TT>.</P>
<P>Hints for the degree of parallelism are set within a comment string in the SQL
statement.
<H4><FONT COLOR="#000077"><B>The Syntax for Parallelism in a Comment String</B></FONT></H4>
<P>The syntax of this comment is as follows:
<H4><FONT COLOR="#000000"><B>SYNTAX:</B></FONT></H4>
<PRE><FONT COLOR="#0066FF">PARALLEL ( alias_or_tablename , [ integer/DEFAULT ] [ , integer/DEFAULT ] )
</FONT></PRE>
<P>The parallel hint specifies the table or alias being scanned, followed by a value
for the number of query servers to be used (or the default). The final optional value
specifies how the table is to be split among different instances of a parallel server.
Here is an example of using hints:</P>
<PRE><FONT COLOR="#0066FF">SELECT /*+ FULL(emp) PARALLEL(emp, 4) */
empno
FROM emp;
</FONT></PRE>
<P>If you add the <TT>FULL</TT> and <TT>PARALLEL</TT> hints to this statement, the
Oracle optimizer will create an execution plan that uses a full-table scan. Furthermore,
this table scan will be executed with a parallel degree of <TT>4</TT> if the query
servers are available. This statement overrides both the degree of parallelism specified
in the table definition and the default Oracle initialization parameters.</P>
<P>The hint <TT>NOPARALLEL</TT> disables parallel scanning of a table and overrides
the specified degree of parallelism.
<H4><FONT COLOR="#000077"><B>The Syntax for the </B>NOPARALLEL<B> Hint</B></FONT></H4>
<P>The <TT>NOPARALLEL</TT> hint has the following syntax:
<H4><FONT COLOR="#000000"><B>SYNTAX:</B></FONT></H4>
<PRE><FONT COLOR="#0066FF">NOPARALLEL ( alias_or_tablename )
</FONT></PRE>
<H4><FONT COLOR="#000077"><B>Parallel Query Tuning</B></FONT></H4>
<P>Parallel query operations can be very effective on multiprocessor or parallel-processing
computers; they can also be effective on uniprocessor systems where much of the time
is spent waiting for I/O operations to complete. Systems with sufficient I/O bandwidth--and
especially systems with disk arrays--benefit from parallel query operations.</P>
<P>If your system is typically processing at 100% of its CPU utilization and you
have a small number of disk drives, you probably won't benefit from parallel query
operations. If your system is extremely memory limited, you also will probably not
benefit from parallel query operations.</P>
<P>The two areas that can be tuned for parallel queries are I/O and parallel servers.
By properly configuring your datafiles, you can help parallel queries be more effective.</P>
<P><FONT COLOR="#000077"><B>I/O Configuration</B></FONT></P>
<P>The function of a parallel query is to split up query operations so they more
effectively use the system. One of the ways a parallel query does this is by allowing
the processing of the query to continue while pieces of the query operation are stalled
waiting for I/Os to complete. Parallel queries are not effective if the entire table
is limited to one disk drive.</P>
<P>By striping the table across many drives, I/Os can be distributed and a higher
level of parallelism can occur. Striping can be done with OS striping, with Oracle
striping, or (better yet) with a hardware disk array.</P>
<P>Large contiguous extents can also help performance in parallel query operations.
During scan operations, the query coordinator splits contiguous ranges of blocks
into large, medium, and small groups. Each query server is given a large group of
blocks to start with, progressively working its way down to the small group of blocks
until the scan is completed. This is done in an attempt to balance the load performed
by each query server. If there are several large extents in a table, the query coordinator
can find blocks to dispatch to the query servers much more easily.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>Remember to compose your temporary
	tablespace of several large extents on a striped volume. This arrangement helps sorting
	performance. 
<HR>