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. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, July 31, 2003 2:54 AM > 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. > [EMAIL PROTECTED] > > > -----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: [EMAIL PROTECTED] > > 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: [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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: DENNIS WILLIAMS > INET: [EMAIL PROTECTED] > > 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: [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). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Henry Poras > INET: [EMAIL PROTECTED] > > 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: [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). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Henry Poras > INET: [EMAIL PROTECTED] > > 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: [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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] 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: [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).
