Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Truncate performance problems

Re: Truncate performance problems

From: Tim Gorman <>
Date: Tue, 04 May 2004 10:45:31 -0600
Message-ID: <>


Welcome back!

Can you treat TRUNCATE like any other application code and perform SQL tracing on it, then TKPROF or otherwise analyze the results?

If running from SQL*Plus:

    alter session set tracefile_identifier = 'truncate';     alter session set max_dump_file_size = unlimited;     alter session set events '10046 trace name context forever, level 8';     truncate table xxxxx;

Exiting right after the TRUNCATE prevents additional stuff from getting mixed into the trace. Then, go to the USER_DUMP_DEST directory, search for any file named "*truncate*.trc", and run TKPROF and remember to use the "SORT=PRSELA,EXEELA,FCHELA" option to sort statements by elapsed time (assuming that you've got TIMED_STATISTICS=TRUE).

Feel free to post some of the results back to the list?

Hope this helps...


on 5/4/04 10:25 AM, Shawn Ferris at wrote:

> Hey All..
> After several years of being away from this list, I am finally back. And
> sure enough.. I have a question for you all.. (Sybase got in the way of
> real RDBMS work) -- He he
> Over the last year or so.. We've been dealing with an issue where
> truncating a table can take on the upwards of half an hour, or longer.
> These are small-ish tables.. with less than 100 extents.
> Everything we've looked into, we've implemented.. and everytime we
> implement something.. it gets better for a while, but eventually starts to
> degrade again.
> I have not formulated any details to hand out yet, so this is more of a
> general question to see if anyone else has had a similar experience, and
> what if anything they've done to fix it.
> Everytime we run into the issue, it seems a different wait event is
> involved.. This weekend, I had a high wait on 'local write wait'.. Other
> times they've been enqueues on dictionary tables.. (uet$ and fet$ if
> memory serves)

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Tue May 04 2004 - 11:44:28 CDT

Original text of this message