6
Tuning Oracle Service for MTS Performance

This chapter provides Oracle Service for MTS performance tuning information:

Specific topics discussed are:

• Automatically Restarting Oracle Service for MTS

• Improving Performance

• Managing Connections

• Increasing the Transaction Timeout Parameter

• Changing Initialization Parameter File Settings

Automatically Restarting Oracle Service for MTS

With release 8.1.5, the Oracle Service for MTS automatically shut down in the following situations:

• the Oracle database was down

• the Oracle Service for MTS was unable to connect to the Oracle database for whatever reason

With release 8.1.5, you needed to manually restart the Oracle Service for MTS. For these situations in releases 8.0.6 and 8.1.6, the Oracle Service for MTS automatically restarts and attempts to re-establish a connection to the Oracle database. Upon re-establishing the connection, all existing, active, enlisted transactions are aborted. Transactions prepared before the Oracle Service for MTS shut down are either committed or aborted, depending upon the outcome of the transaction (determined by the Microsoft Distributed Transaction Coordinator (MS DTC).

For example, if component A was enlisted in a transaction when the Oracle Service for MTS shut down, then:

• the Oracle Service for MTS is restarted

• component A's transaction is aborted and subsequent database operations by component A result in errors

However, if component A called the SetComplete() method on the Microsoft Transaction Server transaction context object, and the MS DTC successfully prepared the transaction before the Oracle Service for MTS shut down, the service on restart:

• queries the MS DTC for the final outcome of component A's transaction as determined by the MS DTC

• commits or aborts the Oracle transaction accordingly

Users can check an entry in the Event Viewer to determine if the Oracle Service for MTS has been restarted. See "Using the Event Viewer" for instructions on using the Event User.

Attention: If Oracle Service for MTS does not restart, transactions remain "in-doubt" and must be manually committed or aborted. See Oracle8i Distributed Database Systems for information on manually committing or aborting these transactions.

Improving Performance

You can improve performance when you:

• Optimize your hardware configuration

  Optimize your network interconnects between the computer running the Component Object Model (COM) components and the computer running the Oracle Service for MTS (if they are different) and the interconnects between these computers and the computer running the Oracle8 database.

• Optimize your programming methods

  Placing all your code for a given transaction into one COM component means you do not need to mark that component as transactional. This eliminates the overhead of going through Microsoft Transaction Server and the Oracle Service for MTS. You can then use the Oracle commit or rollback functions to control that transaction in your component. If you are using OCI, you can still use ORAMTSSvcGet(), but you can also use the ORAMTS_CFLG_NOIMPLICIT flag. If you are updating across two or more Oracle8 databases, use database links and connect to one database from your COM component.

  See "Integrating OCI with Microsoft Transaction Server" for more information on using ORAMTSSvcGet().

Managing Connections

When a COM component ends a session with the Oracle8 database, the connection by default does not immediately terminate. Instead, the connection remains idle in a connection pool, where it is available for reuse by another COM component attempting a new connection to the Oracle8 database.

The idle period during which a connection can be reused reduces the resource costs associated with opening a new connection. The amount of time that the connection remains idle and available in the connection pool is determined by several registry parameter settings that you can modify on the computers on which the client Microsoft Transaction Server components and the Oracle Service for MTS are installed. The Oracle Service for MTS uses a similar connection pool. The connection pool is located in two places:

Connection Pool Location	This Type of Pooling is Used for Connections Between...
Client side	Microsoft Transaction Server client components and the Oracle8 database
Server side	The Oracle Service for MTS and the Oracle8 database

The following illustration identifies the connection pool locations and the registry parameters associated with each pool:

This table describes the client side registry parameters that you can modify to manage connection pooling:

Client Side Parameter	Description	Default Value Entry
ORAMTS_CONN_POOL_TIMEOUT	This parameter enables you to set how long a connection remains idle and available for reuse in the client side connection pool before timing out. After timing out, the connection is released.	60 seconds
ORAMTS_NET_CACHE_TIMEOUT	This parameter enables you to set how long the server portion of the connection remains idle and available for reuse in the client side connection pool before timing out. Each database connection consists of two portions: The portion of the connection associated with ORAMTS_CONN_POOL_TIMEOUT is responsible for session issues such as user name, password, and Net8 connection information. The portion of the connection associated with ORAMTS_NET_CACHE_TIMEOUT is responsible for server issues (the physical network connection). The server connection is the more expensive operation. It is advisable to keep this value set higher then the session timeout value associated with ORAMTS_CONN_POOL_TIMEOUT. After ORAMTS_CONN_POOL_TIMEOUT times out, the server portion of the connection associated with ORAMTS_NET_CACHE_TIMEOUT remains available for a slightly longer period of time. This portion remains available because when you create a connection to an Oracle8 database, the server portion of the connection requires more resources to initially establish than the session portion of the connection. A server connection can then be reused by creating a new session with it.	120 seconds Note: This value is in addition to the value you set for ORAMTS_CONN_POOL_TIMEOUT. For example, if you set ORAMTS_CONN_POOL_TIMEOUT to 180, and set ORAMTS_NET_CACHE_TIMEOUT to 60, the time period before a connection is completely terminated is 240 seconds.
ORAMTS_NET_CACHE_MAXFREE	This parameter enables you to set the maximum number of free server connections to maintain in the client side connection pool at any given time.	5
ORAMTS_OSCREDS_MATCH_LEVEL	This parameter enables you to set the degree of Windows NT security checking to perform on a connection when OS_ROLES is set to TRUE in the INIT.ORA file. When a user connects to the Oracle8 database (for example, with the CONNECT / command), there are certain database roles and privileges associated with their Windows NT user name. When the user disconnects, the connection becomes idle and available in the pool. When another user issues the CONNECT / command, the Windows NT identity of both users must match or the second user can receive the same database roles and privileges as the first user. This can be a security concern if the second user has only the CONNECT and RESOURCE database roles, but accidently receives the DBA database role associated with the first user. For this situation, setting this parameter to OS_AUTH_LOGIN ensures that Windows NT security checking is performed. Furthermore, if the Oracle8 database has OS_ROLES set to TRUE, the roles of the operating system user are associated with a connection regardless of whether "CONNECT /" or "CONNECT USER NAME/PASSWORD" is performed. To enable Windows NT security checking in this case, set this parameter to ALWAYS. Windows NT security checking is an expensive operation. There is always a cost associated with Windows NT verifying the operating system credentials prior to reusing a connection. For performance reasons, it is advisable to set this parameter to NEVER. However, if you set OS_ROLES to TRUE or use operating system-authenticated connections, ensure that you set this parameter accordingly.	There are three possible values: ALWAYS Windows NT security checking is always performed. This setting is the most secure, because it does not permit a second user to accidently receive the database roles and privileges of the first user. OS_AUTH_LOGIN Windows NT security checking is only done if the user name and password are NULL. This is the default value. NEVER No Windows NT security checking is performed. This setting is the least expensive of the three. Use this setting if you are not setting OS_ROLES to TRUE or not using operating system-authenticated connections.

This table describes the server side registry parameters that you can modify to manage connection pooling:

Server Side Parameter	Description	Default Value Entry
ORAMTS_SVC_CONN_POOL_TIMEOUT	This parameter enables you to set how long a connection remains idle and available for reuse in the server side connection pool before timing out. After timing out, the connection is released.	300 seconds
ORAMTS_SVC_NET_CACHE_TIMEOUT	This parameter enables you to set how long the server portion of the connection remains idle and available for reuse in the server side connection pool before timing out. Each database connection consists of two portions: The portion of the connection associated with ORAMTS_SVC_CONN_POOL_TIMEOUT is responsible for session issues such as user name, password, and Net8 connection information. The portion of the connection associated with ORAMTS_SVC_NET_CACHE_TIMEOUT is responsible for server issues (the physical network connection). The server connection is the more expensive operation. It is advisable to keep this value set higher then the session timeout value associated with ORAMTS_SVC_CONN_POOL_TIMEOUT. After ORAMTS_SVC_CONN_POOL_TIMEOUT times out, the server portion of the connection associated with ORAMTS_SVC_NET_CACHE_TIMEOUT remains available for a slightly longer period of time. This portion remains available because when you create a connection to an Oracle8 database, the server portion of the connection requires more resources to initially establish than the session portion of the connection. A server connection can then be reused by creating a new session with it.	0 Note: This value is in addition to the value you set for ORAMTS_SVC_CONN_POOL_TIMEOUT. For example, if you set this value to 60, and set ORAMTS_SVC_NET_CACHE_TIMEOUT to 60, the time duration before a connection is completely terminated is 120 seconds. The default values for ORAMTS_SVC_NET_CACHE_TIMEOUT and ORAMTS_SVC_NET_CACHE_MAXFREE are set to zero because the Oracle Service for MTS uses a single user to connect to the database. Therefore, the parameter ORAMTS_SVC_CONN_POOL_TIMEOUT can be used to configure the pool.
ORAMTS_SVC_NET_CACHE_MAXFREE	This parameter enables you to set the maximum number of free server connections to maintain in the server side connection pool at any given time.	0

To edit the connection pool registry settings:

1. Log on to the appropriate computer:

   For...	Log on to the computer where...
   Server side parameters	Oracle Service for MTS is installed
   Client side parameters	Client Microsoft Transaction Server components are installed

2. Enter the following at the MS-DOS command prompt:

   C:\> REGEDT32
   

3. Go to the appropriate registry location:

   For...	Go to...
   Server side parameters	HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet \Services\OracleMTSServiceN
   Client side parameters	HKEY_LOCAL_MACHINE\SOFTWARE \ORACLE\HOMEID where ID is the unique registry subkey of your Oracle home

4. Double-click the parameter to edit on the right side of the Registry Editor window.

5. Modify the value in the dialog box that appears, and click OK.

6. Choose Exit from the Registry main menu.

Increasing the Transaction Timeout Parameter

If your transaction requests are timing out before completing, it may be because the transaction timeout parameter is set too low. Increase the transaction timeout parameter to ensure that your transactions have sufficient time to complete.

To increase the transaction timeout parameter:

1. Go to the computer on which Microsoft Transaction Server is installed and the Oracle Service for MTS is configured.

2. Choose Start > Programs > Windows NT 4.0 Option Pack > Microsoft Transaction Server > Transaction Server Explorer.

   The Microsoft Management Console appears.

3. Double-click Console Root in the Microsoft Management Console Explorer window.

4. Double-click Microsoft Transaction Server.

5. Double-click Computers.

6. Right-click My Computer.

   A menu appears with several options.

7. Choose Properties.

   The My Computer Properties dialog box appears.

8. Choose the Options tab.

9. Enter a value in the Transaction Timeout field and click OK:

   
   

   The transaction timeout value is increased. For most environments, 60 seconds may be sufficient. However, if your transaction is competing with numerous concurrent transactions, this value may be too low.

Changing Initialization Parameter File Settings

You may need to set several initialization parameters to the values described below in order to use your Oracle8 database with Microsoft Transaction Server. The values to which to set these parameters are based upon your database workload environment. See section "Verifying Initialization Parameter File Values" for information on determining proper parameter settings.

To set initialization parameters:

1. Go to the computer on which the Oracle8 database is installed.

2. Go to the initialization parameter file for your Oracle8 database release:

   If Using An...	Go to...
   8.1.x databases	ORACLE_BASE\ADMIN\DB_NAME\PFILE\INIT.ORA
   8.0.6 databases	ORACLE_HOME\DATABASE\INITSID.ORA

3. Set the following initialization parameters to at least these values:
   • SESSIONS = 200 (or larger if anticipating heavier loads)

   • PROCESSES = 200 (or larger if anticipating heavier loads)

4. Start SQL*Plus:

   C:\> SQLPLUS
   

5. Connect with the INTERNAL account:

   ENTER USER-NAME: INTERNAL
   

6. Shut down the Oracle8 database:

   SQL> SHUTDOWN
   

7. Restart the Oracle8 database:

   SQL> STARTUP
   

8. Exit SQL*Plus:

   SQL> EXIT