DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Minimize Rows_per_block

  1. #1
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78

    Minimize Rows_per_block

    Hi,

    I recently implemented a "minimize records_per_block" against a table to eliminate row migration, within a database used with a third-party application.

    (The table had virtually empty rows inserted, that were then massively updated... leading to >60% row migration.)

    I pre-populated the table with X-rows of "null" data, where X was the optimum number of rows per block, from pseudo-live data, and then issued the minimize command. Loading of subsequent real data resolved the migration problem completely, with minimum extra row storage overhead.

    The application vendor is supposed to be incorporating this methodology into future "builds", one of which they will be re-implementing on our site shortly.

    Question (now I get to it), is:

    Where on earth does Oracle store information relating to minimized row tables in it's dictionary??

    (I might be heavily presuming here... that it actually DOES store it somewhere where we can logically see it!)

    I can't see anything in a regular V$View that looks a "candidate". I'm wondering if the value gets "tucked away" in an X$ table somewhere....

    I'm basically wanting to check IF they have implemented the remedy, prior to our users starting to load in the data (and then having us suffer migration, dire performance overhead, and necessary table rebuilds somewhere down the line.....

    Clues anyone?

    Best regards,

    - Tony.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    ill check, curious though why not use pctfree? minimize rows per block is a good solution but if you get the rows wrong then you can waste a lot of space / incur more io

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It's a column available through the sys.tab$ column spare1, which stores a value known as the Hakan factor.

    There are a lot of bugs and "features" associated with this, by the way. Have a search in metalink.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  4. #4
    Join Date
    Mar 2006
    Posts
    40
    Why not use AVG_SPACE in the DBA_TABLES which is the average amount of free space, in bytes, in a data block allocated to the table. You can use CHAIN_CNT - Number of rows in the table that are chained from one data block to another, or which have migrated to a new block, requiring a link to preserve the old ROWID.

    You will need to do analyze table or dbms_stats.gather_table_stats in order to collect this data.
    go to the oracle clinic
    Quick and easy Oracle database support and consulting

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    what good would that do, he knows how much space he wants as he knows how much his rows grow?

    He is trying to avoid exactly the things you are detecting there

  6. #6
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    Many thanks for the responses!

    I didn't use PCTFREE as it was a bit of an "unknown" as to what fields are originally "populated" (some are), prior to the update routines that then run.

    I've only had a week to look at the performance on this application, and all the code was "wrapped", so I couldn't decipher at a glance, exactly how it all worked.

    Rather than try and figure out which ones the vendor was populating to begin with, and with what values (and support wasn't available to me [different timezone], developer on "annual leave"), I jumped in and implemented a quick-fix, rather than "trial and error" a guess for PCTFREE.

    I knew what the max rowsize was, and there wasn't much variation from it, so I eliminated guesswork and stumped for minimize rows per block.

    :-)

    - Tony.

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