BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation








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:


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 Name: mts_serv.sql
rem Function: generate percent busy report for shared servers
rem History: MRA 11/24/01 Verified and formatted for Oracle9i
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'
START title80 'Server Status'
SPOOL rep_out\&db\mts_disp
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;

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

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.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter