This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Mar 27, 2013 1:18 AM by Radek RSS

AUTOALLOCATE vs UNIFORM SIZE

Radek Newbie
Currently Being Moderated
Hi,

Let's take 11.2.0.3 as an example.

If I had, let's say, a table 1.005GB in size, why should I bother to use AUTOALLOCATE for its tablespace and waste disk space (only 5B used in last 64MB extent), if I could instead use 1005 extents x 1MB as UNIFORM SIZE.


Thus:
1. I don't waste disk space for the UNIFORM SIZE option.
2. Extents number makes no difference from performace view (ok, I don't drop and recreate this table), as extent is just a logical structure. I mean extent number don't matter.
3. I don't use parallel direct path inserts (no extent trimming), just the conventional insert.
4. I still take advantage over ...,64kb, 128kb... multi-block read

Shoud I apply this to every tablespace? Makes sense?
Or should I not bother and leave it all to oracle? (AUTOALLOCATE) ?
  • 1. Re: AUTOALLOCATE vs UNIFORM SIZE
    sb92075 Guru
    Currently Being Moderated
    Radek wrote:
    Hi,

    Let's take 11.2.0.3 as an example.

    If I had, let's say, a table 1.005GB in size, why should I bother to use AUTOALLOCATE for its tablespace and waste disk space (only 5B used in last 64MB extent), if I could instead use 1005 extents x 1MB as UNIFORM SIZE.


    Thus:
    1. I don't waste disk space for the UNIFORM SIZE option.
    2. Extents number makes no difference from performace view (ok, I don't drop and recreate this table), as extent is just a logical structure. I mean extent number don't matter.
    3. I don't use parallel direct path inserts (no extent trimming), just the conventional insert.
    4. I still take advantage over ...,64kb, 128kb... multi-block read

    Shoud I apply this to every tablespace? Makes sense?
    Or should I not bother and leave it all to oracle? (AUTOALLOCATE) ?
    you would "waste" even less space if you made extent size the same as Oracle Block Size.
    At what total size do you hit maximum number of extents?
  • 2. Re: AUTOALLOCATE vs UNIFORM SIZE
    Radek Newbie
    Currently Being Moderated
    sb92075 wrote:
    you would "waste" even less space if you made extent size the same as Oracle Block Size.
    Exacly. Why "waste" not waste ? Please read my comment carefully:
    - I still take advantage over ...,64kb, 128kb... multi-block read. Would I be able to use this feature if went down to the block size. No, so please don't write silly comments.
    At what total size do you hit maximum number of extents?
    I would appreciate valid response instead of sarcastic comment. You have nothing to say, please dont reply.

    Thank you.
  • 3. Re: AUTOALLOCATE vs UNIFORM SIZE
    Kh$n Journeyer
    Currently Being Moderated
    For performance reasons most loads leverage direct path operations where the load process directly formats and writes Oracle blocks to disk instead of going through the buffer cache. This means that the loading process allocates extents and fills them with data during the load. During parallel loads, each loader process will allocate it own extent and no two processes work on the same extent. When loading data into a table with UNIFORM extents each loader process will allocate its own Uniform extent and begin loading the data, if the extents are not fully populated the table is left with a lot of partially filled extents, effectively creating ‘HOLES’ in the table. Not only is this space wastage but it also impacts query performance as subsequent queries that scan the table have to scan all of the extents even if they are not fully filled.

    What is different with AUTOALLOCATE? AUTOALLOCATE will dynamically adjust the size of an extent and trim the extent after the load in case it is not fully loaded (Extent Trimming)

    please have a look into below links.

    https://blogs.oracle.com/datawarehousing/entry/parallel_load_uniform_or_autoallocate
    http://www.oracle.com/technetwork/issue-archive/2007/07-may/o37asktom-101781.html

    Edited by: Asad99 on Mar 25, 2013 11:50 PM

    Edited by: Asad99 on Mar 25, 2013 11:51 PM
  • 4. Re: AUTOALLOCATE vs UNIFORM SIZE
    Radek Newbie
    Currently Being Moderated
    Asad99,

    Thank you for your response. I exluded this situation in my original post:
    Radek wrote:
    3. I don't use parallel direct path inserts (no extent trimming), just the conventional insert.
    I repeat my question then: Is there any reason to use AUTOALLOCATE then if we exclude direct path parallel processing?
  • 5. Re: AUTOALLOCATE vs UNIFORM SIZE
    Kh$n Journeyer
    Currently Being Moderated
    How does Oracles AUTOALLOCATE extent size management work?

    1. Allocates extents in the following sizes only:
    64K, 1MB, 8MB, 64MB or 256MB.
    2. A segment gets allocated an initial extent of 64K. As the segment grows in size the sizes of the subsequent extents also increases.
    3. The extent allocations make use of the following basic formula, the first 16 extents are 16K in size, thenext 64 extents are 1M in size, the next 120 extents are 8M in size, and then they increase to 64M in size and so on.
    4. The above sizes are all exact multiples of each other and are also perfectly devisable by 8K, 16K or 32K database block sizes.
    5. Although there are multiple extent sizes, fragmentation won’t occur as other segments will always be able to find an exact free space size to fit into, or even if free space pockets are split during a space allocation, the remaining size will still always be a multiple of the possible extent sizes

    Benefits of AUTOALLOCATE

    1. Extents are always a multiple of the database block size.
    2. All extent sizes are multiples of each other.
    3. Prevents space fragmentation.
    4. Accommodates small segments through to extremely large segments with the same ease.
    5. No requirement to group segments into tablespaces according to size.
    6. Does not waste space due to an inappropriate size.
    7. Won’t cause segments to extent into thousands of extents.
    8. It is the default for tablespace creation.
    9. Requires less work from DBA’s when creating tablespaces.
    10. Eliminates sizing calculations and DBA sizing miscalculations.
    11. Performance benefit with Full Table Scans as extents are fewer and larger and are multiples of the multiblock read count, reducing additional multiblock reads.


    Benefits of UNIFORM extent sizes

    1. Prevents fragmentation.
    Disadvantages of UNIFORM extent sizes.
    1. Difficult to calculate appropriate UNIFORM extent sizes that cater for small through to very large segments in databases.
    2. Need to group segments together into tablespaces according to size.
    3. Miscalculated UNIFORM extent sizes either results in space wastage or forces segments to occupy thousands of extents.
    4. Segments frequently out grow their UNIFORM extent size.
    5. Performance impact with Full Table Scans if extents are not a multiple of the multiblock read count, necessitating additional multiblock reads in order to retrieve the last remaining blocks of the extent.
    6. Tablespace QUOTAS are still dictionary managed and therefore forcing a segment into thousands of extents can still have an impact when reorganizing or dropping it.


    Conclusion
    1. The advantages of AUTOALLOCATE include and outweigh the advantages of UNIFORM extent size allocations.
    2. Miscalculated UNIFORM extent sizes pose a risk to the database.
    3. If segments are to be grouped into tablespaces according to size, then it is strongly suggested not to use multiple UNIFORM extent sizes to differentiate the segments, but rather use multiple BLOCK sizes to differentiate the segments i.e.
    3.1 8K tablespace block size for small and medium segments
    3.2 16K tablespace block size for large segments
    3.3 32K tablespace block size for very large segments.
    4. Multiple BLOCK sizes will gave huge performance benefits if used correctly in conjunction with appropriate segment sizing.


    further details

    http://www.rdbconsulting.com/files/The%20Extent%20of%20Extents.pdf

    Edited by: Asad99 on Mar 26, 2013 12:47 AM

    Edited by: Asad99 on Mar 27, 2013 4:43 AM
  • 6. Re: AUTOALLOCATE vs UNIFORM SIZE
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    It depends on the mix of tables.

    For example, if you install something like EBusiness Suite or Peoplesoft or any such ERP package, you'll find literally thousands of tables that are empty or have only one or a few rows. 1MB each for few thousand tables increases the size of the initial database. 11gR2's deferred_segment_creation was provided to handle such situations. Without d_s_s, the workaround was to use AUTOALLOCATE so that the max wasted space was 64KB on each of such tables.


    To take another example, if your applications consists of a mix of very small and medium and large tables, you could use AUTOALLOCATE as a "uniform" rule for all the tables. Alternatively, you'd have to group these tables by size and create separate tablespaces with different UNIFORM sizes. AUTOALLOCATE simplifies things.



    Hemant K Chitale

    Edited by: Hemant K Chitale on Mar 26, 2013 3:59 PM
  • 7. Re: AUTOALLOCATE vs UNIFORM SIZE
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Asad99 wrote:

    What is different with AUTOALLOCATE? AUTOALLOCATE will dynamically adjust the size of an extent and trim the extent after the load in case it is not fully loaded (Extent Trimming)

    please have a look into below links.

    https://blogs.oracle.com/datawarehousing/entry/parallel_load_uniform_or_autoallocate
    http://www.oracle.com/technetwork/issue-archive/2007/07-may/o37asktom-101781.html
    I think the most significant point these articles actually make is that you should think carefully about the relationship between important objects and their tablespaces. If you check the arithmetic, the newer article is loading 1GB of data into an object declared with 100MB extents, using a parallel degree of (probably) around 100.

    1GB data at parallel 100 is 10MB per slave - aiming at extents if 100MB, which is why you get about 9 times as much space allocated as used. But (a) why would you go parallel 100 on just 1GB of data, and (b) why would you declared extents of 100MB for only 1GB of data ?

    Looking at the autoallocate figures - it would be interesting to see if Oracle handled the "small extents" problem in this example, but if it didn't then it would have created 16 extents of 64KB and 9 extents of 1MB for each slave - totalling 2,500 extents for 1GB - and when running a parallel query against that table it would be restricted to 64KB reads for each of the small extents. (See http://jonathanlewis.wordpress.com/2007/05/29/autoallocate-and-px/ ) It's possible, though, that the fix referenced in the update to my article would have applied leaving each slave with about 10 extents of 1MB each - and if they trimmed them then you'd have holes in the tablespace which would have to be filled by extents of 64KB by something else later on - so maybe a uniform extent of 1MB for the target tablespace would have been a better bet for this object.

    Regards
    Jonathan Lewis
  • 8. Re: AUTOALLOCATE vs UNIFORM SIZE
    Radek Newbie
    Currently Being Moderated
    Dear Mr Chitale,

    Thank you.
    I partly agree:
    Hemant K Chitale wrote:
    It depends on the mix of tables.

    For example, if you install something like EBusiness Suite or Peoplesoft or any such ERP package, you'll find literally thousands of tables that are empty or have only one or a few rows. 1MB each for few thousand tables increases the size of the initial database. 11gR2's deferred_segment_creation was provided to handle such situations. Without d_s_s, the workaround was to use AUTOALLOCATE so that the max wasted space was 64KB on each of such tables.
    Unfortunately for first 16 extents only, then extent size increases to 1MB for next 63 extents, then 8MB, and so on, so on.
    So, if I create few extensts of 64kB or one initial of 1MB, makes not such a big difference.


    >
    To take another example, if your applications consists of a mix of very small and medium and large tables, you could use AUTOALLOCATE as a "uniform" rule for all the tables. Alternatively, you'd have to group these tables by size and create separate tablespaces with different UNIFORM sizes. AUTOALLOCATE simplifies things. So why should I lose space ? Are there any other differences that good be used as a strong argument for AUTOALLOCATE option ?
    Thank you

    I understand this is a good practice. Where's the benefit? I understand , if I do conventional insert with AUTOALLOCATE what I lose is last extent not fully used plus maybe some fragmentation in previous extents (for long table rows that do not fit in the datablock for example). If data doesn't fit in one data block for AUTOALLOCATED extent it won't fir in UNIFORM SIZE extent either (considering, both TBS are the same block size).

    >
    Hemant K Chitale

    Edited by: Hemant K Chitale on Mar 26, 2013 3:59 PM
  • 9. Re: AUTOALLOCATE vs UNIFORM SIZE
    Kh$n Journeyer
    Currently Being Moderated
    great clarification. thanks
  • 10. Re: AUTOALLOCATE vs UNIFORM SIZE
    Radek Newbie
    Currently Being Moderated
    Thank you Mr Lewis for taking your time in replying to my post.

    Yes, I know Mr Kyte's example very well. This is why I excluded parallel load and extent trimming from my original post.
    Let's focus on Standard Edition and conventional load by one process.
  • 11. Re: AUTOALLOCATE vs UNIFORM SIZE
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Unfortunately for first 16 extents only, then extent size increases to 1MB for next 63 extents, then 8MB, and so on, so on.
    So, if I create few extensts of 64kB or one initial of 1MB, makes not such a big difference.
    Each empty or 1 row t able would have 1MB in UNIFORM. Multiply this by a few thousand tables and your initial database (with no user-data) is large. AUTOALLOCATE prevented this.

    So why should I lose space ? Are there any other differences that good be used as a strong argument for AUTOALLOCATE option ?
    I didn't use these words. Don't attribute them to me.
    If data doesn't fit in one data block for AUTOALLOCATED extent it won't fir in UNIFORM SIZE extent either (considering, both TBS are the same block size).
    Allocation is never in terms of data blocks but in terms of extents. In a small table the max wasted space is 64KB in AUTOALLOCATE but 1MB in UNIFORM 1MB.

    Hemant K Chitale
  • 12. Re: AUTOALLOCATE vs UNIFORM SIZE
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Radek wrote:
    Thank you Mr Lewis for taking your time in replying to my post.

    Yes, I know Mr Kyte's example very well. This is why I excluded parallel load and extent trimming from my original post.
    Let's focus on Standard Edition and conventional load by one process.
    Third time lucky - this is my third attempt to answer the question, the last two times IE simply closed the window on me for no apparent reason.

    AUTOALLOCATE is a good default choice if you know virtually nothing about your data. Statistically (under average conditions) it rarely causes problems.

    As soon as you start seeing special cases, though, you can always think about using uniform extents. It's simply a matter of how much time you have to observe and think, and how much benefit you might gain. If you've got several slowly growing objects of about 1Gb then I think it would be reasonable to set up a tablespace with uniform 1MB (or maybe 8MB) for them as this could avoid (for example) the chance of several objects adding 64MB at the same time and hitting a file size limit. On the other hand, if you've only got one object, is it worth the hassle of having a whole tablespace to cater for one bit of overallocation ? (Bear in mind that you'll probably overallocate the tablespace anyway, and you've probably got pctfree set to the default 10 anyway, so the 64MB "wastage" isn't particularly significant compared to the 10% that might be empty within the object; and presumably the object will carry on growing.)



    Regards
    Jonathan Lewis
  • 13. Re: AUTOALLOCATE vs UNIFORM SIZE
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Asad99 wrote:
    How does Oracles AUTOALLOCATE extent size management work?

    1. Allocates extents in the following sizes only:
    When quoting wholesale from an article it is polite to supply the URL: http://www.rdbconsulting.com/files/The%20Extent%20of%20Extents.pdf

    Reading the article I did wonder if the author was getting commission for selling autoallocate extents ;)
    3. If segments are to be grouped into tablespaces according to size, then it is strongly suggested not to use multiple UNIFORM extent sizes to differentiate the segments, but rather use multiple BLOCK sizes to differentiate the segments i.e.
    3.1 8K tablespace block size for small and medium segments
    3.2 16K tablespace block size for large segments
    3.3 32K tablespace block size for very large segments.
    Completely idiotic idea.
    4. Multiple BLOCK sizes will gave huge performance benefits if used correctly in conjunction with appropriate segment sizing.
    Pointless to make this comment without explaining what "used correctly" means. Simply picking a blocksize based on the size of the segment has nothing to do with correctness, and (on a statistical basis) probably won't give ANY performance benefits, let alone HUGE ones.

    Regards
    Jonathan Lewis
  • 14. Re: AUTOALLOCATE vs UNIFORM SIZE
    Radek Newbie
    Currently Being Moderated
    Hemant K Chitale wrote:
    Unfortunately for first 16 extents only, then extent size increases to 1MB for next 63 extents, then 8MB, and so on, so on.
    So, if I create few extensts of 64kB or one initial of 1MB, makes not such a big difference.
    Each empty or 1 row t able would have 1MB in UNIFORM. Multiply this by a few thousand tables and your initial database (with no user-data) is large. AUTOALLOCATE prevented this.
    Very strong argument, but for first 16 tables. All next tables will start from 1MB, then 8MB, then 64MB initial extent anyway, correct?
1 2 Previous Next

Legend

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