Re: Preparing a table to reorg
- From: Neil Kodner <nkodner@xxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>
- Date: Thu, 17 Jun 2010 06:40:08 -0400
Out of your ideas, I like the default 2k(or so) best. I've approached the
devs with the idea of moving the CLOB to a separate table but they say it
can't be done without a lot of effort. They also dont want me to 'fake it'
behind the scenes with views, etc.
Inserting a default clob makes sense though.
On Wed, Jun 16, 2010 at 3:54 PM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
> Have you considered having a standard 2K (or so) CLOB to slap in there
> when there row is born?
>
>
>
> Depending on the aggregate vsize of the other columns in a row and the
> details of the block overhead of your tablespace and segment specifications,
> you’re probably getting 3 rows per block. If your skew around that average
> of 2099 is not too bad, this will minimize row migration at a cost of the
> extra redo on the insert.
>
>
>
> If you elect to leave the standard filler CLOB in place, that will cost you
> an extra 1/6th storage for where you have existing nulls. Whether it would
> be cheaper overall to whack those to NULL in your process depends on your
> cost of storage and the redo to whack it.
>
>
>
> That is only a win if the overhead from the row migration is actually a
> problem to you on the access side, and it treats your outlier bigger than
> average clobs as noise.
>
>
>
> Your details matter, and it might be possible to set pctfree, pctused, and
> the size of the default CLOB so that a single row takes the block off the
> free list and when its clob gets updated to the real value it only puts the
> row back on the free list if it is likely another row will fit. (I’m not
> saying 2K is the right size to do that. Your mileage will vary.
>
>
>
> Another reasonable thing to do that comes to mind is having another table
> altogether for the CLOB. Existing queries could be supported via a view.
> Whether that would be an improvement over your current situation with many
> migrated rows is open to question and measurement.
>
>
>
> I could see using partitioning to solve this as well, where you let the
> current “period” get built ugly and then create a nice clean image when
> current becomes previous and use partition exchange to swap that back in for
> the ugly version of the same data.
>
>
>
> If you don’t have partitioning, you could try some hybrid where you’re
> building new rows into two tables union-all’d with the permanent table and
> cycle the rows into the permanent table periodically. That only works if
> your operational schedule has the right opportunties or you’re willing to do
> the “poor man’s partitioning” shell game with views and synonyms.
>
>
>
> Or maybe someone else has some better ideas,
>
>
>
> mwf
> ------------------------------
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Neil Kodner
> *Sent:* Wednesday, June 16, 2010 2:42 PM
> *To:* oracle-l-freelists; Neil Kodner
> *Subject:* Preparing a table to reorg
>
>
>
> I'm looking to use dbms_redefinition to reorganize a table that is affected
> from migrated rows. The table has an average row length of 2131 on a
> tablespace with an 8k block size. Nearly half the records in the table are
> migrated.
>
>
>
> The table contains an inline CLOB with an average length of 2099. Out of
> 6M rows, 1M clob values are null, and out of the remaining 5M, its longer
> than 4000 characters about 10,000 times.
>
>
>
> When a row in this table is created, the clob is empty and later populated
> with an UPDATE statement. The actual values for the clob field aren't
> determined until a few days after the row has been inserted. At that point
> the clob is written along with modifiy user/date fields. Not much else gets
> updated for each row.
>
>
>
> The table has pctfree 40 and its pctused is null.
>
>
>
> I would imagine the update is what's causing the migration. My gut tells
> me that pctfree is wrong but I'm not sure how to tune it.
>
>
>
> Since I'm looking at redefining the table, I'm also considering moving the
> clob out of line because 1) it's not referenced in the majority of the
> queries and 2) it has the opportunity to be greater than 4000 characters but
> it's not all that likely that it will be.
>
>
>
> I'd love to hear thoughts and opinions on the pctfree/pctused and clob
> issues before moving forward with the rebuild in a test environment.
>
>
>
> And finally, with an average row length of 2131, would I benefit from
> rebuilding this table on a tablespace with a larger block size?
>
>
>
>
>
Other related posts:
