RE: waiting for rdbms ipc reply from checkpoint

Henry

   Dictionary-managed tablespaces? (not locally managed or LMT)

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Wednesday, July 30, 2003 12:50 PM
To: Multiple recipients of list ORACLE-L

I'm writing on behalf of the other DBA here. He is trying to drop all objects in a schema. First he truncated all tables, and now is running a script to drop all tables. It is running very slowly. We turned on a 10046 trace, and by far (almost 100%) the largest wait is 'rdbms ipc reply'. Some of the waits had elapsed times in seconds. When I looked at the p1 value
(pid) and queried v$process, it turned out that this session was waiting for
a reply from CHECKPOINT.

Why is a user process communicating with ckpt? The actual number of checkpoints in the system are minimal (right now the instance has been up for ~40minutes [he just bounced it to try some stuff], and the number of background checkpoints completed is 1, the number of DBWR checkpoints is 4).

I did find something by Jonathan Lewis on extent-based checkpointing
(www.jlcomp.demon.co.uk/extent.html). The db flushes all blocks related to
the object from the buffer cache prior to dropping the table. He said this changed in Oracle8 (we are running 8.1.7.4). Also, changing the tablespace to READ ONLY should prevent flushing of the buffer cache. The other DBA here said he tried this to no avail.

Any clues?

Henry

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Henry Poras
  INET: hporas_at_etal.uri.edu

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).