Re: Segment management auto clause

Richard Foote wrote:
>
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> news:Tu3wa.33518$1s1.489854_at_newsfeeds.bigpond.com...
> >
> >
> > ASSM has significant overheads, and I strongly advise you not to use it
> > unless you happen to be suffering from extreme free list contention (which
> > commonly happens in a RAC environment).
>
> Hi Howard,
>
> Under what basis do you make the above comments ? I would be interested to
> know what testing you've performed to come to this final conclusion.
>
> I've been running quite a few tests where I work, comparing the performance
> of tables with/with out ASSM.
>
> Guess what ?
>
> There is a negligible effect on performance. I've runs tests with between 1
> (very large) and approx. 500,000 extents with FTS performance coming in at
> practically identical between otherwise identical ASSM / non ASSM tables.
> Interestingly, the bitmaps cover more extents as the number of extents grow
> and although there is a little overhead with the additional bitmap blocks,
> the net effect of reading them is somewhat minimal.
>
> Few of these table perform deletes so it isn't much of an issue, but in one
> example where some deletes do occur, the ASSM table was more effective at
> reusing space when compare with it's equivalent that had a default setting
> of 40 for PCTUSED and eventually become noticeably better off. The deletes
> were somewhat random in nature and the average row length was practically
> the same in the test. Would a more appropriate setting of PCTUSED changed
> this scenario, most definitely, but interestingly, most sites I visit has
> PCTUSED set to 40. I wonder why ...
>
> But getting back to my main point, my tests (and I stress they're my
> observations on my data on my database configuration and load at my current
> site) most definitely do NOT suggest "significant overheads" with ASSM.
>
> My recommendation would be not to necessarily accept general warnings but
> test, test and test and see what is most suitable. I could probably manually
> tune an individual segment to outperform an ASSM segment, but the question
> is would the effort (for potentially thousands of segments) return
> sufficient dividends for it to be worthwhile.
>
> That is the ultimate question ...
>
> Cheers
>
> Richard

I've not researched in any great depth, but one thing that does raise alarm bells is:

create table X tablespace ASSM;
insert into X one row;
set autotrace on statistics
select * from X;
'n' consistent gets (1<n<50-60)

I don't like the idea of "random" dispersal of rows in table blocks...maybe I'm just old school :-)

cheers
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"