2006 update: I notice that 10gR2 documentation says this parameter becomes deprecated. The very latest 10gR2 Database Reference (Part Number B14237-02, but not B14237-01, although both are for 10gR2) says it defaults to 0, a big contrast with their previous claim that it should NOT be changed from the default 7 seconds[note1] to 0 unless in extreme cases! There was an old Bug 4065836 in 2004 and 2005 apparently pushing for 0, "BROADCAST ON COMMIT SHOULD BE DEFAULT IN 10GR2 FOR RAC INSTALLATIONS". 2007 update: The behavior in 10gR2 is actually controlled by _immediate_commit_propagation, which defaults to true. I don't know if max_commit_propagation_delay is still honored. -------------------------------------------------------------------------------- One problem with too many nodes / instances in RAC is indirectly related to the setting of MAX_COMMIT_PROPAGATION_DELAY. Contrary to Oracle's belief, many applications do require this parameter to be set to 0 to avoid read inconsistency in the application, unless you don't use load balance. (By this, I mean when you commit and immediately read the result, you sometimes see a result as if the previous commit was not done, because this subsequent read goes to an instance different from where commit was sent. I don't mean any data corruption inside the database.) This problem is more common than Oracle thought and the only solution is setting that parameter to 0, in spite of Oracle's warning. Why is this related to number of instances of RAC? Because if you have only two instances, setting this parameter to 0 causes no or very little performance overhead. But with more instances, God forbid 128, setting it to 0 causes a very noticeable delay on each commit, no matter how fast cross-instance communication is. (http://www.itpub.net/440557,2.html "Oralce Japan demos 128 node Oracle10g RAC") -------------------------------------------------------------------------------- OFFICIAL NOTE Note:259454.1 (MAX_COMMIT_PROPAGATION_DELAY In A Real Application Clusters Environment) "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. ... - 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. ... max_commit_propagation_delay =0 (Broadcast-on-Commit Scheme): The Broadcast-on-Commit scheme is marginally more resource intensive than the Lamport Scheme." Note:Note:285267.1 (Oracle E-Business Suite 11i and Database FAQ) "It is recommended to set set the init.ora parameter max_commit_propagation_delay= 0. For Compaq Tru64 clusters only, please set max_commit_propagation_delay= 1" -------------------------------------------------------------------------------- CASES WHERE SETTING max_commit_propagation_delay TO 0 IS DONE Our experience 9.2.0.6 2-node RAC on Linux, with client-side load balance. Setting it to 0 solved the above said problem very nicely. http://metalink.oracle.com/metalink/plsql/ml2_documents.showFOR?p_id=484725.996 Oracle recommends setting it to 0 in that case. http://metalink.oracle.com/metalink/plsql/ml2_documents.showFOR?p_id=390252.996 Oracle cautions about setting it to 0. But a user writes: "I haven't notice any negative result in setting the max_commit_propagation_delay=0, It was necessary for us as user operating on different part of our lab depend on data enter from each other and their processing requires the data to be available immediately. It may be just 7 seconds, but that is too long for somethings :)" http://metalink.oracle.com/metalink/plsql/ml2_documents.showFOR?p_id=508548.996 MAX_COMMIT_PROPAGATION_DELAY=0? Other options? Oracle: "The only other solution would be a total partition of the application across the available node." A user: "instead of changing the max_commit_propagation_delay parameter to 0, we have modified the application to let its first session to load balance (i.e. connect normally) and then make sure that the other sessions connect to the same node/instance. This can be done by querying the v$instance view with "SELECT instance_name FROM v$instance" and then using the value returned in the connect string of the other sessions." http://metalink.oracle.com/metalink/plsql/ml2_documents.showNOT?p_id=274461.1 Exception Encountered: Core Dump Ksliwat Ora-07445 Affecting 9.2.0.5 Fix: Setting MAX_COMMIT_PROPAGATION_DELAY=100 or less than 700 solved the problem http://metalink.oracle.com/metalink/plsql/ml2_documents.showNOT?p_id=154076.1 Data Are Not Available Immediately After Insert on Another Instance 8.1.7 OPS Oracle recommends setting it to 0. http://metalink.oracle.com/metalink/plsql/ml2_documents.showNOT?p_id=295711.1 http://metalink.oracle.com/metalink/plsql/ml2_documents.showNOT?p_id=304328.1 For Portal 10.1.2.0.0, Oracle recommends setting it to 0. http://metalink.oracle.com/metalink/plsql/ml2_documents.showNOT?p_id=216205.1 http://metalink.oracle.com/metalink/plsql/ml2_documents.showNOT?p_id=272227.1 In Oracle Apps, Oracle recommends setting it to 0. http://www.oracle.com/technology/products/ifs/sample_code/CMSDKAndRAC/oraclecmsdkandrac.html In Oracle CM (Content Management), Oracle recommends setting it to 1. http://groups-beta.google.com/group/comp.databases.oracle.server/msg/8092fe63fd05fd5d?hl=en "We changed the setting to 0 (have not seen a performance hit) and the problem went away." http://www.mail-archive.com/oracle-l@fatcity.com/msg70093.html Very sensible discussion. The whole thread is informative. Bjorn Engsig suggests setting it to 0 for 2-node RAC should not be a problem, but for 8-node... which makes sense considering the broadcast on commit scheme. [20051222] Installed Oracle Application Server identity management and repository. Selected Replication, among other things. In the infrastructure database created by selecting these options, max_commit_propogation_delay is set to 0. -------------------------------------------------------------------------------- PROBLEMS http://metalink.oracle.com/metalink/plsql/ml2_documents.showNOT?p_id=2787377.8 Bug 2787377 Wrong results possible in RAC when MAX_COMMIT_PROPOGATION_DELAY=0 Affecting Versions >= 9 but < 10G http://metalink.oracle.com/metalink/plsql/ml2_documents.showNOT?p_id=1356901.8 Bug 1356901 MAX_COMMIT_PROPOGATION_DELAY=0 does not propogate change to other node immediately. Affecting Versions >= 8 but < 9.0. Workaround: set _scn_scheme="Broadcast_SCN_on_commit" ____________ [note1] Documentation older than and including B14237-01 for 10gR2 says this parameter defaults to 7 seconds. But Tru64 is actually an exception. See Bug:3056224,3690896.