CREATE TABLE in 9.2 (Diffs from 8.1.5)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: CREATE TABLE in 9.2 (Diffs from 8.1.5)

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    CREATE TABLE in 9.2 (Diffs from 8.1.5)

    All our schema scripts were written for 7.3 but we used them in 8.1.5 anyway.

    Now were migrating everything to 9.2.

    The table scripts all have

    INTITIAL
    NEXT
    PCTINCREASE
    MINEXTENTS
    MAXEXTENTS

    set for them. In 9.2 I plan to use auto management of the extents. Do I need to/have to specify this in a 9i CREATE TABLE script? I suppose PCTINCREASE 0 need specifying, and possibly MAXEXTENTS? But INITIAL and NEXT?

    What should the statement contain?

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    best prectice would be to just assign the tables/indexes to tablespaces having an appropriate sent of properties.

    If you have a mature system then you already know what the sizes of different segments are, so LMT's with uniform allocation would appear to be appropriate.

    You don't need to specify any of those parameters in that case.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    So ássign the extents size at the TBS level?
    That would mean I'd strip out the small/static lookups tables into their own TBS with extent size of 100K, and the dynamic large tables into theirs with, say, 1Mb extents?

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by JMac
    So ássign the extents size at the TBS level?
    That would mean I'd strip out the small/static lookups tables into their own TBS with extent size of 100K, and the dynamic large tables into theirs with, say, 1Mb extents?
    yes, exactly.

    You ought to start by knowing what the maximum i/o size is on your o/s. Then make DB_FILE_MULTIBLOCK_READ_COUNT*block_size equal to that. The uniform extent sizes you choose ought to be integer multiples of the maximum i/o size also.

    reasonable choices of extent size are generally 128kb, 4Mb, 128Mb.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  5. #5
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    The OS is Windows NT4. Any experience of that Dave?

    So , let me get this straight: MULTI_BLOCK_READ_COUNT will then read 'units' of blocks mapping to whole extents (or multiples of)?

    But the extents size being integer mutiples of max i/o size? Does that mean there always be more than one i/o to read any extent?

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by JMac
    So , let me get this straight: MULTI_BLOCK_READ_COUNT will then read 'units' of blocks mapping to whole extents (or multiples of)?
    extent size shud me multiple of DBMBRC*Block Size ( which in turn should equal Max I/O which is 256K in most of the cases )...

    Originally posted by JMac
    But the extents size being integer mutiples of max i/o size? Does that mean there always be more than one i/o to read any extent?
    Gotta be..(applies only for FTS/FFS)
    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"

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    So , let me get this straight: MULTI_BLOCK_READ_COUNT will then read 'units' of blocks mapping to whole extents (or multiples of)?

    But the extents size being integer mutiples of max i/o size? Does that mean there always be more than one i/o to read any extent?
    The idea is that you don't want an extent to be, say, 1.5 times the size of a multiblock i/o, because it still takes two i/o operations to read it. If you had one of more tablespaces with uniform extent size equal to the max i/o size then it just takes a single i/o operation to read the entire table.
    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