RE: waiting for rdbms ipc reply from checkpoint

One more piece of strange information.

The update seg$ command, which makes sense for dictionary managed, is actually for a dictionary managed tablespace. The SQL is

update seg$
set ...
where ts#=3 --this corresponds to our TEMP ts and file#=(different file#'s were in my trace, which is only a slice) --neither file#

                                                          --corresponds to
the TEMP data file
and   block#=1     --some kind of header block

Interestingly, although this combination of ts# and file# doesn't exist now, EXEC has r=1

This database is a clone so it is up, but is a relative newborn.

Henry

-----Original Message-----
Henry Poras
Sent: Wednesday, July 30, 2003 3:35 PM
To: Multiple recipients of list ORACLE-L

Most are local (except for SYSTEM, TEMP, and a few small ones).

I do have a couple of more clues.

First, a quick, incomplete look at the 10046 trace shows me that the ipc reply waits all come during updates/deletes of seg$ (not all updates -> wait, but all waits [I think] come from
seg$ activity).

Secondly, the other DBA found that running other queries simultaneously
(select count(*) from dba_objects, or splitting the drop script in two and
running them concurrently) speeds things up.

Henry

-----Original Message-----
DENNIS WILLIAMS
Sent: Wednesday, July 30, 2003 2:05 PM
To: Multiple recipients of list ORACLE-L

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

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