Re: Truncate performance problems
- From: Tim Gorman <tim@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 04 May 2004 10:45:31 -0600
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@xxxxxxxxxxxxxx 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@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:
