Fast Object Checkpoints

I have an SR raised with Oracle support on this, but this list and your blogs 
have been more helpful than Oracle support in the past with such problems. 
Quoting verbatim from the SR I have raised with Oracle support:

An 8-node RAC cluster 10.2.0.3 on Solaris 10. RAID-5. We have online 
applications hitting the first 6 nodes and making DML changes to about 10 
tables. On the 7th node, upon kicking off a batch job, that invokes a select 
with full and parallel hints for few of these tables in a single select 
statement, we see that batch job waiting on an "enq:KO fast object checkpoint". 
We also see sessions on the first 6 nodes waiting on "gc cr request" and "gc 
buffer busy" waits on calls that reference those tables.

I understand that parallel scans need to do direct reads from disk and hence 
the batch session would post the CKPT processes on all nodes to write dirty 
buffers for those objects accessed to disk. The CKPT would post DBWR to do the 
writes. The ODMSTAT data that we collected shows this heavy writes happening on 
the datafiles that hold those objects. However, following this write, the 
reads/writes as observed in odmstat suddenly drop . I guess that this drop in 
i/o activity is because we see plenty of sessions are waiting on the "gc" waits 
mentioned earlier, on calls that reference those tables. After anywhere from 2 
to 90 seconds, all the waits dissappear, the batch continues to work and the 
application is back to normal.

Question: What happens after a session initiates a fast object checkpoint for a 
table in a RAC environment? Why do we see sessions that access those some 
tables start waiting on "gc" related waits? Does Oracle lock up access to all 
buffers until all the instances have completed the writes to disk? Is there a 
way to ease the impact from it that we see?

I understand fast object checkpoint can be disabled by setting 
"_db_fast_obj_ckpt"=false". What is the impact to parallel scans with this 
setting? Any other adverse impact from setting this parameter?

I believe I am asking a general question that won't need any 
AWR's/RDA's/Systemdumps or hanganalyze traces to be uploaded. I am also not 
soliciting advice on how to tune parallel queries or the preferred storage for 
write intensive applications. If this needs to go to a specific group, please 
direct it.

Thanks
Raj


This communication is for informational purposes only. It is not
intended as an offer or solicitation for the purchase or sale of
any financial instrument or as an official confirmation of any
transaction. All market prices, data and other information are not
warranted as to completeness or accuracy and are subject to change
without notice. Any comments or statements made herein do not
necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
and affiliates.

This transmission may contain information that is privileged,
confidential, legally privileged, and/or exempt from disclosure
under applicable law. If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
use of the information contained herein (including any reliance
thereon) is STRICTLY PROHIBITED. Although this transmission and any
attachments are believed to be free of any virus or other defect
that might affect any computer system into which it is received and
opened, it is the responsibility of the recipient to ensure that it
is virus free and no responsibility is accepted by JPMorgan Chase &
Co., its subsidiaries and affiliates, as applicable, for any loss
or damage arising in any way from its use. If you received this
transmission in error, please immediately contact the sender and
destroy the material in its entirety, whether in electronic or hard
copy format. Thank you.

Please refer to http://www.jpmorgan.com/pages/disclosures for
disclosures relating to European legal entities.

Other related posts: