-
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.
-
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
-
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.
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|