| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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.
 |
 |