Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: max_commit_propagation_delay

Re: max_commit_propagation_delay

From: Jonathan Lewis <>
Date: Wed, 26 Mar 2003 10:04:24 -0800
Message-ID: <>

It looks to me as if you've worked out exactly what the parameter does already.

Under OPS, (and RAC) each instance has its own SCN range, and the ranges are not re-synchronised in real time (in general). SCNs are resynchronised by piggybacking when messages pass from instance to instance. However, the max_commit_propagation_delay sets an 'idle time' in hundredths of a second to limit the possible resynch delay. If no convenient message has passed between instances for that period of time then an explicit SCN message is sent.

Consequently it is possible (in fact very easy) to produce the following effect:

    Instance A at time t0

        insert row XYZ

    instance B at time t0 + delta
        select row XYZ
        no rows returned.

It should also be possible to produce the effect:

    instance A at time t0

        select 10,000 rows from table T1
            fetch first 100 rows
            fetch next 100 rows
            fetch next 100 rows

    instance B at time t0 + delta
        insert into t1 values (XYZ)

    instance A at time t0 + a bigger delta
            fetch next 100 rows - including (XYZ)

To date I haven't managed to make this second anomaly appear on 9.2 RAC, though - but I'm still trying.

If you set the max_commit_propagation_delay to zero, then neither of these anomalies should be possible as there will be no delay in propagating SCNs - but the overhead is likely to be significant as it probably entails a synchronous message to be sent through GES (or LCK0 as it would be in 8.0.6)

Note for 9i / RAC - (and possibly earlier) although there is a default 7 seconds set for this parameter, the effect of the running checkpoint mechanism seems to force a cross-instance message every three seconds anyway.


Jonathan Lewis

For one-day tutorials:
(see )

____UK_______April 8th
____UK_______April 22nd
____Denmark May 21-23rd
____USA_(FL)_May 2nd

Next dates for the 3-day seminar:
(see )

____Estonia___June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ

> OPS on Sun Nodes. We have this currently set to 0, and
Oracle has
> recommended that we leave it at the default of 700, while making a
> recommendation to improve the performance of this database. But from
what I
> have gathered so far from Metalink, this would not suit us, since,
> transactions would immediately need to see the data on the other
node. Is
> my assumption correct? Can someone throw more light on this
parameter? Any
> good links? Strangely, I did not find any reference at,
> though that site has never dissappointed me.
> Thanks
> Raj

Please see the official ORACLE-L FAQ:
Author: Jonathan Lewis

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Wed Mar 26 2003 - 12:04:24 CST

Original text of this message