batched allocate scn lock request

A session is waiting on another process to allocate a system change number (SCN). If the foreground timed out waiting on a process to get the SCN, the foreground will get the SCN.

This wait is called by the redo writer routines to allocate a new batch of scn numbers. This wait only occurs for shared instances configurations like Oracle Parallel Servers or Oracle Real Application Clsuters. If a new batch is in the process of being allocated, then we just wait on this event for the allocation to complete. It is also dependent on another process completing the allocation request.

Check for a high number of time-outs on this event. Too many time-outs indicates that there may be to much congestion on the SCN resource or Distributed Lock Manager in general. The SCN propagation is dependent on the max_commit_propagation_delay parameter.

This parameter specifies the maximum amount of time allowed before the system change number (SCN) held in the SGA of an instance is refreshed by the log writer process (LGWR). It determines whether the local SCN should be refreshed by Global Cache Services when getting the snapshot SCN for a query. Units are in hundredths of seconds. Under unusual circumstances involving rapid updates and queries of the same data from different instances, the SCN might not be refreshed in a timely manner. Setting the parameter to zero causes the SCN to be refreshed immediately after a commit. The default value (700 hundredths of a second, or seven seconds) is an upper bound that enables the preferred existing high performance mechanism to remain in place.

The Lamport (default) propagation scheme is very efficient and sufficient for the vast majority of applications. Only a small percentage of RAC production customers have had to change the default

value of this parameter. If one can ensure that the logical work unit is done on a particular instance then there should be no need to change this parameter from its default.

However, if any of the following conditions exist, there may be a need to deviate from the default and explicitly set max_commit_propagation_delay=0.

• The data consistency between the different instances must be guaranteed and immediate (i.e. if commits must be seen instantaneously on remote instances).
• When rapid inserts (or updates) and immediate queries of the same dataset are done from different instances.
• If middle-tier connection pools are being used in tandem with connection load balancing to the RAC instances, and the application is arbitrarily selecting a connection (and hence an instance) for each SQL operation.
• Some packaged applications (e.g. SAP) might recommend setting this parameter to a specific value (mostly 0), in which case you should follow the application vendor's recommendations.

Typically, the issue of data consistency among the different instances can be addressed by making minor adjustments in the application logic. Note that this issue is only DML/query related and as such is a data consistency issue, not a data integrity issue. It is valid to question an application design that requires data on one node/connection immediately after commit on a different node/connection as discrete units of work. In the case of connection pools, the preferred design would involve binding logical units of work to the same connection (and hence instance), rather than having the application use connections in an arbitrary way.

There are a few SCN generation schemes, including some that are platform specific . The most important and most common are the "Lamport" scheme and "Broadcast on Commit" scheme. The scheme chosen is a function of the setting for max_commit_propagation_delay.

The Lamport algorithm is the default scheme whereby SCNs are propagated by piggybacking on normal cluster communications, rather than defining a separate mechanism. The Lamport scheme is essentially a performance optimization that is effective for all but a limited set of applications.

Under very limited, but real world circumstances, it is possible for Instance A's local SCN to lag behind instance B's local SCN, thus instance A may not see all the committed transactions from instance B immediately. Specifically, this can occur when there is very little messaging activity from an instance (instance A), and another instance's local SCN has increased due to committed transaction (instance B).

The Broadcast-on-Commit scheme is marginally more resource intensive than the Lamport Scheme. Under the Broadcase on Commit Scheme, each time LGWR writes to the redo log (that is, with every commit):

• LGWR sends a message to update the global SCN.
• LGWR sends a message to every active instance to update local SCN.

This effectively defines a separate mechanism for keeping SCNs synchronized globally in the cluster. However, this scheme has proven to be a requirement for a small percentage of the applications spectrum that exhibit the problematic behaviour noted above.

The following statsitics are related to these wait events and can be examined for better understanding of these waits.

• calls_to_kcmgcs]
• calls_to_kcmgrs
• calls_to_kcmgas
• Unnecesary_process_cleanup_for_SCN_batching
• calls_to_get_snapshot_scn_kcmgss
• kcmgss_waited_for_batching
• kcmgss_read_scn_without_going_to_DLM
• next_scns_gotten_without_going_to_server
• Unnecesary_process_cleanup_for_SCN_batching
• calls_to_get_snapshot_scn_kcmgss
• kcmgss_waited_for_batching
• kcmgss_read_scn_without_going_to_server

Wait Time:

The wait time is 1 second on the assumption that an SCN allocation should normally need much less than that.