DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: How many Extents max per Object

  1. #1
    Join Date
    Feb 2003
    Posts
    22

    How many Extents max per Object

    Hi,

    little question how many extents could a objekt (table or index) have before i have to rebuild it?

    Are 10 Extents to much? What experience did you make?

    Greetz
    Eldrik

  2. #2
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    The following white paper from Oracle should answer your question.

    http://www.alise.lv/Alise/technolog....ILE/defrag.pdf

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by hacketta1
    The following white paper from Oracle should answer your question.

    http://www.alise.lv/Alise/technolog....ILE/defrag.pdf
    old and busted vs new hotness
    I'm stmontgo and I approve of this message

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    It is easy to write "White Papers" about the fragmentations now-a-days. Forget about those papers for a moment.

    I have bben working with UFS, JFS, and RAW from 10 GB database to 1.2 TB on EMC, Sun disk storage, internal disks etc...

    My suggestions are:

    01. Create small, medium, and large tablespaces. Each size may vary, and it depends upon the DB size.

    02. Allocate minimum 6 data files for each tablespace except system and tools.

    03. Keep the number of extents less than 100 for any object. No matter what tablespace type (DMT or LMT) you use.

    04. Create separate tablespace for BIG tables and indexes.

    05. If you use DMT, then set minimum extent length, initial and next sizes all same.

    06. Use RAW device, RAID 1+0. Choose correct stripe size per disk. So far no file system has beaten RAW in our test lab.

    07. If RAW is not possible, then use JFS. If JFS is not possible, then use UFS.

    08. Avoid RAID 5 as much as possible.

    09 Avoid auto extend for data files.

    10 Do not create more than 4002 MB data files.

    Thanks
    Tamil

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Assuming budget is unlimited, your suggestions are great. However, I wonder what your rationalization is for:

    Originally posted by tamilselvan
    09 Avoid auto extend for data files.
    Jeff Hunter

  6. #6
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Tamil, I agree with some of your statements, but not all.

    Originally posted by tamilselvan

    02. Allocate minimum 6 data files for each tablespace except system and tools.
    Why? If I have a 50 Meg tablespace with a bunch of tiny tables - no way am I going to give it six datafiles. What is your reasoning for 6 rather than 5 or 4 for that matter?

    03. Keep the number of extents less than 100 for any object. No matter what tablespace type (DMT or LMT) you use.
    Why? I have had objects with over 500 extents with no problems. I agree with Tom Kyte to move them to a larger extent tablespace, but I usually start looking at that around 500 rather than 1024. Just a personal choice.

    09 Avoid auto extend for data files.
    Again - I disagree. I've got huge filesystems with plenty of room to grow. I don't want a call at 2 in the morning when my tablespace fills up. I set AutoExtend on, and monitor size. When it get's to it's max, I create a new file and turn autoextend off on the big one. This has been discussed here before. It all depends on your system and how much room you have. The trick is to monitor it.

    10 Do not create more than 4002 MB data files.
    Any reasoning to this statement? I use 5 Gig as my threshold.. but it's just a personal preference, I admit. No reason except that I wanted a reasonable size to work with.

    Tha rest I either agree with, or don't have any experience with (RAW).

    Thanks!
    Jodie

  7. #7
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Originally posted by marist89
    Assuming budget is unlimited, your suggestions are great. However, I wonder what your rationalization is for:

    quote:
    --------------------------------------------------------------------------------
    Originally posted by tamilselvan
    09 Avoid auto extend for data files.

    --------------------------------------------------------------------------------
    Ahh - ya beat me to the punch on this one, Jeff!

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by tamilselvan
    10 Do not create more than 4002 MB data files.
    I guess you might want to break this rule if you've 100's of TB's to deal with . . . .

    For various reasons I zip my data files - 4GB is the biggest that pkzip/winzip will deal with (Windoze). (For my modest db, a 2GB max size is convenient.)

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    =========
    Again - I disagree. I've got huge filesystems with plenty of room to grow. I don't want a call at 2 in the morning when my tablespace fills up. I set AutoExtend on, and monitor size. When it get's to it's max, I create a new file and turn autoextend off on the big one. This has been discussed here before. It all depends on your system and how much room you have. The trick is to monitor it.
    ========

    If You set autoextend on, then why do you monitor?

    I never allow oracle to increase the data file size. I precreate all the required data files in the beginning.

    4002 MB size ??

    With 4002 MB size and 1024 db_files, I can create around 4 TB databse. If my DB size is going to be around 1 TB, then I would prefer 2002 MB datafile.

    Why 6 data files for a tablespace ?

    When a tablespace has more than 1 data file, oracle allocates the next extent for an object in the next available data file. It goes on round-robin basis. That way I spread IO evenly across many data files which in turn evenly balancing HBAs (Host Bus Adapters / controllers). Second reason is, parallel dml/ddl works faster when the tablespace has many data files. I prefer to use 6 parallel degree always.

    100 extents or 500 extents ?
    Again, 100 is my personal choice, keeping less work system owned dictionary tables.

    Tamil

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by tamilselvan
    100 extents or 500 extents ?
    Again, 100 is my personal choice, keeping less work system owned dictionary tables.
    How does that apply to LMT's?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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