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








Client Configuration

Oracle Tips by Burleson Consulting

An Oracle client can be one of two basic types, either they are connecting directly to the database through an Oracle Net connection or they are connecting through some server, whether it be an application, forms or reports server through a Net Browser such as Navigator or Internet Explorer for example.

Use of Oracle Applications may also require the downloading and installation of the Jinitiator, a JAVA runtime environment, however, the Jinitiator requires little if any configuration. If the client will attach strictly through the internet or a company intranet then all that is needed is the capability to execute JAVA and the web interface, no further client configuration is required.

A normal Oracle net connection will require the installation of the appropriate protocol, usually TCPIP and installation and configuration of the Oracle Net client software formally called SQLNET or NET8. The configuration of the Oracle net client usually will consist of setting up the proper database addresses in the tnsnames.ora file. If you will be installing on several clients either carry a copy of the proper tnsnames.ora file with you on a floppy, or, have it readily available on the network someplace. Some shops use a centrally defined tnsnames.ora file for ease of administration.

Net-Based Connection

As was stated above the easiest of the client installations involves the net based connection. Essentially once the net browser (usually Internet Explorer is suggested) is installed, the Jinitiator installed and the address for the Oracle applications server loaded into the address listing for the browser, the configuration is complete.

Dedicated Connection

In a dedicated connection the client must have an application that can use Oracle Net, Oracle Net and a network transport protocol such as TCPIP. Once the required software is installed the Remote DBA edits or installs a proper tnsname.ora file in the ORACLE_HOME\network\admin directory and the user should be able to connect. You can test connectivity on a TCPIP network using the ping system utility and the network address or name of the server to see if the server can be reached from the current client. Once the Oracle Net application is installed and configured you can test connectivity using the tnsping utility and the database connection alias (service name).

In a dedicated connection the client requests a connection at the specified port (usually 1521) the listener process accepts the request, checks to see if it has the database in its list of instances and then passes the connection directly to the database. There are no special initialization parameters required for a dedicated connection.

Shared Server Connection

In a shared server or multithreaded server connection the listener passes the connection request to a dispatcher process that then passes it to one of several shared services in a round-robin least busy methodology. Multiple users share the same server process that connects to the database. For a shared server connection the configuration of the client is identical to the configuration of a dedicated client, the real changes are in the database initialization file.

Under multi-threaded server there are several server database initialization parameters that must be set properly for MTS to work. These parameters are:

MTS_CIRCUITS. This is a derived value from the value of SESSIONS, defaults to 0 if MTS not configured. The Oracle9i parameter is CIRCUITS and should be used as this form is deprecated. The CIRCUITS parameter controls the total number of virtual circuits allowed in the database.

MTS_DISPATCHERS. This parameter tells oracle the type and number of dispatchers to create for each network protocol. This parameter becomes DISPATCHERS in Oracle9i and this new form of the parameter should be used beginning with Oracle9i release 9.0.1.

MTS_MAX_DISPATCHERS. Sets the maximum number of dispatcher processes to be allowed to run concurrently. In Oracle9i this becomes MAX_DISPATCHERS, This defaults to 5 but there should be at least one dispatcher for each protocol defined in the MTS_DISPATCHERS parameter. The default value applies only if dispatchers have been configured for the system. The value of MAX_DISPATCHERS should at least equal the maximum number of concurrent sessions divided by the number of connections for each dispatcher. For most systems, a value of 250 connections for each dispatcher provides good performance.

MTS_MAX_SERVERS. Sets the maximum number of shared server processes. In Oracle9i this becomes MAX_SHARED_SERVERS and defaults to either 20 or to twice the value of SHARED_SERVERS (MTS_SERVERS). If artificial deadlocks occur too frequently on your system, you should increase the value of MAX_SHARED_SERVERS.

MTS_SERVERS. Sets the number of shared connection servers to start when the database is started. Defaults to one on a MTS configured system, 0 on a normal system. In Oracle9i this becomes SHARED_SERVERS. Oracle will automatically increase this value up to MAX_SHARED_SERVERS so unless you know you will need more than the default leave it set at the default.

MTS_SESSIONS. Defines the soft limit on MTS sessions, defaults to 5 less than the greater of CIRCUITS or SESSIONS. In Oracle9i you should use SHARED_SERVER_SESSIONS. This allows some dedicated connections to be reserved.

Monitoring Multithreaded Server

The Remote DBA will be tasked with monitoring the multi-threaded server dispatchers and servers and ensuring that there are adequate numbers of each to serve the database and its users. To this end the V$DISPATCHER, V$QUEUE, V$MTS, V$SHARED_SERVER, V$SHARED_SERVER_MONITOR, V$CIRCUIT and V$DISPATCHER_RATE views have been provided by Oracle, for their complete descriptions I will refer you to the Oracle Reference Manual. Let's look at a couple of reports that will give you a look into the MTS performance.

Monitoring Percent Busy

The Remote DBA needs to monitor the percent busy of the dispatcher processes. If they exceed 50% busy then more dispatchers should be started using the:


Command. You monitor percent busy using the V$DISPATCHER view. Source 14.1 shows and example report against the V$DISPATCHER view.

SOURCE 14.1 Example Dispatcher Percent Busy Report

rem Name: mts_disp.sql
rem Function: generate percent busy report for dispatchers
rem History: MRA 10/11/96 Created
rem          MRA 11/24/01 Verified and formatted for Oracle9i
COL protocol FORMAT a60 HEADING 'Dispatcher|Protocol'
COL busy     FORMAT 999.99 HEADING 'Percent|Busy'
START title80 'Dispatcher Status'
SPOOL rep_out\&db\mts_disp
SELECT network protocol,
      ((SUM(busy)/(SUM(busy)+SUM(idle)))*100) busy
FROM v$dispatcher
GROUP BY network;

An example report from Source 14.1 is shown in Listing 14.4.

LISTING 14.5 Example Percent Busy Report

Date: 11/25/01                                                Page:   1
Time: 12:07 PM               Dispatcher Status                SYSTEM
                              galinux1 database 

Dispatcher                                                   Percent
Protocol                                                        Busy
------------------------------------------------------------ -------
(ADDRESS=(PROTOCOL=tcp)(HOST=tuscgalinux.local)(PORT=32780))     .00
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tuscgalinux.local)     .00

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tuscgalinux.local)     .00

Monitoring Average Wait Time

The Remote DBA also needs to monitor the average wait time in relation to the dispatchers. If the wait time gets excessive (for example the users complain about long connect times to the database) the Remote DBA should use the V$DISPATCHER and V$QUEUE views to monitor the dispatcher wait times. An example wait time monitoring script is shown in Source 14.2.

SOURCE 14.2 Average Wait Time Report

rem Name: mts_wait.sql
rem Function: Generate Wait time report for the dispatchers
rem History: MRA 10/11/96 Created
rem          MRA 11/25/01 Verified against Oracle9i
COLUMN network FORMAT a40 HEADING 'Dispacther|Protocol'
COLUMN aw      FORMAT a32 HEADING 'Average Wait|Time %'
START title80 'Dispatcher Wait Times'
SPOOL rep_out\&db\mts_wait
   DECODE (SUM(totalq),0,'no responses',
           SUM(wait)/SUM(totalq)*100||' sec wait/response') aw
FROM v$queue q, v$dispatcher d
      q.paddr = d.paddr
GROUP BY network;

An example output from the report in Source 14.2 is shown in Listing 14.5.

LISTING 14.6      Example MTS Wait Report

Date: 11/25/01                                         Page:   1
Time: 11:44 AM           Dispatcher Wait Times            SYSTEM
                            galinux1 database

Dispacther                               Average Wait
Protocol                                 Time %
---------------------------------------- ------------------------
(ADDRESS=(PROTOCOL=tcp)(HOST=tuscgalinux no responses



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