Re: Segment management auto clause

"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 Received on Tue May 13 2003 - 07:49:25 CDT