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
|
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).
