| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: max_commit_propagation_delay
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
> 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Rajesh.Rao_at_jpmchase.com 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).Received on Wed Mar 26 2003 - 16:28:38 CST
 |
 |