High-Performance Oracle Database Applications:Performance And Tuning For Oracle Database Connectivity Tools

Table of Contents

As a service request is intercepted by an Oracle server, the listener may direct the request via a dedicated server, an MTS, or an existing process (pre-spawned shadow). The key is whether the connection contacts the listener via a service name, or bypasses the listener with the two_task connect string. If the listener is contacted as part of the connection and the MTS parms are defined to init.ora, the client will use the MTS.

There are five basic listener commands: RELOAD, START, STOP, STATUS, and SERVICES. Based on the request, the listener decides whether to dispatch a connection to a dedicated-server process (which it spawns) or to use the MTS. The programmer has several options when deciding how Oracle will manage the process. Dedicated requests can be specified by a version 1.0 connect string, or by using a service name that specifies “server=dedicated” in the tnsnames.ora file.

Note: Local connections will use the listener if the MTSs are defined. Even internal invocations to Oracle (for example, sqlplus/) will add a connection to the MTS.

Managing SQL*Net Connections

Listing 8.4 describes some of the utilities you can use to manage SQL*Net sessions effectively. You should be aware that some of the examples in this section are operation system-independent, and may not apply to your environment.

Listing 8.4 Commit point strength.

commit.sql -  Reports the commit point strength for the database.

SET FEEDBACK OFF
COLUMN NAME  FORMAT a30 HEADING 'Name'
COLUMN TYPE  FORMAT a7  HEADING 'Type'
COLUMN VALUE FORMAT a60 HEADING 'Value'

PROMPT Commit Point-strength Report Output:
PROMPT
PROMPT
SELECT NAME,
       DECODE(type,1,'boolean',
                   2,'string',
                   3,'integer',
                   4,'file') type,
       REPLACE(replace(value,'@','%{sid}'),'?','%{home}') VALUE
FROM   v$parameter
WHERE  NAME = 'commit_point_strength';

Showing And Killing SQL*Net Sessions

On systems running SQL*Net 2.0, you can use a session script to query the number of dedicated and shared servers on the system. For example, Listing 8.5, which appears later in this chapter, shows all connected users and their type of connection to the Oracle database.

Unlike dedicated SQL*Net sessions, you cannot kill multithreaded SQL*Net sessions directly from the Unix operating system. For example, you can identify a runaway session on a dedicated server by using the Unix ps-ef|grep ora command, and subsequently kill it using the kill -9 nnn command. With the multithreaded server, operating system processes no longer exist for each separate task, and you must use the Oracle SQL ALTER SYSTEM KILL SESSION command to kill the task at the Oracle subsystem level (using the ALTER SYSTEM KILL SESSION ‘sid, ser#’; command).

To kill a user in an MTS session, first enter SQL*Plus and type:

SELECT sid, serial#, USERNAME FROM v$session;

SID  SERIAL#    USERNAME
8       28      OPS$xxx
10      211     POS$yyy
13      8       dburleso

If dburleso is the session you want to kill, enter the ALTER SYSTEM KILL SESSION ‘13, 8’ command.

This cumbersome method of clobbering runaway SQL*Net connections can be very annoying in development environments where dozens of programmers are testing programs, and they must call the DBA every time they want to kill a runaway task. The only alternative, however, is to grant the programmers ALTER SYSTEM authority on test platforms.

Managing The Multithreaded Server (MTS)

One of the problems with SQL*Net version 1.0 was that each incoming transaction was “spawned” by the listener as a separate operating system task. With SQL*Net version 2.0, Oracle now has a method for allowing the listener connection to dispatch numerous subprocesses. With the MTS, all communications to a database are handled through a single dispatcher instead of separate Unix process IDs (PIDs) on each database. This translates into faster performance for most online tasks. Even local transactions will be directed through the MTS, and you will no longer see a PID for your local task when you issue ps-ef|grep oracle.

However, be aware that the MTS is not a panacea, especially at times when you want to invoke a dedicated process for your program. For Pro*C programs and I/O-intensive SQL*Forms applications—or any processes that have little idle time—you may derive better performance using a dedicated process.

In general, the MTS offers benefits such as reduced memory use, fewer processes per user, and automatic load balancing. However, it is often very confusing to tell whether the MTS is turned on—much less working properly.

Remember the following rules of thumb when initially starting the MTS:

• The MTS is governed by the init.ora parameters. If no MTS params are present in init.ora, the MTS is disabled.

• The MTS is used when the MTS params are in the init.ora and requests are made by service name (such as @myplace). In other words, you must retrieve the ROWID of all version 1.0 connect strings (such as t:unix1:myplace).

• Each user of the MTS requires 1 K of storage, so plan to increase your shared_pool_size.

• The v$queue and v$dispatcher system tables indicate if the number of MTS dispatchers is too low. Even though the number of dispatchers is specified in the init.ora file, you can change it online in SQL*DBA with the ALTER SYSTEM command:

SQLDBA> ALTER SYSTEM SET MTS_DISPATCHERS = 'TCPIP,4';

• If you encounter problems with the MTS, you can quickly regress to dedicated servers by issuing an ALTER SYSTEM command. The following command turns off the MTS by setting the number of MTS servers to zero:

SQLDBA> ALTER SYSTEM SET MTS_SERVERS=0;

• In order to use OPS$, you must set two init.ora values to true (they default to false):

remote_os_authent = true
remote_os_roles = true

• When both SQL*Net 1.0 and 2.0 are installed, the user may connect to the server either via a dedicated server or via the MTS. However, you cannot stop and restart the listener when connecting via the MTS. You must connect to SQL*DBA with a dedicated server.

• In some cases, the instance must be bounced if the listener is stopped, or it will restart in dedicated mode. Whenever an instance is to be bounced, stop the listener, shut down the instance, restart the listener, and start up the instance. The listener reads the MTS parameters only if it is running before startup of the instance. Therefore, bouncing the listener will disable the MTS.

Table of Contents