| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: waiting for rdbms ipc reply from checkpoint
SQL> SELECT TABLESPACE_NAME, EXTENT_MANAGEMENT, CONTENTS
2 FROM DBA_TABLESPACES
3 WHERE TABLESPACE_NAME='PSTEMP';
TABLESPACE_NAME EXTENT_MAN CONTENTS ------------------------------ ---------- --------- PSTEMP DICTIONARY TEMPORARY
SQL> select *
2 from v$tempfile;
no rows selected
SQL> SELECT *
2 FROM V$TABLESPACE
3 WHERE TS#=3;
TS# NAME
---------- ------------------------------
3 PSTEMP
SQL> SELECT TS#, FILE#
2 FROM SYS.SEG$
3 WHERE TS#=3;
TS# FILE#
---------- ----------
3 4
Henry
-----Original Message-----
Tanel Poder
Sent: Wednesday, July 30, 2003 8:34 PM
To: Multiple recipients of list ORACLE-L
Hi!
Are you sure your TEMP tablespace is dictionary managed? If they're locally managed (tempfiles) then in trace the file# from v$tempfile is actually added the value of db_files parameter.
Tanel.
> 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).
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: tanel.poder.003_at_mail.ee 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).Received on Wed Jul 30 2003 - 20:09:23 CDT
 |
 |