I am engaged in a discussion about moving from 10g --> 11g. The
tablespaces in this particular 10G are all created with the manual
segment space management. I am reading the blogs and am aware of all the
troubles that ASSM can cause, ranging from wasting space to problems with
the free lists and even some spurious corruption issues.
However, Oracle made ASSM default in version 10g, and this project is
about upgrading a large production DB. What are the opinions here? Does
it make sense to go with manual SSM or should I go with ASSM?

--
http://mgogala.byethost5.com

On Jan 25, 9:41*am, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:
We're running 11.2.0.2 with ASSM tablespaces and have found no issues
in doing so. I will say that the client using this system (2-node RAC
on Linux) has a pretty 'tame' system but it does tend to have heavy
activity spurts during the year. Prior to that the system was on
10.2.0.4, again with ASSM, and no ill effects have been experienced.
I've also read the blogs but, knock on wood, we're still unaffected by
the reported issues.


David Fitzjarrell

Mladen Gogala wrote:
We have a couple of tables in which there are continuous inserts and deletes, at a dreadful
rate, and we have to rebuild the indexes every week, to keep size and performance in check.

Could not fall back to MSSM, because system is also ASSM, I seem to remember.

On Jan 25, 1:21*pm, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:
Richard Foote discussed index growth in a blog post a couple of weeks
ago and through some testing of my own I discovered a reverse-key
index could help keep the index size down:

http://oratips-ddf.blogspot.com/2012...uve-grown.html

It might help in your situation.


David Fitzjarrell

On Jan 25, 9:21*pm, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:
"To keep size and performance in check"; I understand the size issue,
but how do you measure the performance of your indexes before & after
the rebuild? Do you really have significant performance improvements
after rebuilding the indexes?

Matthias

On Thu, 26 Jan 2012 01:01:18 -0800, mhoys wrote:


I was actually looking for any argument against ASSM. The app to be
converted uses 8i philosophy with uniform extents and manual segment
management. As a result, there is a genuine tablespace zoo. Indexes and
data are separated, a relict from the good old times when DBA could
specify disk devices, and the tablespaces are divided in 3 categories, S,M
and X. There is a bitter opposition to the idea of having auto-allocate
tablespace with ASSM, one for each logical part.
I wonder what kind of argument can be made in favour of such 8i
organization, disguised as 10G database? So far, no argument has been
presented to me, that's why I'm asking.


--
http://mgogala.freehostia.com

On 31 Jan., 03:37, Noons <wizofo... (AT) gmail (DOT) com> wrote:
Yawn... With apologies to Oscar Wilde: People who think they are
always right tend to irritate those of us who are.

On Jan 31, 4:59*am, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:
A while back Niall posted a script on his site, and a question about
this on asktom. I tried a variant of it, basically, creating some
dozens of tables to fill a tablespace, dropping every other one, and
adding them back, in short order there was an out of space error.
Jonathan weighed in with the answer, which was something like the
objects have to be created on a boundary of the segment size, so the
power of 2 can burn you.
http://asktom.oracle.com/pls/asktom/...44747021484934

Since I have an ERP with various sizes, I use auto allocate, and
haven't had any issues like this. As bad as some of the old 4GL I'm
using is, at least it doesn't ordinarily do the strange DDL to see
this type of problem. I would segregate based on segment size, but
have never gotten around to it, the rare times it would make a
difference I just handle with moves or reloads anyways. I do have
some volatile tables, those are in their own ts, simply because they
have been since 8.0.

If I had Noon's data, I probably would do it his way. More
generically, autoallocate is fine, I wouldn't go as far as saying
uniform is evidence of compulsive tuning, though in some cases I can't
help but think it.

jg
--
http://www.maximumpc.com/article/new...ithout_itanium

On Feb 2, 4:26*am, joel garry <joel-ga... (AT) home (DOT) com> wrote:

I do recall that. But that is not really the issue. We don't daily
create/drop tables. Data on existing tables is not static. We create
tables once, then populate them, and in many cases daily and with more
data than before. As they grow, they'll grab increasingly larger
extents with ASSM. And if they all reside in a single tablespace, it
is a given those extents won't be contiguous: they can't be if there
is more than one growing table.
Now, if one or more of those tables gets a truncate - or lots of
deletes - and we want to shrink it either via reorg or offload/load,
we'll end up with a tablespace that has "holes" of different range of
sizes all over.
The problem is not the holes themselves, that is not an issue. The
problem is that the number of holes of each size may not necessarily
match the requirements for expansion of other tables already large in
that tablespace.
With uniform allocation, the holes are always the same size and all
free space can be used at any time with no restrictions.
It has nothing to do with tuning, it has all to do with not creating a
nightmare of maintenance,*IF* data is volatile.
Although of course if one's job description is to create issues to
resolve later, then auto-allocate is a great idea! :-)

Randolf Geist wrote,on my timestamp of 1/02/2012 7:21 PM:

Nothing I could do or say is right compared to your work, Randolf. Hang in
there, man.