Fast Object Checkpoints
From: Rajesh Rao <Rajesh.Rao_at_jpmchase.com>
Date: Fri, 12 Mar 2010 10:12:17 -0500
Message-ID: <B169B49ADAC13E429204826BD76EC83402D2C780D5_at_EMASC201VS01.exchad.jpmchase.net>
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.
Date: Fri, 12 Mar 2010 10:12:17 -0500
Message-ID: <B169B49ADAC13E429204826BD76EC83402D2C780D5_at_EMASC201VS01.exchad.jpmchase.net>
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 12 2010 - 09:12:17 CST