HI Tom,

Sub : Poor Response while working in MTS mode.

We have 8i 8.1.6 installation on NT plateform and Oracle database is configured to run in MTS mode. We are 25 concurrent users at present but expected to grow up to 250. Local naming method of NET8 is followed to connect to Oracle server in a switched ethernet LAN.

My question is after connecting to a database in a LAN, users face delay in getting response most of the times even for smaller data volume. In general, delay is around about one or one and one half minutes. Only few times, we get prompt response but not always. Our SGA is properly sized and there is no contention in harddisks. Also, all objects in the database are properly tuned. I personnely feel that MTS performance at our end does not seem to be proper or we have not configured MTS for best performance.

Can you suggest any remedial solution to this ?

Followings are our initialization parameters used for MTS.

MTS_DISPATCHERS = "(PROTOCOL=TCP)(PRE=Oracle.aurora.server.SGiopServer)(MUL=ON)"
SHARED_POOL_SIZE = 152428800
LARGE_POOL_SIZE = 31457280
SORT_AREA_SIZE = 65536
SORT_AREA_RETAINED_SIZE = 65536

There is no mts_max_dispatchers, mts_servers & mts_max_servers defined. Meaning that we have retained default one dispatcher and one shared server. Session Data Unit (SDU) buffer size has not been efined in a client as well as server tnsnames.ora resulting efault SDU buffer size of 2048. I have also verified the available MTS performance views but could not conclude anything.

Following are tnsnames.ora files for SERVER & CLIENT respectively.

1. SERVER tnsnames.ora

# TNSNAMES.ORA Network Configuration File: D:\Oracle\Ora81\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

GEB.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = NMP)(Server = NF5000)(Pipe = ORAPIPE))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

GEBTCP.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = nf5000)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = nf5000)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)


2. CLIENT tnsnames.ora

# C:\FORMS6I\NET80\ADMIN\TNSNAMES.ORA Configuration File:C:\forms6i\net80\admin\tnsnames.ora
# Generated by Oracle Net8 Assistant

GEB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = nf5000)(PORT = 1521))
(CONNECT_DATA = (SID = ORCL))
)

EXAMPLE3.WORLD =
(DESCRIPTION =
(ADDRESS = (COMMUNITY = nmp.world)(PROTOCOL = NMP)(Server = FinanceServer1)(Pipe = ORAPIPE))
(CONNECT_DATA = (SID = ORCL))
)

EXAMPLE1.WORLD =
(DESCRIPTION =
(ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = Production1)(Port = 1521))
(CONNECT_DATA = (SID = SID1))
)

BEQ-LOCAL.WORLD =
(DESCRIPTION =
(ADDRESS = (COMMUNITY = beq.world)(PROTOCOL = BEQ)(PROGRAM = oracle73)(ARGV0 = oracle73ORCL)(ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))
(CONNECT_DATA = (SID = ORCL))
)

EXAMPLE2.WORLD =
(DESCRIPTION =
(ADDRESS = (COMMUNITY = spx.world)(PROTOCOL = SPX)(Service = Server_lsnr))
(CONNECT_DATA = (SID = ORCL))
)

TCP-LOOPBACK.WORLD =
(DESCRIPTION =
(ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = 127.0.0.1)(Port = 1521))
(CONNECT_DATA = (SID = ORCL))
)


3. SERVER sqlnet.ora

# SQLNET.ORA Network Configuration File: D:\Oracle\Ora81\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES)

4. CLIENT sqlnet.ora

AUTOMATIC_IPC = OFF
TRACE_LEVEL_CLIENT = OFF
names.directory_path = (TNSNAMES)
names.default_domain = world
name.default_zone = world