DBAsupport.com Forums - Powered by vBulletin
Page 1 of 5 123 ... LastLast
Results 1 to 10 of 49

Thread: Dictionary managed objects

  1. #1
    Join Date
    Dec 2002
    Posts
    28

    Dictionary managed objects

    1) whats the recommended formulae to calculate which tablespace the relevant table or index should be created in for new tables which dont contain data?
    2) for existing tables and indexes I thought I will look at current no of extents and size and order accordingly.


    Three tablespaces will exist for creating tables and three will exist for indexes as per below

    initial 128K next 1024K PCT Inc 0
    initial 512K next 4096K PCT Inc 0
    initial 5M next 10M PCT Inc 0

    PCTFREE will be 10

    NOTE Dictionary managed tablespaces will have to be used not LMT.

    Please help

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    set initial=next or you WILL get fragmentation at a tablespace level

  3. #3
    Join Date
    Dec 2002
    Posts
    28

    how to order the size

    whats the best way to calculate which tablespace the existing and new table or index's should go into if I use the sizes as per above.

    I can look at dbe_segments for current extents and move the greater number of extents into the larger tablespaces.


    any other ideas?

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: Dictionary managed objects

    Originally posted by dsd7038
    Three tablespaces will exist for creating tables and three will exist for indexes as per below

    initial 128K next 1024K PCT Inc 0
    initial 512K next 4096K PCT Inc 0
    initial 5M next 10M PCT Inc 0
    How in the world have you come to those silly numbers for INITIAl and NEXT parameters for your tablespace?

    Think about what davey23uk has advised you, then think again what storage parameters will you set for your tablespaces and only then start thinking about which segment should go into which tablespace.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Why are you splitting tables and indexes into different tablespaces? There's no performance advantage, you know -- that's an urban legend.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #6
    Join Date
    Dec 2002
    Posts
    28
    Sizes were obtained from http://www.bytelife.com/storage.htm. The article appeared to quite good. See for yourself.

    I am dividing Tables and Indexes as customer (DBA's)requires this. I have also read various documents which state that by separating tablespaces for table an indexes allows for easier maintenance. Not sure how right or wrong this is.

    As we produce on the shelf packages any sizes I recommend will apply to small, maedium and large databases which makes my task more difficult as different customers have different sizes per table and index. I require a general calculation.

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Sizes were obtained from http://www.bytelife.com/storage.htm. The article appeared to quite good. See for yourself.
    No, that's a quite bad article. The advice is terrible

    Here's some new rules for you with DMT's.

    i) PCTINCREASE=0. Always.
    ii) INITIAL=NEXT. Always.
    iii) For each TS with increasing INITIAL size, make the sizes increase by a factor equal to the DB_FILE_MULTIBLOCK_READ_COUNT -- typically, 32.

    I am dividing Tables and Indexes as customer (DBA's)requires this. I have also read various documents which state that by separating tablespaces for table an indexes allows for easier maintenance. Not sure how right or wrong this is.
    Your customer's DBA's are working on the basis of myth and legend. There is no benefit in performance or manageability in separating tables and indexes.

    As we produce on the shelf packages any sizes I recommend will apply to small, maedium and large databases which makes my task more difficult as different customers have different sizes per table and index. I require a general calculation.
    You could choose your smallest tablespace's initial size based on some database size criteria, but still keep the factor of DMRC between the different TS initial sizes.

    eg.
    No. of customers < 10000, small TS initial size = 32kb
    No. of customers > 10000 & < 50000, small TS initial size = 64kb
    No. of customers > 50000, small TS initial size = 128kb
    Last edited by slimdave; 08-14-2003 at 12:28 PM.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by dsd7038
    Sizes were obtained from http://www.bytelife.com/storage.htm. The article appeared to quite good. See for yourself.
    The article is more or less a crap. If you realy want to read a *good* article about how to size segments in DMTs, read the article "HOW TO STOP DEFRAGMENTING AND START LIVING: THE DEFINITIVE
    WORD ON FRAGMENTATION" http://technet.oracle.com/deploy/ava...pdf/defrag.pdf
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by slimdave
    Why are you splitting tables and indexes into different tablespaces? There's no performance advantage, you know -- that's an urban legend.
    I've thought on and off for a long while about this; tho' I don't expect the advantages to be enormous, how about the following?

    1) indexes are (much) smaller than tables, so if you use uniform space allocation they perhaps should go in different TS's.

    2) this is one way to spread load across disks (hardware striping is better but we can't all do that).

    3) (actually an example of #2) Reading a row via index might require (say) 2 I/O's on the index and 1 on the table - if there is a FTS running at the same time, you win if they are on different disks.

    I'm sure someone will enjoy shooting that down!

  10. #10
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by DaPi
    I've thought on and off for a long while about this; tho' I don't expect the advantages to be enormous, how about the following?

    1) indexes are (much) smaller than tables, so if you use uniform space allocation they perhaps should go in different TS's.

    2) this is one way to spread load across disks (hardware striping is better but we can't all do that).

    3) (actually an example of #2) Reading a row via index might require (say) 2 I/O's on the index and 1 on the table - if there is a FTS running at the same time, you win if they are on different disks.

    I'm sure someone will enjoy shooting that down!
    Me too, i have been thinking about it too...

    Agreed that the reads and writes are sequential, during select it will go to index first and then to table, and during inserts, first in table then in index. So seperating them on tablespaces on same disk won't do any good.

    But if there are simultanious inserts and selects from many users then i think having indexes and tables on seperate tablespaces on seperate disks will get the benefit.

    I have read that thread on google where TK, JL and all great ones are discussing this same issue but i still doubt it as there had been no practical explanation or any example to show who was right...

    And morever i feel that by keeping indexes and tables on seperate tablespaces, i can manage them better.

    One example that comes to my mind.

    Say i have a very highly volatile table (lots of transaction going on all the time) having millions of rows in a tablespace. Now if i create an index on this same tablespace, won't this add up to the i/o being carried out by other online users.

    Now if i were to create this index on another tablespace on seperate disk totally, at least it would reduce the inserts (but not the reads) on the same disk as the inserts would go to seperate disks altogether during the index creation...

    Someone enlighten me...
    Last edited by adewri; 08-14-2003 at 04:19 AM.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

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