 |
|
Monitoring for Server Process Contention
Oracle Tips by Burleson Consulting
|
We have discussed monitoring the dispatchers,
now lets turn our attention to the shared servers. An example report
using the V$SHARED_SERVER view is shown in Source 14.3. If the percent
busy for all servers is greater than 50% and the number of server
processes is equal to the initialization parameter MAX_SHARED_SERVERS
then the MAX_SHARED_SERVERS parameter needs to be increased. If the
value is less than MAX_SHARED_SERVERS then the Oracle kernel should
automatically start more server processes up to the value of
MAX_SHARED_SERVERS. If you feel the need to manually start more server
processes you can use the command:
ALTER SYSTEM
SET SHARED_SERVERS=n
Where n is the current number of processes
plus the number you which to add.
SOURCE 14.3 Script to Monitor Shared Server
Processes
rem
rem Name: mts_serv.sql
rem Function: generate percent busy report for shared servers
rem History: MRA 11/24/01 Verified and formatted for Oracle9i
rem
COL name FORMAT a4 HEADING 'Name'
COL busy FORMAT 999.99 HEADING 'Percent|Busy'
COL status FORMAT a13 HEADING 'Server Status'
COL messages HEADING 'Meassages'
COL bytes HEADING 'Bytes'
COL requests HEADING 'Requests'
rem
SET feedback OFF VERIFY OFF LINES 78 PAGES 58
START title80 'Server Status'
SPOOL rep_out\&db\mts_disp
rem
SELECT name,
((SUM(busy)/(SUM(busy)+SUM(idle)))*100) busy,
MAX(status) status, MAX(messages) messages,
MAX(bytes) bytes, MAX(requests) requests
FROM v$shared_server
GROUP BY name;
rem
SPOOL OFF
SET feedback ON VERIFY ON LINES 22
TTITLE OFF
An example output from Source 14.3 is shown in
Listing 14.6.
LISTING 14.7 Example Shared Server Report
Date:
11/25/01 Page: 1
Time: 12:01 PM Server Status SYSTEM
galinux1 database
Percent
Name Busy Server Status Meassages Bytes Requests
---- ------- ------------- --------- --------- ---------
S000 .00 WAIT(COMMON) 0 0 0
Monitoring and Configuring MTS
Other than the scripts shown above I haven't
found a tool that allows you to monitor the MTS system from Oracle.
Several third party tools such as Precise*Q Monitor provide this
functionality. The Database Configuration Assistant (DBCA) allows
configuration of the Shared Server Mode if you select the Configure
database options in a database option from the top level screen. Once
you have selected an instance from the pick list you get a set of
available options, all will be gray, choose the next button and select
the Shared Server Mode option and then click on the Edit Shared
Connections Parameters button.
Once the Shared Server Mode edit box is
displayed you can choose from TCP, IPC or TCPS protocols and then
select the initial number of dispatchers, connections per dispatcher,
max number of dispatchers, initial number of servers and max number of
servers from the Basic edit tab. From the advanced edit tab you can
configure multiplexing, connection pooling and maximum number of
network sessions. Figure 14.5 shows the basic edit screen for the MTS
(now to be called shared server) option.
Figure 14.5 MTS Configuration Screen From DBCA
Before attempting a manual configuration of
Oracle Shared Server (the option formerly known as MTS) I suggest you
consult the Oracle Database Administrators Guide for detailed
instructions.
Overview of Parallel Server
Oracle parallel server or OPS for short has
undergone extreme changes in Oracle9i. Other than bug fixes not much
happened with OPS from version 7 where it was introduced up to
Oracle8i. In Oracle8i the first foundations of the Real Applications
Clusters was laid and finally with Oracle9i they were realized.
Real Application Clusters (RAC) Overview
Oracle has had the ability to run in parallel
server mode since versions 7. In Oracle parallel server mode means
that two or more Oracle instances share a single set of database
files. An instance of Oracle is the executable image, background
processes, instance-specific initialization file, control files, redo
log thread and a set of either shared or private rollback segments. A
shared database consists of the datafiles that make up all tablespaces
in the database. Until later versions of Oracle8i a shared Oracle
database system had to use a laborious process of copying blocks into
and out of memory and to and from disk in order to share information
in a single block between the multiple instances. This complex sharing
mechanism resulted in performance issues if the database itself didn't
practice data partitioning. Oracle9i relieves Remote DBAs and designers from
these limitations and problems.
Benefits of RAC
In the beginning was Oracle Parallel Server or
OPS, now we have Real Application Clusters or RAC. It is more than
just a name change. RAC provides a process known asw cache-fusion
which allows direct sharing of Oracle database blocks between cache
areas of Oracle instances participating in RAC via a high speed
interconnect. This direct sharing eliminates the biggest performance
robbers in the OPS architecture, the DB block PING.
A DB block PING would occur when an instance
participating in an OPS database would have a block in its cache that
another participating instance required. In OPS if another instance
required the block in the cache of a second instance, the block would
have to written out to disk, the locks transferred and then the block
re-read into the requesting instance. As you can imagine all of this
IO was expensive in terms of time and performance.
The new RAC with cache fusion exploits the new
high speed buses and architectures available in the new servers. By
eliminating the blockage caused by pinging, RA enables better, more
efficient scaling. In order to add more processing or user capacity
you simply add a server to the LAN, load the appropriate software and
Oracle9i and you are up and operating.
What Is a DB Cluster?
A DB cluster consists of a group of
independent servers, connected via a LAN that share a set of disk
assets via the LAN and have a high speed cluster interconnect that
allows connection of their processors to the other processors in the
cluster. The clusters do not share memory and act as independent
servers. The servers can either be single processor or SMP (Symmetric
Multi-Processor) nodes. A DB cluster provides fault-tolerance and
allow for modular growth. By having the built in redundancies of
multiple servers the chance that a single point of failure will
prevent database access is unlikely.
DB Cluster Architectures
Essentially there are only two basic
architectures used in DB clusters, these are the shared-nothing and
shared-disk. The shared-nothing cluster uses dedicated disk assets for
each server in the cluster. The shared-disk cluster uses a SAN type
storage array to serve the cluster.
Examples of systems that use the shared
nothing architecture are the IBM RS/6000 SP and the federated database
approach used by SQL Server. This architecture is rapidly losing
ground to the shared-disk architecture.
The shared-disk architecture is being adopted
almost 100% by IBM, SUN and HP and other vendors. It offers better
redundancy, and , better connectivity through technologies such as
fiber-channel, hub and shared SCSI.
What Is a Cluster Interconnect?
The cluster interconnect is a high-speed
(usually optical fiber) connection directly between the servers
involved in a DB cluster. The bandwidth is usually large in a cluster
interconnect (unlike the previous Ethernet or other connections used
in early architectures) thus allowing transfers of blocks instead of
just latch and lock information. This high-speed connection allows
direct transfer of blocks eliminating the PING method of block
transfer to and from disk.
Oracle's cache fusion process utilizes the
high speed cluster interconnect to form a virtual db block cache that
is the combination of the entire db block caches of all of the
participating Oracle9i instances in the RAC.
Problems with Other Architectures
Other architectures for database clusters use
either the shared-nothing disk setup, or use a database setup that
restricts the database in the areas of referential integrity and ease
of use. Let's examine the major vendor implementations.
See
Code Depot for Full Scripts
 |
This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
