This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Nov 15, 2012 12:04 PM by user12229699 Go to original post RSS
  • 15. Re: Locally Managed Tablespaces vrs ASSM
    Jimbo Explorer
    Currently Being Moderated
    Interesting, I was told on an Oracle course that pct_free is still used even with ASSM in order to determine how full the data block is. It is pct_increase that is no longer used ( by virtue of Extent Management under Locally Managed Tablespaces - a prerequisite for ASSM )
  • 16. Re: Locally Managed Tablespaces vrs ASSM
    RNi Explorer
    Currently Being Moderated
    your pct_free isn't Mark's pct_used and pct_used isn't used with ASSM

    Regards
    RN
  • 17. Re: Locally Managed Tablespaces vrs ASSM
    Jimbo Explorer
    Currently Being Moderated
    You quite right - not paying attention to my pct's ! PCT_USED is not used in ASSM, though I think PCT_FREE may still be used
  • 18. Re: Locally Managed Tablespaces vrs ASSM
    JohnWatson Guru
    Currently Being Moderated
    I cannot with the suggestion that ASSM is "slower for high-volume concurrent insertion". Completely the reverse. The algorithm for searching the bitmaps (which is based on hashing each session's SID) means that concurrent searches for suitable blocks will tend to search different parts of the bitmap, which will point to different blocks of the segment. So no buffer busy waits on the bitmaps or the data blocks. The old freelist mechanism is awful in comparison, with sessions queueing up for access to free lists and the data blocks at the high water mark. In a RAC, it is even better because the instance number is included in the algorithm, so you tend to get an affinity of parts of the bitmaps to each instance.
    Even with release 9.1 when there were bugs in the bitmap maintenance, it was better than freelists. That's my experience, anyway.
  • 19. Re: Locally Managed Tablespaces vrs ASSM
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    JohnWatson wrote:
    I cannot agree with the suggestion that ASSM is "slower for high-volume concurrent insertion". Completely the reverse. The algorithm for searching the bitmaps (which is based on hashing each session's SID) means that concurrent searches for suitable blocks will tend to search different parts of the bitmap, which will point to different blocks of the segment. So no buffer busy waits on the bitmaps or the data blocks. The old freelist mechanism is awful in comparison, with sessions queueing up for access to free lists and the data blocks at the high water mark. In a RAC, it is even better because the instance number is included in the algorithm, so you tend to get an affinity of parts of the bitmaps to each instance.
    John,

    Historically ASSM was similar in behaviour to (freelists 16) because it would format 16 blocks at a time. As a consequence of this, people who had set their freelists to values like 50 because they had a very large number of concurrent sessions inserting single (or small numbers of) rows found that switching to ASSM would introduce contention that they weren't expecting. This was a fairly rare occurrence, though, but some websites reported it as if it were a major problem for everyone. (In fact, it's possible that Oracle now formats more blocks when using larger extent sizes - but I haven't looked closely at this feature for some time.)

    Your're generally right about the RAC benefits, though, although people who understood what Oracle was doing would create multiple freelist groups - which had the same effect as associating L1 bitmaps with instances. I believe that one of key reasons for introduced ASSM was to make it unnecessary for DBAs to have to work out the best value for freelist groups (which couldn't be changed without rebuilding the segment.)

    Osama was right about the tablescan performance, though - at its most efficient one bitmap block covers 256 data blocks, and more commonly it's 1 to 128, so a tablescan has to scan between 0.5% and 1% extra blocks - and then has to deal with odd batches of (probably 16 at a time) between the low high water mark and high high water mark. On the other hand, if you're doing a tablescan that's big enough to notice this difference you've probably got a much more important performance problem to address anyway.


    Regards
    Jonathan Lewis
  • 20. Re: Locally Managed Tablespaces vrs ASSM
    JohnWatson Guru
    Currently Being Moderated
    Thank you for the clarification. A day when I learn something new is always a good day.
    :)
  • 21. Re: Locally Managed Tablespaces vrs ASSM
    user12229699 Newbie
    Currently Being Moderated
    Colleagues, please can someone explain to me, speaking of database blocks, if i create a tablespace with EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO, but when i create some table i use to set freelist, pctfree, pctused...and now the question what handling block predominate here?, tablespace level or table level?
    Another question, is this optimal? I mean to have a tablespace with UNIFORM EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO, and tables whitin it, such as type LOG and HISTORY with handling blocks based on pctfree, pctused?
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points