Re: slow truncate, "db file parallel write" waits

From: zhu chao <chao_ping_at_vip.163.com>
Date: Tue, 25 May 2004 11:19:01 +0800
Message-ID: <001e01c44207$0721b3b0$2552fc0a@corp.ebay.com>

Hi,

can you try:

truncate table xxx reuse storage.
use global temporary table if possible. 3.place this table into a dedicated small tablespace and do truncate again.

Regards
Zhu Chao.

Original Message ----- From: "Wolfgang Breitling" <breitliw_at_centrexcc.com> To: <oracle-l_at_freelists.org> Sent: Tuesday, May 25, 2004 10:18 AM Subject: slow truncate, "db file parallel write" waits

> Hi everyone,
>
> I hope someone can shed some light on this.
> Here is the situation: Oracle Release 9.2.0.2.0 on Sun Solaris 5.8.
> Tablespaces are LMT.
>
> Truncates are taking exceptionally long. Today I was truncating a table
> after inserting 178301 rows (which took ~26 seconds) and it took over 2
> minutes to truncate. While I was waiting for the truncate to finish I was
> looking at v$session_wait and saw repeated "db file parallel write" all
> with a p1 value of 204. However, the sql trace does not show any "db file
> parallel write"s but instead more than 2 minutes of "rdbms ipc reply", see
> below from the tkprof output:
>
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total
Waited
> ----------------------------------------
Waited ---------- ------------
> SQL*Net message to client 3 0.00
0.00
> SQL*Net message from client 3 35.80
67.42
> SQL*Net break/reset to client 1 0.00
0.00
> rdbms ipc reply 71 2.00
141.16
> db file sequential read 3 0.00
0.00
> local write wait 1 0.08
0.08
> log file sync 1 0.03
0.03
>
> And this is a section of the raw trace following the truncate:
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon May 24 2004 - 22:16:06 CDT