Re: Truncate performance problems

From: Tim Gorman <tim_at_sagelogix.com>
Date: Tue, 04 May 2004 10:45:31 -0600
Message-ID: <BCBD24CB.14500%tim@sagelogix.com>

Shawn,

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;
exit

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...

-Tim

on 5/4/04 10:25 AM, Shawn Ferris at shawn_at_virtualsmf.net 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: 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 Tue May 04 2004 - 11:44:28 CDT