Re: max_commit_propagation_delay

I have recommended setting it to 0 and keep an eye on the waiting that occurs due to this. It this becomes too much, you should consider setting mcpd back to default. Although I do not yet have any hard figures to support it, I think mcpd=0 is a very good choice for 2 node systems, but more than 2 node systems would be hit faster by the broadcast on commit protocol used with mcpd=0.

 Note that the alert.log shows the actual protocol used for SCN distribution.

/Bjørn.

Rajesh.Rao_at_jpmchase.com wrote:

>Thanks for the update Jonathan. The recommendation was made by Oracle
>Support, after they noticed a lot of "on going read of SCN to complete"
>waits from a system dump. Almost 40% of the waits. In some of my statspack
>report, I even see this waits accouting for 70% of the total. Setting this
>parameter to the default seems to be the solution to reduce these resource
>intensive waits, but I saw the same effects as in the first case. and that
>scares me.
>
>I would like to know if DBAs prefer setting it to default, ot set it to 0?
>What would be the criteria for making this decision?
>
>Thanks
>Raj
>
>
>
>
> "Jonathan Lewis"
> <jonathan_at_jlcomp.de To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> mon.co.uk> cc:
> Sent by: Subject: Re: max_commit_propagation_delay
> root_at_fatcity.com
>
>
> 03/26/03 01:04 PM
> Please respond to
> ORACLE-L
>
>
>
>
>
>
>
>
>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
> commit;
>
> 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)
> commit;
>
> 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.
>
>
>
>
>Regards
>
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk
>
>For one-day tutorials:
>(see http://www.jlcomp.demon.co.uk/tutorial.html )
>
>____UK_______April 8th
>____UK_______April 22nd
>____Denmark May 21-23rd
>____USA_(FL)_May 2nd
>
>Next dates for the 3-day seminar:
>(see http://www.jlcomp.demon.co.uk/seminar.html )
>____UK_(Manchester)_May
>____Estonia___June (provisional)
>____USA_(CA, TX)_August
>
>The Co-operative Oracle Users' FAQ
>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>----- Original Message -----
>To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
>Sent: 26 March 2003 14:34
>
>
>
>
>>OPS 8.0.6.3 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
>>
>>
>general
>
>
>>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
>>
>>
>www.ixora.com.au,
>
>
>>though that site has never dissappointed me.
>>
>>Thanks
>>Raj
>>
>>
>>
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Jonathan Lewis
> INET: jonathan_at_jlcomp.demon.co.uk
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (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).
>
>
>
>
>
>
>

-- 
Bjørn Engsig, Miracle A/S
Member of Oak Table Network <http://www.oaktable.net>
Bjorn.Engsig@MiracleAS.dk - http://MiracleAS.dk


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?ISO-8859-1?Q?Bj=F8rn_Engsig?=
  INET: bjorn_at_miracleas.dk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).