RE: does oracle use block level locking with RAC?

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Fri, 9 May 2008 15:04:34 -0400
Message-ID: <21469B88E0EA11498818517F2103353101C66369@EPRI17P32001A.csfb.cs-group.com>


With RAC, Oracle has to maintain "cache coherency". Because there's more than one instance, and therefore more than one SGA, a block can reside in more than one SGA, and the usual locking mechanisms of a single instance aren't sufficient. If instance A is making changes to a block, and instance B wants to make changes, instance B has to wait, and then have the block transferred to its SGA over the cluster interconnect before it can make changes. These waits will usually show up as "gc buffer busy" waits, "gc" meaning "global cache."  

Oracle makes a big deal of "Cache Fusion", which transfers blocks over the interconnect, but in reality if you have a lot of inter-instance contention for blocks, performance will usually be very poor.  

Paul Baumgartel
CREDIT SUISSE
Information Technology
Prime Services Databases Americas
One Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com  


From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ric Van Dyke
Sent: Friday, May 09, 2008 2:52 PM
To: ricks12345_at_gmail.com; oracle-l_at_freelists.org Subject: RE: does oracle use block level locking with RAC?

There are locks and then there are locks.  

Your right Oracle locks ROWs not blocks. However, when in RAC only one instance "owns" the block at a given time. I forget all the techno garble to describe the mechanism right now but when another instance wants to change a block it requests to be the owner of that block if it isn't at the time the change is being made. So it sure seems like a lock because you can't access the block until you own it. But it's not a lock in the sense that most of us think of a lock. Is that confusing enough? :-)  


Ric Van Dyke

Hotsos Enterprises


Hotsos Symposium 2009 dedicated to performance and nothing but performance

March 8 - 12, 2009 in Dallas, Texas

Be there.


From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rick Ricky
Sent: Friday, May 09, 2008 2:39 PM
To: oracle-l_at_freelists.org
Subject: does oracle use block level locking with RAC?  

I saw on a board somewhere that someone is having deadlock issues due to block level locking with Oracle RAC? I thought Oracle always did row level locking on everything? Could this be a mis-perception with something else?



Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html


--
http://www.freelists.org/webpage/oracle-l
Received on Fri May 09 2008 - 14:04:34 CDT

Original text of this message