关于oracle rac scn 产生方法

大家都知道oracle rac 的scn产生方法有两种，但是是通过哪个参数来控制这个方法的可能不知道，这里有一边oracle内部文档，大家看一看。

DeployingReal Application Clusters:

SettingMAX_COMMIT_PROPAGATION_DELAY 

Kirk McGowan, Vijay Lunawat, ServerTechnologies

 

Introduction

MAX_COMMIT_PROPAGATION_DELAY is a parameterspecific to Real Application Clusters. It should not be changed from itsdefault value except under a limited set of circumstances. This articleoutlines the issues and considerations in changing this parameter from itsdefault value.

Definition

Oracle9i Release 2 Referencemanual defines MAX_COMMIT_PROPAGATION_DELAY as follows:

“This parameter specifies the maximum amount oftime allowed before the system change number (SCN) held in the SGA of aninstance is refreshed by the log writer process (LGWR). It determines whetherthe local SCN should be refreshed by Global Cache Services when getting thesnapshot SCN[1]for a query. Units are in hundredths of seconds. Under unusual circumstancesinvolving rapid updates and queries of the same data from different instances,the SCN might not be refreshed in a timely manner. Setting the parameter tozero causes the SCN to be refreshed immediately after a commit. The defaultvalue (700 hundredths of a second, or seven seconds)[2] is an upper bound thatenables the preferred existing high performance mechanism to remain in place.” 

BestPractice: Leave Max_commit_propagation_delay=700 (default value[3])

The Lamport (default) propagation scheme isvery efficient, and sufficient for the vast majority of applications. Only asmall percentage of RAC production customers have had to change the defaultvalue of this parameter. If one can ensure that the logical work unit is doneon a particular instance then there should be no requirement to need to changethis parameter from its default.

 

However, if any of the following conditionsexist, there may be a need to deviate from the default and explicitly setmax_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 consistencyamongst the different instances can be addressed by making minor adjustments inthe application logic. Note that this issue is only DML/query related, and assuch is a data consistency issue, not a data integrity issue. Itis valid to question an application design that requires data on onenode/connection immediately after commit on a different node/connection, asdiscrete units of work. And in the case of connection pools, the preferreddesign would of course involve binding logical units of work to the sameconnection (and hence instance), rather than having the application useconnections in an arbitrary way.

Background

There are a few SCN generation schemes, including some thatare platform specific[4]. The most important andmost common are the “Lamport” scheme and “Broadcast on Commit” scheme. Thescheme chosen is a function of the setting for max_commit_propagation_delay.

There is also a large range ofvalues that can be used for max_commit_propagation_delay. Values other thanthose discussed in this paper are not recommended, and exist primarily forhistorical reasons.

max_commit_propagation_delay= 700 (Lamport Scheme) [Default behaviour]

The Lamport algorithm is the default scheme whereby SCNsare propagated by piggybacking on normal cluster communications, rather thandefining a separate mechanism. The Lamport scheme is essentially a performanceoptimization that is effective for all but a limited set of applications.

Under verylimited, but real world circumstances, it is possiblefor Instance A’s local SCN to lag behind instance B’s local SCN, thus instanceA may not see all the committed transaction from instance B immediately.Specifically, this can occur when there is very little messaging activity froman instance (instance A), and another instance’s local SCN hasincreased due to committed transaction (instance B).

max_commit_propagation_delay=0 (Broadcast-on-Commit Scheme)

The Broadcast-on-Commitscheme is marginally more resource intensive than the Lamport Scheme.

·      Each time LGWR writes to theredo log (that is, with every commit):

o   LGWR sends a message to update the global SCN.

o   LGWR sends a message to every active instance to update local SCN.

This effectively defines a separate mechanism for keepingSCNs synchronized globally in the cluster. However, this scheme has proven tobe a requirement for a small percentage of the applications spectrum thatexhibit the problematic behaviour noted above.

 

Determining SCN Generation Scheme

Since the implementation is platform specific, the SCNpropagation scheme that was actually selected can be determined from thealert.log. e.g. "Picked % scheme to generate SCNs".

Following are the excerpts from alert.log for various values of thisparameter:

 

<<<< max_commit_propagation_delay=700, default, HP-UX >>>> This effectivelydefines a separate mechanism for keeping SCNs synchronized globally in thecluster. However, this scheme has proven to be a requirement for a smallpercentage of the applications spectrum that exhibit the problematic behaviournoted above.

 

Tue Oct 28 16:45:57 2003

ALTER DATABASE OPEN

This instance was first to open

Picked Lamport schemeto generate SCNs

Tue Oct 28 16:45:58 2003

LGWR: Primary database is in CLUSTER CONSISTENTmode

 

<<<< max_commit_propagation_delay=0, HP-UX >>>>

 

Tue Oct 28 16:19:39 2003

ALTER DATABASE OPEN

This instance was first to open

Picked broadcast on commitscheme to generate SCNs

Tue Oct 28 16:19:39 2003

LGWR: Primary database is in CLUSTER CONSISTENTmode

 

<<< max_commit_propagation_delay=0, HPTru64, default value>>>>>>

.....................

.....................

Wed Oct 29 06:54:06 2003

ALTER DATABASE OPEN

This instance was first to open

SSN Service opened - initial SCN: 0x0000.00c2df97

Picked Lamport Serverscheme to generate SCNs

---

[1]Snapshot SCN – Refers to the SCN at the start of the query.

[2]The default value on HP Tru64 platform is max_commit_propagation_delay=0.

[3] OnHP Tru64, up to and including versions 9.2.0.4, we recommend setting max_commit_propagation_delay=1 (default is 0).

 

[4]The only platform specific scheme used at this time is the default in Tru64. Inthis case, it uses the cluster memory channel for SCN propagation.