Re: slow truncate, "db file parallel write" waits
- From: "zhu chao" <chao_ping@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 25 May 2004 11:19:01 +0800
Hi,
can you try:
1. truncate table xxx reuse storage.
2. 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@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
