I can't think of a reason why RO would be acquired when there is no need
to invalidate the buffers. Did you review the statistics section of the
statspack report to see whether any other statistics is offbase
indicating abnormal buffer cache activity ?
If not, turn on sqltrace and see which statement is causing these
enqueue waits.
I am hesitant to think that frequent logoff/logon combined with the use
of global temporary table to be a cause. Since I don't have your
version, I can' t test that out either.
Thanks
Keith Moore wrote:
Thanks.
The database is version 10.1.0.3.
The strange thing is that no objects are being dropped. And certainly not 5180 times, like statspack shows.
Could this be a bug that 10.1.0.4 would fix? We have a strange situation here where the Sysadmin installs the softwarwe and then I take over. I've been trying to get him to upgrade to 10.1.0.4, but without success.
It also makes it interesting when something doesn't work and you have to figure out what he might have done wrong.
Keith
Keith RO enqueue is acquired while dropping/truncating the objects. Drop /truncate will post the DBWR to flush the buffers associated with those objects. While DBWR is performing this operation, foreground process will wait for RO enqueue. Usually RO can be seen with CI (Cross Instance invalidation ) enqueues even in non-rac single instances
One of the primary reason is that DBWR is slow or buffer cache is too big.
What is surprising though, this behavior is very visible up to 9i. But, in 10g, drop will simply rename the objects ( for flashback feature). Clearing the recycle bin will take care of flushing the buffers and so this is not a bigger issue anymore. Did you disable the flashback feature by any chance ?
BTW, 10.0.3 looks too low of a version. So, far I have seen 10.1.0.4 to be a stable release.
Thanks
Riyaj "Re-yas" Shamsudeen Certified Oracle DBA (ver 7.0 - 9i) Allocation & Assortment planning systems JCPenney
Keith Moore wrote:
Does anyone have information on this?
We have setup a two node 10g RAC database, Oracle version 10.0.3.
I ran statspack for a test and this shows up as the top wait time in one instance with 3 minutes of wait time during a 12 minute test. Interestingly, it has less than one second of wait on the other instance.
Here is the statspack info:
Enqueue Type (Request Reason) ------------------------------------------------------------------------------ Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms) ------------ ------------ ----------- ----------- ------------ -------------- RO-Multiple Object Reuse (fast object reuse) 5,180 5,180 0 2,217 184 83.00 TX-Transaction (row lock contention) 23 23 0 23 1 49.57
Any help would be greatly appreciated.
Keith
-- //www.freelists.org/webpage/oracle-l
The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer.
-- //www.freelists.org/webpage/oracle-l
The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer.
