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

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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: