Oracle Online MTS Modifications

While you initially configure MTS in your instance parameter file, there are some changes that you can make while the instance is running. Using the ALTER SYSTEM command, you can manually adjust the number of dispatchers that are running. Also using ALTER SYSTEM, you can increase the minimum number of shared server processes that Oracle keeps around.

Changing the Number of Shared Server Processes

The minimum number of shared server processes is controlled by the MTS_SERVERS parameter setting. MTS_SERVERS is a dynamic parameter that you can change while the instance is running. For example:

ALTER SYSTEM
  SET MTS_SERVERS = 20;

If fewer than 20 shared server processes are running when you issue this command, enough new shared server processes will be started to bring the total up to at least 20. If more than 20 shared server processes are running, this command may not have an immediate effect. Instead, as demand drops off, the number of shared server processes will eventually drop to the minimum number specified by MTS_SERVERS.

The MTS_SERVERS setting can never be greater than MTS_MAX_SERVERS. MTS_MAX_SERVERS places an upper limit on the number of shared server processes that may be running at any given time, and that limit cannot be changed while the instance is running.

Remember that an Oracle instance will automatically adjust the number of shared server processes as demand requires, but that number will always be in the range defined by MTS_SERVERS and MTS_MAX_SERVERS.

Changing the Number of Dispatchers

The ALTER SYSTEM command lets you change the number of dispatcher processes that are running for an instance. Use the following syntax:

ALTER SYSTEM
  SET MTS_DISPATCHERS = '(INDEX=conf_indx)(address)(options)';

The clauses in the MTS_DISPATCHERS setting can actually appear in any order. You could, for example, specify the index last and the address first if you prefer to do that. The replaceable parameters in the syntax are as follows:

Using the ALTER SYSTEM command can sometimes be a bit frustrating for several reasons. The syntax is complex, and errors aren't always reported. If you make a mistake in the string that you pass, the command may still execute "successfully." There may also be a significant delay before your changes take effect, especially when you are reducing the number of dispatchers.

Adding dispatchers

To add dispatchers, start by looking at what you already have. Pay particular attention to the CONF_INDX values:

SQL> SELECT name, conf_indx, network
  2  FROM v$dispatcher;

NAME  CONF_INDX NETWORK
---- ---------- ------------------------------
D000          0 (ADDRESS=(PROTOCOL=tcp)(HOST=d
                onna.gennick.org)(PORT=1217))
D001          1 (ADDRESS=(PROTOCOL=tcp)(HOST=d
                onna.gennick.org)(PORT=1218))
D002          2 (ADDRESS=(PROTOCOL=tcp)(HOST=d
                onna.gennick.org)(PORT=1219))
D003          3 (ADDRESS=(PROTOCOL=tcp)(HOST=d
                onna.gennick.org)(PORT=1220))

If you are going to add more TCP/IP dispatchers, you need to decide which index to use—you can add to an existing index or create a new one. We'll demonstrate both. To start, let's add a new dispatcher with an index of 4. The following command will do this:

ALTER SYSTEM
  SET MTS_DISPATCHERS='(INDEX=4)(PROTOCOL=TCP)(DISPATCHERS=1)';

In this command, INDEX = 4 represents a new CONF_INDX value. The DISPATCHERS = 1 setting tells the instance that you want one dispatcher for the index. The PROTOCOL = TCP setting results in a TCP/IP dispatcher.

To increase the number of dispatchers for an existing index, specify that index and also specify a higher number of dispatchers than already exist for that index. For example:

ALTER SYSTEM
  SET MTS_DISPATCHERS=
    '(INDEX=0)(ADDRESS=(PROTOCOL=TCP)(HOST=donna.gennick.org)
     (PORT=1216))(DISPATCHERS=2)';

This command affects index 0. There is currently one dispatcher running for that index. This command uses DISPATCHERS=2 to raise the number to two dispatchers. Instead of specifying just the protocol, the ADDRESS parameter has been used to define the exact host and port address of the new dispatcher to be created. The following example shows the results of executing the two ALTER SYSTEM commands shown in this section:

SQL> ALTER SYSTEM
  2    SET MTS_DISPATCHERS='(INDEX=4)(PROTOCOL=TCP)(DISPATCHERS=1)';

System altered.

SQL> ALTER SYSTEM
  2    SET MTS_DISPATCHERS=
  3      '(INDEX=0)(ADDRESS=(PROTOCOL=TCP)(HOST=donna.gennick.org)
  4       (PORT=1216))(DISPATCHERS=2)';

System altered.

SQL> SELECT name, conf_indx, network
  2  FROM v$dispatcher;

NAME  CONF_INDX NETWORK
---- ---------- ------------------------------
D000          0 (ADDRESS=(PROTOCOL=tcp)(HOST=d
                onna.gennick.org)(PORT=1217))
D001          1 (ADDRESS=(PROTOCOL=tcp)(HOST=d
                onna.gennick.org)(PORT=1218))
D002          2 (ADDRESS=(PROTOCOL=tcp)(HOST=d
                onna.gennick.org)(PORT=1219))
D003          3 (ADDRESS=(PROTOCOL=tcp)(HOST=d
                onna.gennick.org)(PORT=1220))
D004          4 (ADDRESS=(PROTOCOL=tcp)(HOST=d
                onna.gennick.org)(PORT=1155))
D005          0 (ADDRESS=(PROTOCOL=TCP)(HOST=d
                onna.gennick.org)(PORT=1216))

6 rows selected.

You can see that two dispatchers have been added, one for index 4, and one for index 0.

You can never add more dispatchers than are allowed by the MTS_MAX_DISPATCHERS setting.

Removing dispatchers

The process for removing dispatchers is similar to that used for adding them. You need to specify the index of the MTS_DISPATCHERS parameter that you want to change in order to identify the dispatcher protocol and to set a new value for the number of dispatchers. The following two commands remove the two dispatchers that were added in the previous section:

ALTER SYSTEM
  SET MTS_DISPATCHERS='(INDEX=4)(PROTOCOL=TCP)(DISPATCHERS=0)';
ALTER SYSTEM
  SET MTS_DISPATCHERS='(INDEX=0)(PROTOCOL=TCP)(DISPATCHERS=1)';

One frustrating thing about removing dispatchers is that they aren't deleted immediately. Even if a dispatcher is not busy, you may need to wait for several minutes until its process is finally deleted. This can leave you wondering whether your command actually worked.