 |
|
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:
ALTER SYSTEM
SET MTS_DISPATCHERS
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
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
rem
COL protocol FORMAT a60 HEADING 'Dispatcher|Protocol'
COL busy FORMAT 999.99 HEADING 'Percent|Busy'
rem
SET feedback OFF VERIFY OFF LINES 78 PAGES 58
START title80 'Dispatcher Status'
SPOOL rep_out\&db\mts_disp
rem
SELECT network protocol,
((SUM(busy)/(SUM(busy)+SUM(idle)))*100) busy
FROM v$dispatcher
GROUP BY network;
rem
SPOOL OFF
SET feedback ON VERIFY ON LINES 22
TTITLE OFF
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
(PORT=32782))(PRESENTATION=oracle.aurora.server.GiopServer)(
SESSION=
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tuscgalinux.local) .00
(PORT=32783))(PRESENTATION=oracle.aurora.server.SGiopServer)
(SESSION
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
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
rem
COLUMN network FORMAT a40 HEADING 'Dispacther|Protocol'
COLUMN aw FORMAT a32 HEADING 'Average Wait|Time %'
SET feedback OFF VERIFY OFF LINES 78 PAGES 55
START title80 'Dispatcher Wait Times'
SPOOL rep_out\&db\mts_wait
SELECT
NETWORK,
DECODE (SUM(totalq),0,'no responses',
SUM(wait)/SUM(totalq)*100||' sec wait/response') aw
FROM v$queue q, v$dispatcher d
WHERE q.type='DISPATCHER' AND
q.paddr = d.paddr
GROUP BY network;
SPOOL OFF
SET feedback ON VERIFY ON PAGES 80 LINES 22
TTITLE OFF
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
.local)(PORT=32780))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOS no responses
T=tuscgalinux.local)(PORT=32782))(PRESEN
TATION=oracle.aurora.server.GiopServer)(
SESSION=
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOS no responses
T=tuscgalinux.local)(PORT=32783))(PRESEN
TATION=oracle.aurora.server.SGiopServer)
(SESSION
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. |
 |
|
