Re: How to revert to DMTS ?
Date: Sat, 02 Oct 2010 13:06:02 +0200
Message-ID: <4ca7110d$0$14246$ba620e4c_at_news.skynet.be>
ddf schreef:
>
> I'm lost as to why you'd think that a DTMS would reduce your undo,
> improve multiple concurrent insert/delete times and speed up the full
> table scans because I cannot understand how that type of tablespace
> configuration would help matters. Have you checked the wait stats for
> what these transactions might be waiting on? Have you looked to the O/
> S to see if there are any bottlenecks in network traffic or the I/O
> interface? Have you checked metalink (MOS) for asynch io bugs/fixes?
> Large transactions generate large amounts of undo regardless of
> whether the tablespace is dictionary managed or locally managed. To
> what value is your undo_retention parameter set? Is the undo
> tablespace set to guarantee the retention? Large undo_retention
> values can cause Oracle to extend the UNDO tablespace to accomodate
> transactions and to enforce the retention period; guaranteed retention
> can affect that further by preventing Oracle from 'stealing' undo
> space that's still retained but sits unused (it doesn't happen often
> but it is possible).
>
> It looks like more investigation is in order before you state, with
> certainty, that a dictionary managed tablespace will fix all of your
> ills.
>
>
>
> David Fitzjarrell
I've been investigating this application and its database for two years now, mostly tuning the queries it "generates". The "application" was probably originally written for a database system that did not support updates, so everything is delete and insert, a part from the select statements that are 20k long.
Only recently I got an error and while investigating, found that there is a bug in the 10.2.0.1 in combination with indexes and ASSM and heavy deletes and inserts. I'll be a month before we upgrade the production system, and in the mean time I wanted to try DMTS or MSSM. The database couldn't handle DMTS anymore, so MSSM it was. Tried that the previous night, but yesterday we had as much undo as usual.
It's weekend now, but monday I'll run through your checklist. Received on Sat Oct 02 2010 - 06:06:02 CDT