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

Thread: How to determine minimal extent size ?

  1. #11
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Sorry, but that wasn't the question.

    In the SQL-example I try to determine the optimal extent size for reading data when looking at the OS.

    I'm trying to proof that my theory is correct:
    - The segments have an extent size of 64K;
    - db_file_multiblock_read_count=128; block size 8KB
    - when reading data, the trace file says that Oracle reads in chunks of 32 blocks, so 32 blocks * 8K = 256KB. So, probably, this is the OS's limit.

    To rephrase my question:
    -If Oracle can optimally read 256KB at a time, isn't it better to have extent sizes that are 256KB? or a multiple of 256KB? So that the extent size corresponds with the optimal OS-read-size?

    I hope this clarifies it.

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

  2. #12
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    I think the answer is YES.

    But extents of 40KB are good, because there are also a lot of tables that only have 1 or 2 rows. So, rebuilding those tables to extent size 256KB would only waste hard disk space.

    Now I understand the TS_SMALL, TS_MEDIUM, TS_LARGE story better.

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

  3. #13
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by efrijters
    I think the answer is YES.

    But extents of 40KB are good, because there are also a lot of tables that only have 1 or 2 rows. So, rebuilding those tables to extent size 256KB would only waste hard disk space.

    Now I understand the TS_SMALL, TS_MEDIUM, TS_LARGE story better.

    Thanks everybody!
    Yes, this seems fair, but i should add that this only affects large i/o requests, like full table or fast full index scans. Accessing tables by rowid through the index, or index operations other than FF scan, get no benefit from this extent sizing issue.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  4. #14
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Thanks SlimDave! Have a good weekend!
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

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