How to determine minimal extent size ?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: How to determine minimal extent size ?

Hybrid View

  1. #1
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317

    How to determine minimal extent size ?

    Hello,

    I have 40 Kilobytes as an extent size in my tablespaces. Now I read the following text on this page.
    Having 1,000 extents for an object by itself does not pose any performance problems, so long as the extents are sized as a multiple of (DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE). The aforementioned formula is relevant for full-table scans or index fast full scans and not for single-block index scans. If the extent sizes adhere to the above formula, it ensures that Oracle will issue the same number of read system calls regardless of whether the object has 1 extent or 1,000 extents. If the extents are not aligned with the aforementioned size, additional read system calls can cause unnecessary overhead on the I/O sub-system. In the bigger scheme of things, assuming worst-case scenario, go ahead and add one additional read system call per extent per heavily hit table/index in your database. If you have many hundreds of objects with 1000s of misaligned extents, it will create overhead on the I/O sub-system.
    In my 8i database:
    db_file_multiblock_read_count = 128
    db_block_size = 8 Kilobytes

    Does this mean that the minimal extent size must be 128 * 8 = 1024KB?

    Help is very appreciated.

    Erik
    Last edited by efrijters; 09-25-2003 at 07:38 AM.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  2. #2
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Maybe I must rephrase the question:

    Is my extent size (40KB) too small? Is it better for I/O performance to make it the size (or multiple) of DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE ??

    Erik
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  3. #3
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    I'm starting to think that my question is irrelevant/too hard?/stupid/badly described.

    Or are all you DBA's out to see the introduction of 10G in Amsterdam?

    Humble grasshopper is hopping home...
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    the reason for the "extent size should be multiple of (block size*db_file_multiblock_read_count)" statement is that when oracle and the o/s combine to read large chunks of data, the number of bytes that will be read in a single operation will be the minimum of ...

    * extent size
    * default block size*db_file_multiblock_read_count
    * o/s maximum i/o size

    So, in your case you have block size*db_file_multiblock_read_count = 1Mb (which maybe misleadingly high -- your o/s might not support a 1Mb iu/o operation). If you have a table of 2Mb you could potentially scan it in 2 i/o operations (provided the o/s will do so, of course). However if that table is divided into extents of 40kb then each i/o operation will be limited to 40kb, and it will take "25.6" (ie. 26) i/o operations to scan the table.

    If you have tables that fit within a single 40kb extent then that's ok -- you'll get your single i/o to scan the entire table, but a full scan of a multi-extent table with extent size that is not an integer multiple of 1Mb (or whatever the maximum allowed by the o/s is) will suffer.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    SlimDave, this explains all! Thanks a million! I'm really glad you took the time to answer my question.

    Now I only have to figure out what the maximum value of bytes the I/O-system and the OS can handle.

    I think the 40KB value was only a calculated guess when the db was installed a few years ago.

    Have a cigar!

    Greetings,

    Erik
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Ah, well you can do that experimentally. See this thread at AskTom.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Thanks again SlimDave!!

    So, if I get this right: If you give "db_file_multiblock_read_count" an ubsurdly high value, it gets the maximum (an therefore optimal) value for the OS?

    When I test this in my db:
    SQL>alter session set db_file_multiblock_read_count = 500;

    Session altered.

    SQL>select value from v$parameter where name = 'db_file_multiblock_read_count';

    VALUE
    ----------------------------
    128

    1 row selected.

    SQL>alter session set events '10046 trace name context forever, level 8';

    Session altered.

    SQL> select /*+ FULL(a) */ count(*) from sys.source$ a;

    COUNT(*)
    ----------
    1381610


    Trace file:
    select /*+ FULL(a) */ count(*) from sys.source$ a
    END OF STMT
    PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=0
    EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
    WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
    WAIT #1: nam='db file sequential read' ela= 0 p1=1 p2=208 p3=1
    WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=931 p3=32
    WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=1731 p3=32
    WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=1803 p3=32
    WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=1883 p3=32
    WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=1979 p3=32
    WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=2043 p3=32
    etc etc

    Extent sizes:
    Code:
      1  select bytes/1024, count(*)
      2      from dba_extents where owner='SYS' and
      3      segment_name='SOURCE$'
      4      group by bytes/1024
      5*     order by 1
    SQL> /
    
    BYTES/1024   COUNT(*)
    ---------- ----------
            16          1
           256        218
    My conclusion/question:
    So Oracle reads in chunks of 32 blocks * 8K (block size) = 256K.
    But the tablespace SYSTEM has an extent size of 64KB.

    Is this good? Isn't it better to have a 256K extent size in this case?

    I want to completely understand this. So that's why I inserted this long example...


    Hope you can help me, SlimDave (or anybody)

    Thanks in advance!!

    Erik
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by efrijters
    But the tablespace SYSTEM has an extent size of 64KB.

    Is this good? Isn't it better to have a 256K extent size in this case?

    Erik
    Its the Segment's Extent Size that matter not TS, as its just that TS's extent size will be used if you dont specify storage param while segment is created in it..

    BTW, dont play with SYSTEM TS.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    I know it's all about the segment size. I create tablespaces with a specific extent size and all objects in that tablespace inherit that extent size. So the extent sizes are equal. That's clear to me.

    I would really like to have an answer to the above questions.

    The example shown was executed because of an example at the "AskTom"-site.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    I am quite not sure about what is the question..

    If this is your Q
    "But the tablespace SYSTEM has an extent size of 64KB.
    Is this good? Isn't it better to have a 256K extent size in this case?"

    Then i did ans, it immaterial as long as you have all segments defined the storage & it depends on those Extent size not TS.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width