Charles Hooper schreef:
Since I could not create a DMTS, I created a LMTS with MSSM, and moved the
indexes to that tablespace during the night.

But today we had as much undo as usual.

So I'll have to wait until we get 10.2.0.5 and hope that helps.

During the move, I did not specify freelists. But I don't think they would have
brought the undo down.

Mladen Gogala schreef:
Sadly, it's something the company bought and for a number of years we'll have to
live with it, if we ever rewrite it ourselves.

On Sep 30, 3:21*pm, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:
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

ddf schreef:
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.

Gerard:

# I've been investigating this application and its database for two
years now, mostly tuning the queries it "generates".

Well 10.2.0.4 has been out a really really long time ( and now
10.2.0.5 ) but you were so busy that missed out on all the maintenance
that is missing by running 10.2.0.1 ( base release )?

At the point you are currently at I would recommend that you read Cary
Millsaps book "Optimizing Oracle Performance". Maybe follow that up
with Tom Kytes new book and/or Troubleshooting Oracle Performance by
Christian Antognini.

John Hurley wrote:
This system is less than three years old, I've got no idea why system management went for a base
release at that time. But it's clear I should have taken a step back and that I should have
seen we were years behind with the database version. Not that I should expect too much, the
application is such a monster that small increase in performance on the DB side will be, what we
call, "a drop on a hot plate".