ch19.htm

Teach yourself Oracle8 in 21 day

<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 19.2. Much of the time spent in this query is spent
waiting for I/Os to complete.</P>
<P><A NAME="02"></A><A HREF="02.htm"><B>Figure 19.2.</B></A></P>
<P><I>A table scan performed without the use of the Parallel Query option.</I></P>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>A parallel query splits the
query into several pieces, each handled by a different server process. These processes,
called <I>query servers</I>, 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 19.3 shows a
parallel query.</P>
<P><A NAME="03"></A><A HREF="03.htm"><B>Figure 19.3.</B></A></P>
<P><I>A table scan performed with the use of the Parallel Query option.</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 both by the size of
the data to be accessed and the degree of parallelism achieved.</P>
<P>The query coordinator determines how the query is parallelized (if at all). 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 as defined for the
	table has second precedence.</P>
	<P><B>3.</B> Initialization parameters--Finally, the Oracle initialization parameters
	are used.
</DL>

<P>Regardless of what these values are set to, 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. The syntax of this comment is as follows:</P>
<PRE><FONT COLOR="#0066FF">PARALLEL (alias_or_tablename , [integer/DEFAULT] [, integer/DEFAULT])
</FONT></PRE>
<P>The <TT>PARALLEL</TT> hint specifies the table or alias being scanned, followed
by a value for the number of query servers to be used (or <TT>DEFAULT</TT>). The
final optional value specifies how the table is to be split among different instances
of a parallel server. Here is an example using the <TT>dogs</TT> table that you have
seen throughout this book:</P>
<PRE><FONT COLOR="#0066FF">SELECT /*+ FULL(dogs) PARALLEL(dogs, 4) */
dogname
FROM dogs;
</FONT></PRE>
<P>Because of the addition of 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. The <TT>NOPARALLEL</TT> hint has the following
syntax:</P>
<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 your CPU utilization and you
have a small number of disk drives, you probably won't benefit from parallel query
operations. The same is true if your system is extremely limited in memory.</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 that they
more effectively take advantage of 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>If you stripe the table across many drives, I/Os can be distributed and a higher
level of parallelism can occur. Striping can be performed with OS striping, with
Oracle striping, or (better yet) with a hardware disk array. See Day 20, &quot;Effectively
Tuning and Optimizing the Database,&quot; for more detailed information about tuning
I/O.</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 of blocks. Each query server is given a large
group of blocks to start with, and each server progressively works its way down to
the small group of blocks until the scan is completed. This is done in an attempt
to balance the load handled by each query server. If several large extents are 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, your temporary tablespace
	should consist of several large extents on a striped volume. This arrangement helps
	sorting performance. 
<HR>


</BLOCKQUOTE>

<P><FONT COLOR="#000077"><B>Degree of Parallelism</B></FONT></P>
<P>The proper distribution of I/Os and the degree of parallelism are the two most
important things to tune in the Parallel Query option. Tuning the degree of parallelism
is partially trial and error and partially analysis. It is very important to take
notes when you are experimenting with the degree of parallelism. Your first guess
should be based on the following factors:

<UL>
	<LI>The CPU capacity of your system--The number and capacity of CPUs affects the
	number of query processes you should run.
	<P>
	<LI>The capacity of the system to handle large numbers of processes--Some operating
	systems can handle many simultaneous threads; others are more limited.
	<P>
	<LI>The system load--If the system is already running at 100% capacity, the degree
	of parallelism doesn't have much effect. If you are running at 90%, too many query
	processes can overload the system.
	<P>
	<LI>The amount of query processing on the system--If most operations are updates
	but there are a few critical queries, you might want many query processes.
	<P>
	<LI>The I/O capacity of the system--If your disks are striped or if you are using
	a disk array, you should be able to handle a large number of parallel queries.
	<P>
	<LI>The types of operations--Are you performing many full-table scans or sorts? These
	operations benefit greatly from parallel query servers.
</UL>

<P>All these parameters should have some influence on the degree of parallelism you
set for your system. Remember that the preceding points are simply guidelines to
help you guess a starting point. Here are a few other suggestions:

<UL>
	<LI>CPU-intensive operations such as sorts should indicate a lower degree of parallelism.
	CPU-bound tasks are already taking advantage of the CPUs and tend not to be waiting
	for I/O.
	<P>
	<LI>Disk-intensive operations such as full-table scans should indicate a higher degree
	of parallelism. The more operations waiting for I/O, the more the system can benefit
	from another query server.
	<P>
	<LI>Many concurrent processes should indicate a lower degree of parallelism. Too
	many processes can overload the system.
</UL>

<P>Once you determine your starting point, you can monitor your system by querying
the dynamic performance table, <TT>V$PQ_SYSSTAT</TT>. This can be performed with
the query shown in Listing 19.1.<BR>
<BR>
<FONT COLOR="#000000"><B>INPUT:</B></FONT></P>
<H4><FONT COLOR="#000077"><B>Listing 19.1. Viewing parallel query performance data
from </B>V$PQ_SYSSTAT<B>.</B></FONT></H4>
<P><FONT COLOR="#0066FF"><TT>SQL&gt; select * from v$pq_sysstat;</TT></FONT></P>
<PRE><FONT COLOR="#0066FF"><TT></TT></FONT></PRE>
<P><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></P>
<PRE><FONT COLOR="#0066FF">STATISTIC                           VALUE
------------------------------ ----------
Servers Busy                            0
Servers Idle                           12
Servers Highwater                      16
Server Sessions                       380
Servers Started                         4
Servers Shutdown                        4
Servers Cleaned Up                      0
Queries Initiated                      21
DFO Trees                              77
Local Msgs Sent                   2459361
Distr Msgs Sent                         0
Local Msgs Recv'd                 2459318
Distr Msgs Recv'd                       0

13 rows selected.
</FONT></PRE>
<P><B>ANLYSIS: </B>When looking at the output from this query, the following statistics
are quite useful:

<UL>
	<LI><TT>Servers Busy</TT>--This indicates the number of servers busy at any one time.
	Check this statistic several times to get a good idea of the average value. If the
	value is equal to the initialization parameter <TT>PARALLEL_MIN_SERVERS</TT>, you
	have probably configured too many query servers.
	<P>
	<LI><TT>Servers IdlE</TT>--This indicates the number of servers idle at any one time.
	If you always have many idle servers, consider reducing <TT>PARALLEL_MIN_SERVERS</TT>.
	<P>
	<LI><TT>Servers Starter</TT>--This indicates the number of query servers that have
	started in this instance. If the value for <TT>Servers Busy</TT> is low but you see
	a large number for <TT>Servers Started</TT>, you might be using query servers sporadically.
	<P>
	<LI><TT>Servers Shutdown</TT>--This indicates the number of query servers that have
	been shut down because they are idle. This value is most likely similar to the <TT>Servers
	Started</TT> value.
</UL>

<P>After you determine your degree of parallelism, begin testing; evaluate the information
you get from <TT>V$PQ_SYSSTAT</TT> and from your operating system's monitoring facilities.
Keep an eye out for CPU usage and excessive waiting for I/O. If the CPU usage is
too high, try reducing the degree of parallelism. If the CPU usage is too low and
there is significant waiting for I/O, try increasing the degree of parallelism.</P>
<P>Remember that the degree of parallelism is determined by SQL hints, table definitions,
and initialization parameters. The total number of query servers is determined by
the initialization parameter <TT>PARALLEL_MAX_SERVERS</TT>; the number started initially
is determined by the initialization parameter <TT>PARALLEL_MIN_SERVERS</TT>.</P>