Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Atlanta
    Posts
    6

    Question Unanswered: Index size and use of space

    This may be a newbie question to most, but here goes:

    THE ENVIRONMENT
    Running 8.1.7
    Table contains audit trail type info
    Quantity of daily inserts is High
    Table is in one tablespace

    There are 4 indexes
    All indexes are in 1 tablespace (different from data tablespace)

    Maintenance on these data is as follows:
    When there is one next_extent left in either the Data or Index
    Tablespace, delete older data and rebuild indexes.

    Table & indexes are dictionary managed. Allocation_type is 'USER'.

    THE METRICS
    'analyze index index_name validate structure' reports a usage number that is significantly less than the allocated space (initial extent plus some number of next_extents allocated).

    THE QUESTION
    Given that the indexes are rebuilt after older data is deleted and I have calculated the bytes required for each index entry, can I expect to use the space for X additional rows before adding another Next_Extent?
    Where X = [ Allocated Space - Usage from 'Analyze ] / Index Row Size before adding another Next_Extent?

    A more rigorous estimation would be:

    Rows = floor(floor[(Avail Data Space/Avg Entry Size) * # of blks]/1.1)
    where:
    available data space = (block size - block header size) - ((block
    size - block header size) * (PCTFREE/100)) ; and
    bytes per entry = entry header + ROWID length + F + V + D
    where:
    entry header = 1 byte
    ROWID length = 6 bytes
    F = total length bytes of all columns with 1 byte column lengths
    (CHAR, NUMBER, DATE, and ROWID types)
    V = total length bytes of all columns with 3 byte column lengths
    (VARCHAR2 and RAW datatypes)
    D = combined data space of all columns

    I'd be happy to supply more details, if needed.
    Thanks in advance, d mck

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: Index size and use of space

    Originally posted by dmck43
    This may be a newbie question to most, but here goes:

    THE ENVIRONMENT
    Running 8.1.7
    Table contains audit trail type info
    Quantity of daily inserts is High
    Table is in one tablespace

    There are 4 indexes
    All indexes are in 1 tablespace (different from data tablespace)

    Maintenance on these data is as follows:
    When there is one next_extent left in either the Data or Index
    Tablespace, delete older data and rebuild indexes.

    Table & indexes are dictionary managed. Allocation_type is 'USER'.

    THE METRICS
    'analyze index index_name validate structure' reports a usage number that is significantly less than the allocated space (initial extent plus some number of next_extents allocated).

    THE QUESTION
    Given that the indexes are rebuilt after older data is deleted and I have calculated the bytes required for each index entry, can I expect to use the space for X additional rows before adding another Next_Extent?
    Where X = [ Allocated Space - Usage from 'Analyze ] / Index Row Size before adding another Next_Extent?

    A more rigorous estimation would be:

    Rows = floor(floor[(Avail Data Space/Avg Entry Size) * # of blks]/1.1)
    where:
    available data space = (block size - block header size) - ((block
    size - block header size) * (PCTFREE/100)) ; and
    bytes per entry = entry header + ROWID length + F + V + D
    where:
    entry header = 1 byte
    ROWID length = 6 bytes
    F = total length bytes of all columns with 1 byte column lengths
    (CHAR, NUMBER, DATE, and ROWID types)
    V = total length bytes of all columns with 3 byte column lengths
    (VARCHAR2 and RAW datatypes)
    D = combined data space of all columns

    I'd be happy to supply more details, if needed.
    Thanks in advance, d mck
    With disk space costing about $1/GB,
    you've wasted more money worrying
    about this issue that you'll ever save
    by using less disk space. Unless you are
    bored silly & need a task like this to keep
    you busy, I suggest that you go find another
    task with better RIO than this one.

  3. #3
    Join Date
    May 2003
    Location
    Atlanta
    Posts
    6

    Re: Index size and use of space

    Originally posted by anacedent
    With disk space costing about $1/GB,
    you've wasted more money worrying
    about this issue that you'll ever save
    by using less disk space. Unless you are
    bored silly & need a task like this to keep
    you busy, I suggest that you go find another
    task with better RIO than this one.
    Thanks for your response. However, we have 2 PROD servers with 500+ TB's each and the hardware types tell me that significant capital expenditures will be needed to add more storage - something to do with their disk farm design considerations...

    I did note in the desc for init_stats table there are fields called BTREE_SPACE and USED_SPACE. I wonder if the difference of those would be a better measure of space available for record keys.

    As for wasting time, my charge from mgmt is to maximize the age of the audit trail/history -- the user community wants 18 month, the dev team says 3-6 month. Mgmt wants independant analysis based on the first 8 months in PROD... and pointed at me! I decided not to say "I'm clueless". Obviously, I'm not clear about it either ;-)

    Thanks again for your response. Any other comments, thoughts are welcome. d mck

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Cost per Gb is not the issue. It could be the cost of equipment big enough to deal with the Gb's. I can put hundreds of Gigs of disk space on a P3 here, doesn't mean it's big enough to handle it.

    "Add another index, disk space is cheap" is not the way to gain efficient databases.

    Bill

Posting Permissions

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