📄 ch19.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself Oracle 8 In 21 Days -- Ch 19 -- Advanced Oracle Options</TITLE>
</HEAD>
<BODY TEXT="#000000" BGCOLOR="#FFFFFF">
<CENTER>
<H1><IMG SRC="../buttonart/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR>
<FONT COLOR="#000077">Teach Yourself Oracle 8 In 21 Days</FONT></H1>
</CENTER>
<CENTER>
<P><A HREF="../ch18/ch18.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch20/ch20.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>
<HR>
</CENTER>
<CENTER>
<H1><FONT COLOR="#000077">- Day 19 -<BR>
Advanced Oracle Options</FONT></H1>
</CENTER>
<P>Today you will learn about some of the advanced options available to Oracle, including
the Oracle Parallel Server option (OPS) and the Oracle Parallel Query option. Although
these options sound as though they would be similar to each other, they have no correlation.</P>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>The Oracle Parallel Server
option, an add-on feature, allows more than one instance of Oracle to share the same
database; two or more systems can simultaneously access the same data, providing
a performance boost and failover capabilities where one node can take over for another
in the event of a failure. This linking of systems to form one larger system has
traditionally been called a <I>cluster</I>.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The term <I>cluster</I> has historically
been used to identify a large system that performs a single task and is made up of
two or more smaller systems working in tandem.</P>
<P>Numerous vendors have recently introduced "clustering" systems that
consist of an offline standby node or another node running a completely different
application that can take over some tasks in the event the primary node fails. In
my opinion, this is not a clustering solution. Because vendors are piggybacking on
cluster terminology, the end-user is being deceived. Vendors such as Oracle, Tandem,
DEC, and others have had true clusters for many years and are being cheated by these
standby systems.<BR>
<BR>
My rule of thumb is that if you cannot access all data from all nodes all the time,
it is not a cluster.
<HR>
</BLOCKQUOTE>
<P>The Oracle parallel server cluster has been available for many years, but has
recently been introduced on the NT platform. If you use the features of the Parallel
Server option, both performance and system uptime can be improved.</P>
<P>The Oracle Parallel Query option is not a clustering option; it has nothing to
do with clustering (except that you can use it on a cluster). This option allows
certain SQL operations to be parallelized. By parallelizing these operations, you
can improve performance by utilizing the time the system waits for I/O operations
to complete.</P>
<P>The Parallel Query option can provide a good-to-outstanding performance improvement
over traditional query operations. Today you will learn how to use the Parallel Query
option as well as how to tune your application to better perform with the Parallel
Query option.
<H2><FONT COLOR="#000077"><B>The Parallel Server Option</B></FONT></H2>
<P>The Parallel Server option is one of the most innovative and impressive options
available from Oracle. With this option you can cluster several computers using a
shared-disk subsystem and have multiple Oracle instances access the same database,
as shown in Figure 19.1. If your application is suitable, you can see very good scalability
by adding extra computers.</P>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>The Oracle Parallel Server
option uses a sophisticated locking mechanism in conjunction with a shared-disk subsystem
to allow multiple instances to access the same data. Communication between the computers
occurs through a <I>server interconnect</I>, which usually consists of high-speed
network accesses at a very low level. Using the traditional network stack does not
provide the performance required for a server interconnect.</P>
<P><A NAME="01"></A><A HREF="01.htm"><B>Figure 19.1.</B></A></P>
<P><I>A parallel-server configuration.</I></P>
<P>
<H3><FONT COLOR="#000077"><B>Server Interconnect</B></FONT></H3>
<P>The server interconnect provides two functions: to communicate locking information
and to act as a system heartbeat. The system heartbeat communicates to other systems
in the cluster that the server is still operational. If the heartbeat message does
not arrive, other servers in the cluster assume that the system is nonfunctional
and roll back transactions that have not been committed.</P>
<P>The shared-disk subsystem allows all members of the cluster to access all shared
data in the cluster. This disk subsystem must be shared and should be available at
all times, regardless of the state of any particular server. This subsystem can use
RAID and data caching as long as both operations are performed on the disk side of
the shared channel rather than on the controller side. Because the release of a lock
sometimes depends on data being written out to the shared disk, the more performance
you have, the better off you are.
<H3><FONT COLOR="#000077"><B>Locking</B></FONT></H3>
<P>Locking is performed with a process called the Distributed Lock Manager (DLM).
The DLM is responsible for locking data that is being modified so that the data cannot
be modified in another instance. Locking ensures data integrity across the entire
cluster. A data block or group of blocks is locked until another instance needs that
data.</P>
<P>If you can partition your users so that users accessing data in a particular table
all use the same instance to access that data, you will have reduced lock contention.
You can enhance performance by carefully partitioning the data and the users. If
you partition the data into update-intensive and read-intensive tables, you will
also benefit.</P>
<P>At instance startup, a number of Parallel Cache Management (PCM) locks are created.
PCM locks lock data blocks being accessed within each instance to guarantee that
multiple instances do not alter the same data.</P>
<P>You can use PCM locks to lock data blocks for reading or for updating. If a PCM
lock is used as a read-lock, other instances can acquire read-locks on the same data
blocks. It is only when updating that an exclusive lock must be acquired.</P>
<P>PCM locks are allocated to datafiles; as such, they give you some flexibility
over the configuration of the locks. A PCM lock locks one or more data blocks, depending
on the number of PCM locks allocated to the datafile and the size of the datafile.
Because an inherent overhead is associated with PCM locks, it is not beneficial to
overconfigure the locks.</P>
<P>If you know your data-access patterns, you can configure your system based on
these general rules:
<UL>
<LI>Partition work between servers. Try to balance the systems so that users accessing
the same table reside on the same computer. This arrangement reduces lock contention
between machines. By segmenting the work, you can reduce the amount of lock traffic.
Remember that once a lock is acquired, it is released only when another system needs
to lock that data.
<P>
<LI>Put lots of PCM locks on tables with heavy update traffic. If you have lots of
updates, you can benefit from lowering the blocks-per-lock ratio. By increasing the
number of locks, you increase overhead--but by having fewer blocks per lock, you
can cut down on the percentage of locks with contention.
<P>
<LI>Use <TT>PCTFREE</TT> and <TT>PCTUSED</TT> to specify fewer rows per block on
high-contention tables. By doing this and decreasing the number of blocks per lock,
you reduce the lock contention--at the cost of more locks and more space required.
<P>
<LI>Put fewer locks on read tables. If you have tables that are mostly read, use
fewer PCM locks. Read locks are not exclusive; the reduction in locks cuts down on
interconnect traffic.
<P>
<LI>Partition indexes to separate tablespaces. Because indexes are mostly read, you
can benefit by requiring fewer PCM locks. By segmenting the tables, you can put fewer
PCM locks on the index tables and more on the data tables.
</UL>
<P>The dynamic performance tables <TT>V$BH</TT>, <TT>V$CACHE</TT>, and <TT>V$PING</TT>
contain information about the frequency of PCM lock contention. By looking at the
<TT>FREQUENCY</TT> column in these tables, you can get an idea of the number of times
lock conversions took place because of contention between instances.</P>
<P>The dynamic performance table, <TT>V$LOCK_ACTIVITY</TT>, provides information
about all types of PCM lock conversions. From this information you can determine
whether a particular instance is seeing a dramatic change in lock activity. An increase
in lock activity might indicate that you have an insufficient number of PCM locks
on that instance. With this information, you can use the <TT>V$BH</TT>, <TT>V$CACHE</TT>,
and <TT>V$PING </TT>tables to identify the problem area.</P>
<P>The Parallel Server option can be effective if your application is partitionable.
If all the users in your system must access the same data, a parallel server might
not be for you. But if you can partition your workload into divisions based on table
access or if you need a fault-tolerant configuration, the Parallel Server option
may work.</P>
<P>If you use the Parallel Server option, you must take special care to properly
configure the system. By designing the system properly, you can take maximum advantage
of the parallel server features.
<H2><FONT COLOR="#000077"><B>The Parallel Query Option</B></FONT></H2>
<P>The Oracle Parallel Query option makes it possible for some Oracle functions to
be processed by multiple server processes. These functions are queries, index creation,
data loading, and recovery. In each of these functions, the general principle is
the same: Keep processing while Oracle waits 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 actually 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. If you are running on a Symmetric Multiprocessor (SMP) computer,
a cluster, or a Massively Parallel Processing (MPP) machine, you can take maximum
advantage of the Parallel Query option.</P>
<P>Many different types of SQL functions can take advantage of the Parallel Query
option, including
<UL>
<LI>Aggregatations
<P>
<LI>The <TT>CREATE INDEX</TT> function
<P>
<LI>The <TT>CREATE TABLE AS SELECT</TT> function
<P>
<LI>The <TT>DELETE</TT> function
<P>
<LI>The <TT>GROUP BY</TT> function
<P>
<LI>Hash joins
<P>
<LI>The instance recovery operation
<P>
<LI>Nested loop joins
<P>
<LI>The <TT>ORDER BY</TT> function
<P>
<LI>Sort merge joins
<P>
<LI>Table scans
<P>
<LI>Union operations
<P>
<LI>The <TT>UPDATE</TT> function
</UL>
<P>These operations and several others will automatically be parallelized based on
the database-initialization parameters and table definitions.
<H3><FONT COLOR="#000077"><B>Parallel Query Processing</B></FONT></H3>
<P>Parallel query processing allows certain Oracle statements to be run in parallel
by multiple server processes. 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 INDEX</TT> statements
</UL>
<P>Parallel queries are effective on large operations such as table scans and sorts.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -