DML_LOCKS

Property	Description
Parameter type	Integer
Default value	Derived: 4 * TRANSACTIONS
Modifiable	No
Range of values	20 to unlimited; a setting of 0 disables enqueues
Basic	No
Real Application Clusters	You must set this parameter for every instance, and all instances must have positive values or all must be 0.

A DML lock is a lock obtained on a table that is undergoing a DML operation (insert, update, delete). DML_LOCKS specifies the maximum number of DML locks—one for each table modified in a transaction. The value should equal the grand total of locks on tables currently referenced by all users. For example, if three users are modifying data in one table, then three entries would be required. If three users are modifying data in two tables, then six entries would be required.

The default value assumes an average of four tables referenced for each transaction. For some systems, this value may not be enough.

Enqueues are shared memory structures that serialize access to database resources. If you set the value of DML_LOCKS to 0, enqueues are disabled and performance is slightly increased. However, you should be aware of the following restrictions when you set you DML_LOCKS to 0:

· You cannot use DROP TABLE, CREATE INDEX statements

· You cannot use explicit lock statements such as LOCK TABLE IN EXCLUSIVE MODE

· Enterprise Manager cannot run on any instances for which DML_LOCKS is set to 0

Oracle holds more locks during parallel DML than during serial execution. Therefore, if your database supports a lot of parallel DML, you may need to increase the value of this parameter.

Note:

1. Setting DML_LOCKS=0 in a Parallel Server environment can help performance as global DML locks are not required. From Oracle 7.2+ onwards DML locks can be disabled on a per table basis with the command ALTER TABLE ... DISABLE/ENABLE TABLE LOCK.

2. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter

where upper(name) = ‘DML_LOCKS’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE	ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
dml_locks	11636	TRUE	FALSE	FALSE	FALSE	FALSE	dml locks - one for each table modified in a transaction

Oracle initializatoin parameters

DISTRIBUTED_LOCK_TIMEOUT

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

====================================================================

Property	Description
Parameter type	Integer
Default value	60
Modifiable	No
Range of values	1 to unlimited
Basic	No

DISTRIBUTED_LOCK_TIMEOUT specifies the amount of time (in seconds) for distributed transactions to wait for locked resources.
Note:
1. This parameter was hidden in 8i and 9.0 and then made available again in 9.2 onwards. The parameter defines the number of seconds that a distributed transaction waits for a lock. If a session waits longer than this for the lock then ORA-2049 is signalled.
2. This timeout applies even to local lock waits if the session is in a distributed transaction. See Metalink Note 19332.1 for an example.
3. This parameter can be useful to set a time-limit on statements. The session only needs to be using a DBLINK for this behaviour (timeouts) to be enabled.
4. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter where upper(name) = ‘DISTRIBUTED_LOCK_TIMEOUT’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE	ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
distributed_lock_timeout	60	TRUE	FALSE	FALSE	FALSE	FALSE	number of seconds a distributed transaction waits for a lock

Oracle initializatoin parameters

DISPATCHERS

Property	Description
Parameter type	String
Syntax	DISPATCHERS = 'dispatch_clause'
	dispatch_clause::= (PROTOCOL = protocol) | (ADDRESS = address) | (DESCRIPTION = description ) [options_clause] options_clause::= (DISPATCHERS = integer | SESSIONS = integer | CONNECTIONS = integer | TICKS = seconds | POOL = {1 | ON | YES | TRUE | BOTH | ({IN | OUT} = ticks) | 0 | OFF | NO | FALSE | ticks} | MULTIPLEX = {1 | ON | YES | TRUE | 0 | OFF | NO | FALSE | BOTH | IN | OUT} | LISTENER = tnsname | SERVICE = service | INDEX = integer)
Default value	If SHARED_SERVERS is greater than 0, then DISPATCHERS defaults to '(PROTOCOL=tcp)', causing 1 TCP/IP dispatcher to be created.
Modifiable	ALTER SYSTEM
Basic	No

DISPATCHERS configures dispatcher processes in the shared server architecture. The parsing software supports a name-value syntax to enable the specification of attributes in a position-independent, case-insensitive manner. For example:

DISPATCHERS = '(PROTOCOL=TCP)(DISPATCHERS=3)'

Attributes may be specified using the full attribute name or any substring beginning with the first 3 characters. For example, SESSIONS can be specified as SES, SESS, SESSI, and so on.

Specify only one of the following attributes: PROTOCOL, ADDRESS, or DESCRIPTION. If you specify either ADDRESS or DESCRIPTION, then you can specify additional network attributes. Doing so supports multi-homed hosts.

dispatch_clause

· PROTOCOL

The network protocol for which the dispatcher generates a listening endpoint.

· ADDRESS

The network protocol address of the endpoint on which the dispatchers listen.

· DESCRIPTION

The network description of the endpoint on which the dispatchers listen, including the protocol address.

options_clause

· DISPATCHERS

The initial number of dispatchers to start. The default is 1.

· SESSIONS

The maximum number of network sessions to allow for each dispatcher. The default is operating system-specific. Most operating systems have a default of 16 KB.

· CONNECTIONS

The maximum number of network connections to allow for each dispatcher. The default is operating system-specific.

· TICKS

The length of a network tick in seconds. The default is 1 second.

· POOL

Enables Connection Pooling.

o An integer indicates that Connection Pooling is enabled for both incoming and outgoing network connections. The number specified is the timeout in ticks for both incoming and outgoing network connections.

o ON, YES, TRUE, and BOTH indicate that Connection Pooling is enabled for both incoming and outgoing network connections. A default timeout of 10 ticks is used for both incoming and outgoing network connections.

o IN indicates that Connection Pooling is enabled for incoming network connections and the default timeout of 10 ticks is used for incoming network connections.

o OUT indicates that Connection Pooling is enabled for outgoing network connections and the default timeout of 10 ticks is used for outgoing network connections.

o NO, OFF, and FALSE indicate that Connection Pooling is disabled for both incoming and outgoing network connections. This is the default.

POOL can also be assigned a name-value string such as: "(IN=10)", "(OUT=20)", or "(IN=10)(OUT=20)". In such cases:

o If an IN numeric value is specified, then Connection Pooling is enabled for incoming connections, and the number specified is the timeout in ticks for incoming network connections.

o If an OUT numeric value is specified, then Connection Pooling is enabled for outgoing network connections, and the number specified is the timeout in ticks for outgoing network connections.

o If the numeric value of a specified timeout is 1, then the default value of 10 ticks is used. If the numeric value is 0, then pooling is not on.

· MULTIPLEX

Enables the Oracle Connection Manager session multiplexing feature.

o The values 1, ON, YES, TRUE, and BOTH indicate that Network Session Multiplex is enabled for both incoming and outgoing network connections.

o The value IN indicates that Network Session Multiplex is enabled for incoming network connections.

o The value OUT indicates that Network Session Multiplexing is enabled for outgoing network connections.

o The values 0, NO, OFF, and FALSE indicate that Network Session Multiplexing is disabled for both incoming and outgoing network connections. This is the default.

· LISTENER

Specifies the network name of an address or address list of the Oracle Net listeners with which the dispatchers will register.

The LISTENER attribute facilitates administration of multi-homed hosts. This attribute specifies the appropriate listeners with which the dispatchers will register. The LISTENER attribute takes precedence over the LOCAL_LISTENER and REMOTE_LISTENER parameters. See "LOCAL_LISTENER" and "REMOTE_LISTENER".

· SERVICE

Specifies one or more names by which clients can connect to the dispatchers. The SERVICE attribute takes precedence over the SERVICE_NAMES parameter.

· INDEX

Use this attribute in an ALTER SYSTEM SET DISPATCHERS statement to indicate which dispatcher configuration you want to modify. (If you specify INDEX in the initialization parameter file, the Oracle Database ignores it.) In an ALTER SYSTEM statement, INDEX specifies the order in which the parameter's values were initialized. The value ranges from 0 (for the first dispatcher configuration) to one less than the total number of dispatcher configurations you define.

For example, if you specify 3 dispatcher configurations in the initialization parameter file, you would modify the third dispatcher configuration by specifying INDEX=2 in the ALTER SYSTEM statement. You could also add an additional dispatcher configuration in the ALTER SYSTEM statement by specifying INDEX=3.

If INDEX is not specified in the ALTER SYSTEM statement, then the PROTOCOL, ADDRESS, or DESCRIPTION attributes must be specified, and if a dispatcher configuration matching this PROTOCOL, ADDRESS, or DESCRIPTION exists, then that configuration will be modified. Otherwise, a new configuration will be added.

Note:

1. This parameter was renamed in Oracle9i. Prior to Oracle9i the MTS_DISPATCHERS was used to specify how many dispatchers to start for each protocol.

2. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter where upper(name) = ‘DISPATCHERS’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE	ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
dispatchers	(PROTOCOL=TCP) (SERVICE=mydbXDB)	FALSE	FALSE	IMMEDIATE	TRUE	FALSE	specifications of dispatchers

Oracle initializatoin parameters

DISK_ASYNCH_IO

Property	Description
Parameter type	Boolean
Default value	true
Modifiable	No
Range of values	true | false
Basic	No

DISK_ASYNCH_IO controls whether I/O to datafiles, control files, and logfiles is asynchronous (that is, whether parallel server processes can overlap I/O requests with CPU processing during table scans). If your platform supports asynchronous I/O to disk, Oracle recommends that you leave this parameter set to its default value. However, if the asynchronous I/O implementation is not stable, you can set this parameter to false to disable asynchronous I/O. If your platform does not support asynchronous I/O to disk, this parameter has no effect.

If you set DISK_ASYNCH_IO to false, then you should also set DBWR_IO_SLAVES to a value other than its default of zero in order to simulate asynchronous I/O.

Note:

1. Related parameter: DBWR_IO_SLAVES, ASYNC_READ (Oracle 7)

2. This parameter is introduced in Oracle 8.0. It replaces various port specific parameters for ASYNCHRONOUS IO in Oracle 7 (eg: ASYNC_READ). If DISK_ASYNCH_IO is set to FALSE, then typically either DBWR_IO_SLAVES DB_WRITER_PROCESSES should be set unless the system is low throughput.

3. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter where upper(name) = ‘DISK_ASYNCH_IO’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE	ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
disk_asynch_io	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	Use asynch I/O for random access devices

Oracle initializatoin parameters