re: Automatic Segment Space Management

Hi Howard,

I thought I would start a new thread on this as it's something I wouldn't mind others to comment on.

Firstly, I've never suggested ASSM are necessarily brilliant. In fact, I'm actually a bit undecided on them to hold a firm opinion, never mind start any myths.

It's one of those where there are pros and cons, horse for courses, you get the drill.

Other than the issue with FTS having to access the BMBs you've highlighted, there's also the basic question of "wastage" of space as the BMBs use a fair amount of space in relation to the size of the table. Then there's the issue of BMBs "wasting" memory in the buffer cache in that they contain no data as such but need to be accessed regualarly (suggesting they're likely to be cached). So on the side of the prosecution, those are significant issues.

But on the side of the defence, there are a few saving graces. First of all there's the issue of contention and how they very nicely cope with much simultaeous inserts (at least comparable to reasonable tuned freelist groups, freelists). And I know how many DBAs don't tune this very well (present company excluded of course :) so as a default behaviour, it's not bad in this respect.

Then there's the issue of PCTUSED being thrown out the window. Again, I know many DBAs (present company excluded again )who don't touch this (40% is a good enough guess) resulting in much wasted space when blocks don't get placed on freelist after delete activity. This obviously hurts FTS. Then if you have tables with large varying row sizes, many blocks get taken off the freelist prematurely because of the failure of the large insert to fit under PCTFREE. This also can cause much wasted space and poor FTS performance. With ASSM, these are no longer issues per se.

So if you have much concurrent insert, got no idea how or what to set PCTUSED (or you're never heard of PCTUSED), if you have large varying row sizes, then ASSM may not be a bad option. In fact, it may be a better option for a default than 1 freelist and 40% pctused.

Like I said, I'm somewhat undecided. I do think one could manually override and improve the performance of a table, *IF* you know how. I guess it goes back to that training discussion and the direction of Oracle to just let Oracle handle all these nitty gritty details.

It's up to the jury now your honour.

My (myth neutural) thoughts.

Richard

> > Therefore, I would question the worth of developing anything that
> > details stuff at the block level. Note also with 9i, the fullness of
> > blocks statistics can be easily derived with dbms_space.space_usage and
> > automatic segment space management.
>
> That's if you want to use ASSM, of course. Which you would be mad to do,
> unless freelist contention is a big issue in your life. Which practically
> dictates its a RAC thing only.
>
> It's either the nasal hairs growing in my nostrils, or I can smell a new
> Oracle myth in the making: "Use ASSM! All the time! It's brilliant!".
>
> It's not. It's bloody awful for full table scans unless you have freelist
> contention growing as prolifically as my nasal hairs. So there!
>
> Regards
> HJR

Received on Tue Aug 13 2002 - 04:22:15 CDT