ASSM vs. non-ASSM

[Mladen Gogala]

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

[ddf]

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

[Gerard H. Pille]

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.

[ddf]

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/01/my-how-youve-grown.html

It might help in your situation.


David Fitzjarrell

[mhoys]

On Jan 25, 9:21 pm, "Gerard H. Pille" <g...@skynet.be> 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.

"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

[Mladen Gogala]

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

[Gerard H. Pille]

mhoys wrote:
> On Jan 25, 9:21 pm, "Gerard H. Pille"<g...@skynet.be> 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.
>
> "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
>

Yes, like 100 times faster, which I would call significant. Do you know what a "soufflé" is,
and how it can fail? That's like our indexes.

[Gerard H. Pille]

Mladen Gogala 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.
>
>

We often regret not being able anymore to decide where something is stored.

[Randolf Geist]

On 26 Jan., 18:06, Mladen Gogala <gogala.REMOVETHISmla...@google.com>
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.

I know that this is all quite confusing, but you can have auto-
allocate with MSSM, or vice-versa uniform extents with ASSM.

Therefore I'm not sure what arguments you're looking for? Are you
specifically thinking about how Oracle manages free space within a
segment (MSSM vs. ASSM) or is this a discussion related to extent
management (auto-allocate vs. uniform)?

Regarding ASSM: Like all "automatic" things it has its quirks and a
surprising number of corner cases where it behaves (vastly)
differently from MSSM.

Nevertheless the route ahead is clear: Many new features will only be
supported/coded with ASSM, so if you think that you want to take
advantage of bigfile tablespaces, shrink space, securefile LOBs etc.
then you can only use them with ASSM.

Furthermore if you want to benefit from the concurrency enhancements
that are built into ASSM and don't want to fiddle manually with
freelists and/or freelist groups of MSSM, then again ASSM seems to be
the right choice.

If both of above is not that important to the specific situation I
would stick to MSSM. Note: For highly concurrent systems (which means
an assumed concurrency of many more than 16 concurrent accesses to the
same area of a segment) a manually tuned MSSM configuration might
perform better than ASSM.

Hope this helps,
Randolf

[Mladen Gogala]

On Thu, 26 Jan 2012 20:02:19 +0100, Gerard H. Pille wrote:

> Yes, like 100 times faster, which I would call significant. Do you
> know what a "soufflé" is,
> and how it can fail? That's like our indexes.

So, your indexes are made with egg whites and chocolate? No wonder they
fail. They must be delicious?


--
http://mgogala.freehostia.com

[Gerard H. Pille]

I must admit I never had a soufflé with chocolate.

But after a week of extreme maltreatment, our indexes are full of air.

[onedbguru]

I like "soufflé".

Yes, but the next time you need "more space" for that index it is
already allocated and will be used. If you need the space more than
you need the performance boost by not having to allocate more data
blocks to said index, go ahead and shrink it or rebuild it.

I really like the idea not not having to worry about where my data is
stored. Use ASM, ASSM, BIGFILE tablespaces and Fewer tablespaces. I
found that with ASSM, I do get used allocated space for indexes, but
shrinking/rebuilding them only last a few days, then it is back almost
exactly the same size prior to the operation. We found that if we
left it alone, it did what it was supposed to do and reuse that
allocated space for that index as necessary. So, we just added more
disks for other objects and left these big indexes alone. Performance
did not change before or after rebuild/shrink operations.

Performance going from filesystem to ASM almost doubled on one
system. But typically I see 10-20% improvement. YMMV. We started
tracking overall usage and stopped worrying about an individual device
filling up. With small-file TS, you still need to add more datafiles
if they become full, but you should be monitoring tablespace usage
anyway. If you move to BIGFILE, that too goes away. For an 8K block
size, a BF datafile can grow up to 32TB.

Adding datafiles to SMALLFILE TS is also much more simple. alter
tablespace foo add datafile '+DATA1' size 1G autoextend enable;

[Mladen Gogala]

On Thu, 26 Jan 2012 12:27:45 -0800, Randolf Geist wrote:

> I know that this is all quite confusing, but you can have auto-
> allocate with MSSM, or vice-versa uniform extents with ASSM.

Randolph, I know that, that's why I mentioned them separately. Thanks for
your insight, though, you've repeated, almost verbatim, my argument in
this situation.



--
http://mgogala.byethost5.com

[Gerard H. Pille]

onedbguru wrote:

>
> Yes, but the next time you need "more space" for that index it is
> already allocated and will be used.

This is false. The indexes keep growing for the same amount of data and queries using these
indexes become too slow.

[mhoys]

I would like to answer with "it depends" :-) Btw, I tried to develop a
test case to show the performance difference between "normal" and
"soufflé" B-tree indices, but I haven't been successful so far. This
is on 10.2.0.5, ASSM tablespace with automatic extent allocation...
What are the properties of your system, Gerard?

Matthias Hoys

[Gerard H. Pille]

I assure you, it doesn't depend. We are on 10.2.0.5 too, hoping this recent release would solve it.

We have some twenty sessions inserting and updating, a couple of others deleting. But faster
than I can type in any way.

[Noons]

Been running ASSM in our patched up 10.2.0.3 DW without any issues - so far.
Going 11.2.0.3 in a matter of months and will definitely keep ASSM.
Spurious corruptions were all patched up in 10r2. So were most of the space
problems, and the remaining I've been able to keep under control with scheduled
in-place reorgs.
I can't talk about the stability of ASSM in 11gr2, but if it is like in my
current 10gr2 setup, I'll be happy.

[joel garry]

Also see http://jonathanlewis.wordpress.com/category/oracle/infrastructure/assm/
for some more ideas and background. Having a bunch of uncommitted
inserts seems very interesting, it spotlights the problems with
concurrency and tracking blocks with bitmaps.

jg
--
@home.com is bogus.
http://www.newsfactor.com/news/SAP-Sets-Database-Sights-on-Oracle/story.xhtml?story_id=1220020SU1PU&full_skip=1

[Noons]

Oh, I should have added that I use uniform allocation in all my
tablespaces, rather than automatic allocation. As such, I never get
any of the conditions described in Jonathan's posts on ASSM.

[Randolf Geist]

On Jan 29, 4:28 pm, Noons <wizofo...@gmail.com> wrote:
> Oh, I should have added that I use uniform allocation in all my
> tablespaces, rather than automatic allocation. As such, I never get
> any of the conditions described in Jonathan's posts on ASSM.

I think this is a misbelieve - most of the issues described are
regarding the space management within an extent/block and hence apply
to either extent management method, no matter if uniform or auto-
allocate. Which means these can easily be reproduced using uniform
allocation, in fact Jonathan most of the time uses uniform 1MB
allocation in his experiments.

Hope this helps,
Randolf

[Noons]

Actually, he specifically refers in the posts mentined here to it
being partly a result of auto allocation. Which quite frankly is a
bad idea at best of times as it leads to fragmentation of free space
if the tablespace is volatile.

I have no issues whatsoever with large numbers of uniform extents in a
ASSM tablespace, and I do run reasonably large databases with TBs of
data movement per day.

IME, ASSM is remarkable resilient if uniform allocation is used and
care is taken to isolate and contain table volatility loads by grade/
volume. Which is a much easier proposition than fussing around with
buffer busy waits and their remedies in non-ASSM storage, with the
incumbent "reorgs".

The above of course assumes a patched up system - not a vanilla
install.

[Mladen Gogala]

On Mon, 30 Jan 2012 18:37:04 -0800, Noons wrote:

> Actually, he specifically refers in the posts mentined here to it being
> partly a result of auto allocation. Which quite frankly is a bad idea
> at best of times as it leads to fragmentation of free space if the
> tablespace is volatile.

Auto allocation is a bad idea? I don't see why? The extent sizes are
standardized on "power of 2" sizes, so whenever an object is dropped, the
relinquished extents will be usable by other objects. That seems like a
rather sound argument but I maybe missing something. Basically, it's much
easier to lump everything related to a single application in one
tablespace with auto allocation and ASSM then to carefully store tables
and indexes into their own tablespaces, based on projected object size.



--
http://mgogala.byethost5.com

[Noons]

On Jan 31, 11:59 pm, Mladen Gogala <gogala.mla...@gmail.com> wrote:


> Auto allocation is a bad idea? I don't see why? The extent sizes are

For precisely the same reasons auto increment in old dictionary
managed tablespaces was a bad idea. Great on paper, totally
impractical in use.

> standardized on "power of 2" sizes, so whenever an object is dropped, the
> relinquished extents will be usable by other objects.

Only if the other object's extent size request matches the sizes made
available by the dropped object, in sub-multiples. That is not always
the case.

With uniform allocation there is *ALWAYS* a match. Without fail, no
matter what. I'd much rather have that - a no problem - than a
potential problem.

As well, if you want to do a reorg in place where you compact a
heavily fragmented table into the free space available at start of
tablespace by doing a alter table move with no tablespace target, it
simply won't work with ASSM auto in 99% of the cases. While it is a
breeze and works everytime, without fail, with uniform allocation.

> rather sound argument but I maybe missing something. Basically, it's much
> easier to lump everything related to a single application in one
> tablespace with auto allocation and ASSM then to carefully store tables
> and indexes into their own tablespaces, based on projected object size.

Oh, spreading things into their own tablespaces is not just to manage
size. It's also to manage type of usage. And the corollaries:
volatility and frequency of reference.

And of course, the uniform size can be adjusted to match each type of
use and size of table. I tend to use 1MB as a catch all (except for
Peoplesoft and its 25000 tables!!!), then 10MB or even 100MB for much
larger tables/indexes. Each type in its own tablespace, by volatility
of usage. And particularly where LOBS are concerned I much prefer
this type of management than letting ASSM auto manage (badly) all size
multiples/sub-multiples.

Essentially and from what I've seen, ASSM with auto allocation
requires careful monitoring and frequent maintenance. With uniform
allocation I set it once and I don't have to worry about it ever
again. Other than overall size management which I have to always do,
anyway.

Then again with 11gr2 and sparse initial allocation, 1MB catch-all may
not be all that bad for Peoplesoft as most objects won't use any space
until something is actually stored there. But I remain to see how
that is possible to do when the Peoplesoft install is totally agnostic
to 11gr2 and the sparse allocation is not valid for pre-existing
tables and won't work on exp/imp - which makes it essentially unusable
with PS...

Ah well, things may improve on 12cr2... ;-)

[Randolf Geist]

On 31 Jan., 03:37, Noons <wizofo...@gmail.com> wrote:
> Actually, he specifically refers in the posts mentined here to it
> being partly a result of auto allocation

Yawn... With apologies to Oscar Wilde: People who think they are
always right tend to irritate those of us who are.

[joel garry]

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/f?p=100:11:3813628452718745::::P11_QUESTION_ID:5549302357655#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/news/oracle_hewlett-packard_strategically_screwed_without_itanium

[Noons]

On Feb 2, 4:26 am, joel garry <joel-ga...@home.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.

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! :-)

[Noons]

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

> Yawn... With apologies to Oscar Wilde: People who think they are
> always right tend to irritate those of us who are.

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

[John Hurley]

Nuno:

# Oh, I should have added that I use uniform allocation in all my

tablespaces, rather than automatic allocation. As such, I never get
any of the conditions described in Jonathan's posts on ASSM.

...

Like you already mentioned I am a big fan of the uniform size of 1
mb ... depending on the database design or lack thereof many OLTP
systems work just peachy with almost everything in 1 mb allocations.

At times ( as you mentioned already ) you may find a few things
outside ( and maybe way outside ) the range where you want to do
something above 1 mb.