Re: How to revert to DMTS ?
Date: Fri, 1 Oct 2010 17:35:17 -0700 (PDT)
Message-ID: <6244705c-d2d9-4247-9bb6-8eb9854e98e0_at_k17g2000prf.googlegroups.com>
On Sep 30, 3:21 pm, "Gerard H. Pille" <g..._at_skynet.be> wrote:
> Mladen Gogala wrote:
> > On Thu, 30 Sep 2010 19:43:41 +0200, Gerard H. Pille wrote:
>
> >> Hallo,
>
> >> for performance reasons I need a couple of dictionary managed
> >> tablespaces
>
> > What, exactly, is the performance problem that the dictionary managed
> > tablespaces would solve?
>
> Excessive undo on intensive deletes and inserts in indexes, multiple sessions inserting (DMTS +
> freelists) and slower full table scans.
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 Received on Fri Oct 01 2010 - 19:35:17 CDT