Re: 8.1.7.2 Listener would not register the Dispatchers...

From: koert54 <koert54_at_nospam.com>
Date: Fri, 02 Nov 2001 17:05:19 GMT
Message-ID: <jnAE7.146$eI3.99@afrodite.telenet-ops.be>

Taken from metalink (maybe you've already went through this) - but I hope this will help ...

ORACLE MTS/Networking

network.MTS

FREQUENTLY ASKED QUESTIONS

22-AUG-1999 CONTENTS

What is Multi-Threaded Server (MTS)?
What users (O/S IDs) are working through which MTS dispatchers?
Is MTS supported on Windows NT?
How to trace MTS dispatchers and shared servers?
How to setup IPC for MTS?
How to estimate Shared Pool utilization?
Is MTS supported on Netware?
What diagnostics can be used for MTS?
How to calculate Shared Pool size when using MTS?
How to make a dedicated connection when MTS is configured on the server?
Can local connections be made via MTS?
How to determine the maximum number of connections a dispatcher can handle?
How to configure MTS?
How to turn off MTS without stopping the database?
How to start more dispatchers without stopping the database?

QUESTIONS & ANSWERS
1. What is MTS?

Answer

MTS allows many user processes to share very few server processes. Without MTS, each
user process requires its own dedicated server process; a new server process is created
for each client requesting a connection. A dedicated server process remains associated
to the user process for the remainder of the connection. With MTS, many user processes
connect to a dispatcher process. The dispatcher routes client requests to the next
available shared server process. The advantage of MTS is that system overhead is
reduced, so the number of users that can be supported is increased.

References

<PRE:1013209.6> <PRE:1005246.6>

2. What users (O/S IDs) are working through which MTS dispatchers?

Answer

Disclaimer:

This script is provided for educational purposes only. It is NOT supported by Oracle
World Wide Technical Support. The script has been tested and appears to work as intended.
However, you should always test any script before relying on it.

PROOFREAD THIS SCRIPT PRIOR TO USING IT! Due to differences in the way text editors,
email packages and operating systems handle text formatting (spaces, tabs and carriage
returns), this script may not be in an executable state when you first receive it.

Abstract:

The following script will report what users (O/S IDs) are working through which MTS dispatchers.

Requirements:

Access privileges to the dynamic performance tables.

Script:

cut --------------------- cut -------------- cut --------------

SET ECHO off
REM NAME: tfs
REM USAGE:"@path/..."

REM -------------------------------------------------------------------

REM REQUIREMENTS:
REM Access to dynamic performance tables

REM -------------------------------------------------------------------

REM PURPOSE:
REM The purpose of this script is to report the dispatcher, session and REM process mapping using MTS.

REM -------------------------------------------------------------------

REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT REM supported by Oracle World Wide Technical Support. REM The script has been tested and appears to work as intended. REM You should always run new scripts on a test instance initially.

REM -------------------------------------------------------------------

REM Main text of script follows:

column   network     format a7
column   disp        format a4
column   oracle_user format a11
column   sid         format 999
column   serial#     format 9999999
column   os_user     format a10
column   terminal    format a8
column   program     format a20

break on network on disp

select   d.network network,      d.name disp
       , s.username oracle_user, s.sid sid,           s.serial# serial#
       , p.username os_user,     p.terminal terminal, s.program program
from     v$dispatcher d, v$circuit c, v$session s, v$process p
where    d.paddr = c.dispatcher (+)
and      c.saddr = s.saddr (+)
and      s.paddr = p.addr (+)

order by d.network, d.name, s.username

cut --------------------- cut -------------- cut --------------

References

<PRE:1019594.6>

3. Is MTS supported on Windows NT?

Answer

With Oracle 7 and SQL*Net v1/v2, MTS was not supported on Windows NT. With Oracle
8.0.3 and Net 8.0.3, MTS is supported on Windows NT v4.0 using Winsock v2.

4. How to trace MTS dispatchers and shared servers?

Answer

Add the following parameters to the INIT<sid>.ORA file for each sid that is running MTS and you want to create a Trace.

For dispatchers:
event="10248 trace name context forever, level X"

For shared servers:
event="10249 trace name context forever, level X"

X can be a value from 1 to 10, 10 being the most detailed trace. After you add these
parameters to the INIT<sid>.ORA file, stop the instance and restart it for the changes to take effect.

5. How to setup IPC for MTS?

Answer

IPC or local connections can be made using MTS. The only requirement is that the
version of Oracle on the server platform in use supports MTS and that the server
supports IPC. You also have to configure the LISTENER.ORA and TNSNAMES.ORA files to use IPC.

LISTENER.ORA:

LISTENER=
(ADDRESS_LIST=

     (ADDRESS=
        (PROTOCOL=IPC)
        (KEY=<sid name>)
     )
     (ADDRESS=
        (PROTOCOL=IPC)
        (KEY=<alias in tnsnames.ora for the sid>)
     )

  )
CONNECT_TIMEOUT_LISTENER=10
STARTUP_WAIT_TIME_LISTENER=0
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=

      (SID_NAME=<sid name>)
      (ORACLE_HOME=<home directory path for Oracle>)

)
)

Other addresses can be added to the address list if other protocols are being used.
If the sid name and alias in tnsnames.ora are the same only one address entry is required for IPC.

TNSNAMES.ORA:

<alias>=
(DESCRIPTION=

     (ADDRESS=
        (PROTOCOL=IPC)
        (KEY=<sid name>)
     )
     (CONNECT_DATA=
        (SID=<sid name>)
     )

)

INIT.ORA entries for MTS:

MTS_DISPATCHERS="IPC,2"
MTS_SERVERS=1
MTS_MAX_DISPATCHERS=6
MTS_MAX_SERVERS=3
MTS_SERVICE=<sid name>
MTS_LISTENER_ADDRESS="(ADDRESS=(PROTOCOL=IPC)(KEY=<sid name>))"

To make the multi-threaded IPC connection issue a connection command. Example (using SQLPLUS):

sqlplus <username>/<password>@<alias from tnsnames.ora>

References

<PRE:1005246.6>

6. How to estimate Shared Pool utilization?

Answer

Disclaimer:

Abstract:

This script estimates shared pool utilization based on current database usage. This
should be run during peak operation, after all stored objects (i.e. packages, views) have been loaded.

Requirements:

SELECT privileges on V$ tables

Script:

cut ---------------------- cut -------------- cut --------------

SET ECHO off
REM NAME: TFSSPUTL.SQL
REM USAGE:"@path/tfssputl"

REM ------------------------------------------------------------------------

REM REQUIREMENTS:
REM SELECT on V$ tables

REM ------------------------------------------------------------------------

REM PURPOSE:
REM Estimates shared pool utilization based on current database REM usage. This should be run during peak operation, after all REM stored objects i.e. packages, views have been loaded.

REM ------------------------------------------------------------------------
REM EXAMPLE:
REM    Obj mem:        2536573 bytes
REM    Shared sql:     4101742 bytes
REM    Cursors:        2125 bytes

REM Free memory: 968976 bytes (.92MB) REM Shared pool utilization (total): 7968528 bytes (7.6MB) REM Shared pool allocation (actual): 9000000 bytes (8.58MB) REM Percentage Utilized: 89%
REM

REM ------------------------------------------------------------------------

REM ------------------------------------------------------------------------

REM Main text of script follows:

set echo off
spool tfssputl.lst

Rem If running MTS uncomment the mts calculation and output Rem commands.

set serveroutput on;

declare

        object_mem number;
        shared_sql number;
        cursor_mem number;
        mts_mem number;
        used_pool_size number;
        free_mem number;
        pool_size varchar2(512); -- same as V$PARAMETER.VALUE

begin

Stored objects (packages, views) select sum(sharable_mem) into object_mem from v$db_object_cache;
Shared SQL -- need to have additional memory if dynamic SQL used select sum(sharable_mem) into shared_sql from v$sqlarea;
User Cursor Usage -- run this during peak usage.
assumes 250 bytes per open cursor, for each concurrent user. select sum(250*users_opening) into cursor_mem from v$sqlarea;
For a test system -- get usage for one user, multiply by # users
select (250 * value) bytes_per_user
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = 'opened cursors current'
and s.sid = 25; -- where 25 is the sid of the process
MTS memory needed to hold session information for shared server users
This query computes a total for all currently logged on users (run
during peak period). Alternatively calculate for a single user and
multiply by # users. select sum(value) into mts_mem from v$sesstat s, v$statname n where s.statistic#=n.statistic# and n.name='session uga memory max';
Free (unused) memory in the SGA: gives an indication of how much memory
is being wasted out of the total allocated. select bytes into free_mem from v$sgastat where name = 'free memory';
For non-MTS add up object, shared sql, cursors and 20% overhead. used_pool_size := round(1.2*(object_mem+shared_sql+cursor_mem));
For MTS mts contribution needs to be included (comment out previous line)
used_pool_size := round(1.2*(object_mem+shared_sql+cursor_mem+mts_mem));

select value into pool_size from v$parameter where name='shared_pool_size';

Display results dbms_output.put_line ('Object mem: '||to_char (object_mem) || ' bytes'); dbms_output.put_line ('Shared SQL: '||to_char (shared_sql) || ' bytes'); dbms_output.put_line ('Cursors: '||to_char (cursor_mem) || ' bytes');
dbms_output.put_line ('MTS session: '||to_char (mts_mem) || ' bytes'); dbms_output.put_line ('Free memory: '||to_char (free_mem) || ' bytes ' || '(' || to_char(round(free_mem/1024/1024,2)) || 'MB)'); dbms_output.put_line ('Shared pool utilization (total): '|| to_char(used_pool_size) || ' bytes ' || '(' || to_char(round(used_pool_size/1024/1024,2)) || 'MB)'); dbms_output.put_line ('Shared pool allocation (actual): '|| pool_size ||' bytes ' || '(' || to_char(round(pool_size/1024/1024,2)) || 'MB)'); dbms_output.put_line ('Percentage Utilized: '||to_char (round(used_pool_size/pool_size*100)) || '%'); end; /

spool off

cut ---------------------- cut -------------- cut --------------

Examples:

Object mem: 2536573 bytes
Shared SQL: 4101742 bytes
Cursors: 2125 bytes
Free memory: 968976 bytes (.92MB)
Shared pool utilization (total): 7968528 bytes (7.6MB) Shared pool allocation (actual): 9000000 bytes (8.58MB) Percentage Utilized: 89%

References

<PRE:1020183.6>

7. Is MTS supported on Netware?

Answer

NetWare has been designed without a need for MTS. The design of MTS reduces process
loads for very large installations supporting hundreds of users. Oracle on NetWare
is completely implemented as a multi-threaded application.

8. What diagnostics can be used for MTS?

Answer

The components of MTS consist of processes on the system, communication software
and the shared global section (SGA). Below is a map of MTS which shows how these
pieces fit together. Diagnostic information about all of the above components are
available to privileged accounts in SQLDBA, Server Manager, and SQL*Plus.

SQL*Net

   +--------+          +------------+       +---------+       | server |
    "JSMITH"          "ORA_<sid>_D000"           |            +--------+
                                                 |         "ORA_<sid>_S000"
                                            +---------+
                                             session |
                                            +---------+

For each SQL*Net V2 MTS client session, there exists exactly one row in the circuit
view which describes the current status of the client session.

Some of the views containing MTS information are:

  V$CIRCUIT
  V$SHARED_SERVER
  V$DISPATCHER
  V$MTS
  V$QUEUE
  V$SESSION

These views are readable by SYS and SYSTEM by default. The DBA can provide access
to these views to other users on the database by granting them the MONITORER role.

References

For detailed information on the diagnostic queries see <PRE:1005259.6>

9. How to calculate Shared Pool size when using MTS?

Answer

A)If you are using MTS, then you will need to allow enough memory for all the shared
server users to put their session memory in the shared pool. This can be measured for one user with the following query:

select value sess_mem
from v$sesstat s, v$statname n

   where s.statistic# = n.statistic#
    and  n.name = 'session uga memory'
    and  s.sid  =  23;    -- replace 23 with session id of user being

measured

A more conservative value to use is the maximum session memory that was ever allocated by the user:

select value sess_max_mem
from v$sesstat s, v$statname n

   where s.statistic# = n.statistic#
    and  n.name = 'session uga memory max'
    and  s.sid  =  23;    -- replace 23 with session id of user being

measured

To select this value for all the currently logged on users the following query can be used:
  select sum(value) all_sess_mem
    from v$sesstat s, v$statname n
   where s.statistic# = n.statistic#
    and n.name = 'session uga memory max';

B) OVERHEAD If the system is making heavy use of dynamic SQL this 20% may not be enough, since
this will come out of the runtime heap in the shared pool. You will need to add a
minimum of 20-30% overhead to the values calculated above to allow for unexpected
and unmeasured usage of the shared pool other than object memory, shared sql or cursor
memory. If the system is making heavy use of dynamic SQL 20% may not be enough, since
this will come out of the runtime heap in the shared pool.

How to make a dedicated connection when MTS is configured on the server?

Answer

Add the (SERVER=DEDICATED) parameter to the tnsnames.ora file:

<service name> =
(DESCRIPTION =
(ADDRESS_LIST =

        (ADDRESS =
          (COMMUNITY = <community>)
          (PROTOCOL = <protocol>)
          (Host = <server>)
          (Port = 1526)
        )

)
(CONNECT_DATA =

     (SID = <sid>)
     (SERVER=DEDICATED)           <------------

)
)

NOTE: If Oracle Names is configured for your network, no TNSNAMES.ORA is created.
You therefore cannot create a service name for a dedicated connection. The workaround
this limitation (if required for testing purposes, only) is to add a USE_DEDICATED_SERVER=ON
parameter to the SQLNET.ORA file. This parameter makes all connections from a client workstation DEDICATED.

Can local Unix connections be made via MTS?

Answer

When installing SQL*Net V2 or Net 8 on UNIX, you will automatically install the two
available interprocess communication protocol adapters, IPC and BEQ. The implementation
of these adapters will vary by port: check the port's Installation and Configuration Guide for any port-specific details.

The IPC adapter, which is implemented on top of Unix Domain Sockets, is used for local connections to the MTS.

The BEQ adapter, which is implemented on top of Unix PIPES, cannot be configured
for MTS. It is used to bequeath, or spawn a process and connected to them via Unix PIPES.

How to determine the maximum number of connections a dispatcher can handle?

Answer

The maximum number of connections that a dispatcher can handle is port specific.
One can see the specific number for your operating system by issuing the command:

lsnrctl services

P713 has 2 service handlers

DEDICATED SERVER established:5 refused:0 DISPATCHER established:0 refused:0 current:0 max:60 state:ready

      D000 (machine: technique, pid: 29096)
      (ADDRESS=(PROTOCOL=ipc)(DEV=7)(KEY=#29096.1))

It is 60 in this case.

Any changes you make to the sessions in init.ora are reflected in the maximum_connections
column of v$mts. It is determined as: min(os_specific_limit-3, sessions init.ora parameter)

The 3 are reserved for listening port, incoming connection, and tracefile.

EXAMPLE

If your operating system supports 64 connections per process, but you set sessions
to 30 in the init.ora file, then v$mts will show 30. If you raise the sessions to
100, then v$mts will show 61. The maximum number of sessions depends on both the
port and on the value of sessions as set in the init.ora file.

How to configure MTS?

Answer

Prior to implementing MTS, ensure that SQL*Net V2/Net 8 is installed on both the client and the server; confirm that the client can connect to the server. Check to see that the listener.ora file contains a profile for IPC. An example of the listener.ora follows.

LISTENER =

      (ADDRESS_LIST =
            (ADDRESS=
              (PROTOCOL=IPC)
              (KEY= <SID>)          <- Name of SID
            )
            (ADDRESS =
              (PROTOCOL = TCP)
              (HOST = <Server>)     <- Server name
              (PORT = 1521)
            )
       )

     STARTUP_WAIT_TIME_LISTENER = 0
     CONNECT_TIMEOUT_LISTENER = 10
     LOG_DIRECTORY_LISTENER = <Oracle_Home>/network/log>  <- Vaild directory
     LOG_FILE_LISTENER = <listener>                       <- Valid file name

     SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = <SID>)                         <- Name of SID
          (ORACLE_HOME = <Oracle_Home>)              <- Path to Oracle_home
        )
      )

     TRACE_LEVEL_LISTENER = OFF

2) The following lines should be in the init<sid>.ora file:

   mts_dispatchers="<protocol>,<initial_number_of_dispatchers>"
   mts_max_dispatchers=<maximum number of dispatchers>
   mts_servers=<minimum_number_of_servers>
   mts_service=<sid>
   mts_listener_address="(ADDRESS=(PROTOCOL=<protocol>) (HOST=<host_name>)

(PORT=<port_number>))"

In the following example we are configuring MTS to support:

TCP/IP and IPC connections.
The listener will be listening on port 1521.
The database SID is V716.
The server is server1
The minimum number of dispatchers/servers is one (1).
The maximum number of dispatchers/servers is ten (10).

   mts_dispatchers="tcp,1"
   mts_dispatchers="ipc,1"
   mts_max_dispatchers=10
   mts_servers=1
   mts_max_servers=10
   mts_service=V716
   mts_listener_address="(ADDRESS=(PROTOCOL=tcp)(host=server1)(port=1521))"
   mts_listener_address="(ADDRESS=(PROTOCOL=ipc)(KEY=V716))"

3) To start the system use the following procedure:

Start the listener first (lsnrctl start).
Start the database.

References

<PRE:1005246.6>

How to turn off MTS without stopping the database?

Answer

As the dba, use the ALTER SYSTEM command to set the number of dispatchers to zero. Below is an example for TCP dispatchers:

ALTER SYSTEM SET MTS_DISPATCHERS='TCP,0' Note: When specifying the protocol, it must be in the same case as used in the MTS_DISPATCHERS
command in the init<sid>.ora. For example, if you used TCP in the init<sid>.ora then use TCP in the alter system command.

How to start more dispatchers without stopping the database?

Answer

As the dba, use the ALTER SYSTEM command to set a new value for the number of dispatchers
to have running. Below is an example for TCP:

ALTER SYSTEM SET MTS_DISPATCHERS="TCP,6" Note: When specifying the protocol, it must be in the same case as used in the MTS_DISPATCHERS
command in the init<sid>.ora. For example, if you used TCP in the init<sid>.ora then use TCP in the alter system command.

In this example, there will be a total of 6 dispatchers running, not an additional 6.

The total number of dispatchers you start cannot be greater than the value of MTS_MAX_DISPATCHERS
as defined in the init<sid>.ora.

"Sambavan" <sambavan_at_alumni.utexas.net> wrote in message news:51e73568.0111010907.25d9405a_at_posting.google.com...

> Hi Folks,
>
> I've Oracle 8.1.7.2 SE running on Solaris 8. From the day I upgraded
> my db from 8.1.6.3 to 8.1.7.2, I'm experiencing a problem where my
> listener would not register the dispatchers. When you start the
> instance, it would start the dispatcher and servers. But when you look
> into lsnrctl services, it would show that all the service handles to
> be in DEDICATED mode.
>
> I even tried relinking and no luck. I tried the loopback connection
> and no luck... I even tried changing the tnsnames.ora file SRVR=SHARED
> and it also did not work... For the last 2Months, I have been breaking
> my heads to figure out what was causing this problem...
>
> Any thoughts on this?
>
> Thanx,
> Sam

Received on Fri Nov 02 2001 - 11:05:19 CST