RE: Truncate performance problems
- From: Ravi Kulkarni <kulkarni.ravi@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 04 May 2004 12:31:05 -0500
Shawn -
Even if you use LMTs, there would still be Data Dictionary activity on the TSQ$
table.
Are you noticing the executions of this query (on tsq$) taking the most of your
truncate time ?
Thanks,
Ravi.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Shawn Ferris
Sent: Tuesday, May 04, 2004 11:25 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Truncate performance problems
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)
This is a reporting database that is rapidly approaching 200GB from 90GB
since Nov/ Dec.. We process (if I had to guess) 500 million rows nightly
for report generation/ aggregation.. and other than the truncate issue,
things run well. Occassionally we run into 'cache buffer chains' which
we've always been able to alleviate by tuning the sql.
We've done all the obvious.. 'truncate .. reuse storage'.. Locally managed
tablespaces (uniform - 1 and 20mb for each datasource), etc, etc.
Some other info.. Oracle 9.2.0.4 (we were going to patch to 9.2.0.5, but
we use sql*ldr extensively, and the recent discussion about the sql*ldr
issue with 9.2.0.5 has made me shy away from it until we do proper
testing) 8 x 400MHz, 12gb ram, Sun Solaris 2.8.. Only one other,
insignificant, database on this system.. no middleware/ application
servers. It's pretty much alone.
We are running cooked.. I would love to move to raw but will get pushback.
(I can handle that if it's recommended -- incidently, those who would push
back, are the same that came from a sybase background and run raw for
those database as a rule!?!? Huh.. ok, whatever)
I seem to recall reading an article somewhere, about raw vs. cooked and
that one advantage of raw, is truncate performance.. for the life of me, I
can't find that again. If anyone has insight to that.. please share. 8D --
I have successfully managed a raw environment, so it doesn't scare me on
bit.. in fact, I prefer it due to the flexibility of tuning IO while the
database is live. (Veritas VM)
Anyway.. if anyone has anything we could draw upon.. We'd be forever
grateful.. we're kinda beating our heads try to get at the root cause.
Thanks!
Shawn Ferris
SR. Database Admin
PS: As soon as I can gather some waits stats.. etc.. I'll forward them
along. I know there isn't a lot to go on here, I just haven't been able to
document it much.. it always a firedrill as it's happening. I apologize
for that.
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
