RE: extent deallocation question
Hi Jeremiah, The cross instance call is a reuse block range call, not a checkpoint object call. Thus a separate scan of the cache is needed for every extent. The reason why dirty blocks from dropped extents cannot be allowed to stay in cache is that the blocks might immediately be allocated to another object. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Monday, 29 October 2001 13:55 To: Multiple recipients of list ORACLE-L Thanks for the reply. This is the first time I have heard of an object-level checkpoint. I still don't understand a couple things about this. If the wait is for checkpointing of the blocks composing the deallocated extents, why does a 2Gb segment with 10 extents drop so much faster than a 2Gb segment with 20,000 extents? Does the session performing the DDL require the object checkpoint to complete for every extent deallocated, before the DDL proceeds? Why should such a checkpoint be necessary? Why can't the blocks just go on the LRUW list like all the other dirty blocks? If the speed of these object checkpoints is limited by the checkpoint batch portion of the DBWR write batch, can they be sped up by increasing the value of _db_block_checkpoint_batch to a larger proportion of the DBWR write batch (as ascertained from x$kvii)? It seems like if they just put the blocks from deallocated extents on the dirty list, then the checkpointing could be automatically made more agressive through use of the db_block_max_dirty_target feature. That way as soon as a lot of dirty blocks got put on the LRUW list, the checkpoint portion of the write batch could be automatically increased. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Sun, 28 Oct 2001, [EMAIL PROTECTED] wrote: When you truncate or drop an object, all the extents/buffers associated with that object need to be flushed to the disk, meaning an object checkpoint has to take place. DBW recieves an object checkpoint call, from the client process. Client process then waits for the IPC message from the DBW process for the object checkpoint call completion. The wait event you see is that the client is waiting for this IPC message from DBW. Once DBW completes flushing all the buffers associated with the objects, DBW will send back an IPC message to the client. Interestingly, there is are two lists( main and auxcillary lists) in 8i buffer cache just for this object checkpoint call, which is supposed to improve the performance of these object checkpoint calls. If you have very large buffer cache, DBW can take long time to flush these buffers. If you truncate a table, then all the indexes associated with these tables (an their buffers) need to be checkpointed. Further, only fraction of writes are dedicated for these checkpoints and that could be another reason for longer waits. Jeremiah Wilton [EMAIL PROTECTED] Using dictionary-managed tablespaces, it can sometimes take a very long time to drop or truncate a segment with many tens of thousands of extents, because Oracle takes a long time to update UET$ and FET$. This can be a serious problem for some people because the session performing the DDL holds the ST enqueue for the duration of the extent deallocation. During these long extent deallocations, I observe the session performing DDL waiting on IPC with the database writer. Can anyone tell me why these long extent allocations spend the vast majority of their time waiting on DBW0? Does every block in UET$ and FET$ have to get written out before the deallocate can proceed? If so, why? Using a normal application, I can delete and insert tens of thousands of rows in just a few seconds. What takes Oracle so long with UET$ and FET$? BTW, this is not a problem that I personally have - it is a purely academic question. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: extent deallocation question
Steve I thought the drop/truncate of the objects will do 'checkpoint object ' call and DBW in turn will do extent based checkpointing. I am not even going to try argue with you :-) Could you please explain to us : What are the reasons for 'checkpoint object call ' and ' reuse block range calls ? What statement triggers them ? Thanks in advance, Riyaj Re-yas Shamsudeen Certified Oracle DBA i2 technologies www.i2.com Steve Adams [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/29/01 05:00 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: extent deallocation question Hi Jeremiah, The cross instance call is a reuse block range call, not a checkpoint object call. Thus a separate scan of the cache is needed for every extent. The reason why dirty blocks from dropped extents cannot be allowed to stay in cache is that the blocks might immediately be allocated to another object. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/- For all -Original Message- Sent: Monday, 29 October 2001 13:55 To: Multiple recipients of list ORACLE-L Thanks for the reply. This is the first time I have heard of an object-level checkpoint. I still don't understand a couple things about this. If the wait is for checkpointing of the blocks composing the deallocated extents, why does a 2Gb segment with 10 extents drop so much faster than a 2Gb segment with 20,000 extents? Does the session performing the DDL require the object checkpoint to complete for every extent deallocated, before the DDL proceeds? Why should such a checkpoint be necessary? Why can't the blocks just go on the LRUW list like all the other dirty blocks? If the speed of these object checkpoints is limited by the checkpoint batch portion of the DBWR write batch, can they be sped up by increasing the value of _db_block_checkpoint_batch to a larger proportion of the DBWR write batch (as ascertained from x$kvii)? It seems like if they just put the blocks from deallocated extents on the dirty list, then the checkpointing could be automatically made more agressive through use of the db_block_max_dirty_target feature. That way as soon as a lot of dirty blocks got put on the LRUW list, the checkpoint portion of the write batch could be automatically increased. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Sun, 28 Oct 2001, [EMAIL PROTECTED] wrote: When you truncate or drop an object, all the extents/buffers associated with that object need to be flushed to the disk, meaning an object checkpoint has to take place. DBW recieves an object checkpoint call, from the client process. Client process then waits for the IPC message from the DBW process for the object checkpoint call completion. The wait event you see is that the client is waiting for this IPC message from DBW. Once DBW completes flushing all the buffers associated with the objects, DBW will send back an IPC message to the client. Interestingly, there is are two lists( main and auxcillary lists) in 8i buffer cache just for this object checkpoint call, which is supposed to improve the performance of these object checkpoint calls. If you have very large buffer cache, DBW can take long time to flush these buffers. If you truncate a table, then all the indexes associated with these tables (an their buffers) need to be checkpointed. Further, only fraction of writes are dedicated for these checkpoints and that could be another reason for longer waits. Jeremiah Wilton [EMAIL PROTECTED] Using dictionary-managed tablespaces, it can sometimes take a very long time to drop or truncate a segment with many tens of thousands of extents, because Oracle takes a long time to update UET$ and FET$. This can be a serious problem for some people because the session performing the DDL holds the ST enqueue for the duration of the extent deallocation. During these long extent deallocations, I observe the session performing DDL waiting on IPC with the database writer. Can anyone tell me why these long extent allocations spend the vast majority of their time waiting on DBW0? Does every block in UET$ and FET$ have to get written out before the deallocate can proceed? If so, why? Using a normal application, I can delete and insert tens of thousands of rows in just a few seconds. What takes Oracle so long with UET$ and FET$? BTW, this is not a problem that I personally have - it is a purely academic question. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list
RE: extent deallocation question
Hi Riyaj, The checkpoint object call is used prior to a direct read of an object. DBW0 scans the cache once based on the obj#. This was introduced in 8.0 as an optimization for parallel query. Previously each extent was checkpointed separately. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Tuesday, 30 October 2001 3:10 To: Multiple recipients of list ORACLE-L Steve I thought the drop/truncate of the objects will do 'checkpoint object ' call and DBW in turn will do extent based checkpointing. I am not even going to try argue with you :-) Could you please explain to us : What are the reasons for 'checkpoint object call ' and ' reuse block range calls ? What statement triggers them ? Thanks in advance, Riyaj Re-yas Shamsudeen Certified Oracle DBA i2 technologies www.i2.com Steve Adams [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/29/01 05:00 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: extent deallocation question Hi Jeremiah, The cross instance call is a reuse block range call, not a checkpoint object call. Thus a separate scan of the cache is needed for every extent. The reason why dirty blocks from dropped extents cannot be allowed to stay in cache is that the blocks might immediately be allocated to another object. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Monday, 29 October 2001 13:55 To: Multiple recipients of list ORACLE-L Thanks for the reply. This is the first time I have heard of an object-level checkpoint. I still don't understand a couple things about this. If the wait is for checkpointing of the blocks composing the deallocated extents, why does a 2Gb segment with 10 extents drop so much faster than a 2Gb segment with 20,000 extents? Does the session performing the DDL require the object checkpoint to complete for every extent deallocated, before the DDL proceeds? Why should such a checkpoint be necessary? Why can't the blocks just go on the LRUW list like all the other dirty blocks? If the speed of these object checkpoints is limited by the checkpoint batch portion of the DBWR write batch, can they be sped up by increasing the value of _db_block_checkpoint_batch to a larger proportion of the DBWR write batch (as ascertained from x$kvii)? It seems like if they just put the blocks from deallocated extents on the dirty list, then the checkpointing could be automatically made more agressive through use of the db_block_max_dirty_target feature. That way as soon as a lot of dirty blocks got put on the LRUW list, the checkpoint portion of the write batch could be automatically increased. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Sun, 28 Oct 2001, [EMAIL PROTECTED] wrote: When you truncate or drop an object, all the extents/buffers associated with that object need to be flushed to the disk, meaning an object checkpoint has to take place. DBW recieves an object checkpoint call, from the client process. Client process then waits for the IPC message from the DBW process for the object checkpoint call completion. The wait event you see is that the client is waiting for this IPC message from DBW. Once DBW completes flushing all the buffers associated with the objects, DBW will send back an IPC message to the client. Interestingly, there is are two lists( main and auxcillary lists) in 8i buffer cache just for this object checkpoint call, which is supposed to improve the performance of these object checkpoint calls. If you have very large buffer cache, DBW can take long time to flush these buffers. If you truncate a table, then all the indexes associated with these tables (an their buffers) need to be checkpointed. Further, only fraction of writes are dedicated for these checkpoints and that could be another reason for longer waits. Jeremiah Wilton [EMAIL PROTECTED] Using dictionary-managed tablespaces, it can sometimes take a very long time to drop or truncate a segment with many tens of thousands of extents, because Oracle takes a long time to update UET$ and FET$. This can be a serious problem for some people because the session performing the DDL holds the ST enqueue for the duration of the extent deallocation. During these long extent deallocations, I observe the session performing DDL waiting on IPC with the database writer. Can anyone tell me why these long extent allocations spend the vast majority of their time waiting on DBW0? Does every block in UET$ and FET$ have to get written out before the deallocate can proceed? If so
Re: extent deallocation question
Hi Jeremiah When you truncate or drop an object, all the extents/buffers associated with that object need to be flushed to the disk, meaning an object checkpoint has to take place. DBW recieves an object checkpoint call, from the client process. Client process then waits for the IPC message from the DBW process for the object checkpoint call completion. The wait event you see is that the client is waiting for this IPC message from DBW. Once DBW completes flushing all the buffers associated with the objects, DBW will send back an IPC message to the client. Interestingly, there is are two lists( main and auxcillary lists) in 8i buffer cache just for this object checkpoint call, which is supposed to improve the performance of these object checkpoint calls. If you have very large buffer cache, DBW can take long time to flush these buffers. If you truncate a table, then all the indexes associated with these tables (an their buffers) need to be checkpointed. Further, only fraction of writes are dedicated for these checkpoints and that could be another reason for longer waits. Thanks Riyaj Re-yas Shamsudeen Certified Oracle DBA i2 technologies www.i2.com Jeremiah Wilton [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/28/01 03:35 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:extent deallocation question Using dictionary-managed tablespaces, it can sometimes take a very long time to drop or truncate a segment with many tens of thousands of extents, because Oracle takes a long time to update UET$ and FET$. This can be a serious problem for some people because the session performing the DDL holds the ST enqueue for the duration of the extent deallocation. During these long extent deallocations, I observe the session performing DDL waiting on IPC with the database writer. Can anyone tell me why these long extent allocations spend the vast majority of their time waiting on DBW0? Does every block in UET$ and FET$ have to get written out before the deallocate can proceed? If so, why? Using a normal application, I can delete and insert tens of thousands of rows in just a few seconds. What takes Oracle so long with UET$ and FET$? BTW, this is not a problem that I personally have - it is a purely academic question. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: extent deallocation question
Thanks for the reply. This is the first time I have heard of an object-level checkpoint. I still don't understand a couple things about this. If the wait is for checkpointing of the blocks composing the deallocated extents, why does a 2Gb segment with 10 extents drop so much faster than a 2Gb segment with 20,000 extents? Does the session performing the DDL require the object checkpoint to complete for every extent deallocated, before the DDL proceeds? Why should such a checkpoint be necessary? Why can't the blocks just go on the LRUW list like all the other dirty blocks? If the speed of these object checkpoints is limited by the checkpoint batch portion of the DBWR write batch, can they be sped up by increasing the value of _db_block_checkpoint_batch to a larger proportion of the DBWR write batch (as ascertained from x$kvii)? It seems like if they just put the blocks from deallocated extents on the dirty list, then the checkpointing could be automatically made more agressive through use of the db_block_max_dirty_target feature. That way as soon as a lot of dirty blocks got put on the LRUW list, the checkpoint portion of the write batch could be automatically increased. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Sun, 28 Oct 2001, [EMAIL PROTECTED] wrote: When you truncate or drop an object, all the extents/buffers associated with that object need to be flushed to the disk, meaning an object checkpoint has to take place. DBW recieves an object checkpoint call, from the client process. Client process then waits for the IPC message from the DBW process for the object checkpoint call completion. The wait event you see is that the client is waiting for this IPC message from DBW. Once DBW completes flushing all the buffers associated with the objects, DBW will send back an IPC message to the client. Interestingly, there is are two lists( main and auxcillary lists) in 8i buffer cache just for this object checkpoint call, which is supposed to improve the performance of these object checkpoint calls. If you have very large buffer cache, DBW can take long time to flush these buffers. If you truncate a table, then all the indexes associated with these tables (an their buffers) need to be checkpointed. Further, only fraction of writes are dedicated for these checkpoints and that could be another reason for longer waits. Jeremiah Wilton [EMAIL PROTECTED] Using dictionary-managed tablespaces, it can sometimes take a very long time to drop or truncate a segment with many tens of thousands of extents, because Oracle takes a long time to update UET$ and FET$. This can be a serious problem for some people because the session performing the DDL holds the ST enqueue for the duration of the extent deallocation. During these long extent deallocations, I observe the session performing DDL waiting on IPC with the database writer. Can anyone tell me why these long extent allocations spend the vast majority of their time waiting on DBW0? Does every block in UET$ and FET$ have to get written out before the deallocate can proceed? If so, why? Using a normal application, I can delete and insert tens of thousands of rows in just a few seconds. What takes Oracle so long with UET$ and FET$? BTW, this is not a problem that I personally have - it is a purely academic question. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
