Re: Preparing a table to reorg
- From: Neil Kodner <nkodner@xxxxxxxxx>
- To: Ian Cary <ian.cary@xxxxxxxxxxxxxx>
- Date: Thu, 17 Jun 2010 10:01:59 -0400
But in order to alter the table and rebuild it, i'd have to take it offline,
which wont work in our 24x7 shop. The problem is I have no real way of
estimating how long an offline rebuild would in fact take. Dropping a
column took about 4.5 hours.
On Thu, Jun 17, 2010 at 9:43 AM, Ian Cary <ian.cary@xxxxxxxxxxxxxx> wrote:
> Wouldn't storing the CLOB out of line be what you are looking for. You
> could do this with a simple alter table command e.g.
>
> alter table your_table move lob(your_clob_column) store as (tablespace
> your_lob_tablespace);
>
> This statement will move the lob and the lob index to a separate tablespace
> but will also rebuild the table itself.
>
> This should give the same effect as moving the clob to a separate table but
> without much effort or view requirement.
>
> Cheers,
>
> Ian
>
>
>
>
> nkodner@xxxxxxxxx
> Sent by: To: mwf@xxxxxxxx
> oracle-l-bounce@fr cc:
> oracle-l@xxxxxxxxxxxxx
> eelists.org Subject: Re: Preparing a
> table to reorg
>
>
> 17/06/2010 11:40
> Please respond to
> nkodner
>
>
>
>
>
>
> 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?
>
>
>
>
>
>
>
>
>
>
> This email was received from the INTERNET and scanned by the Government
> Secure Intranet anti-virus service supplied by Cable&Wireless Worldwide in
> partnership with MessageLabs. (CCTM Certificate Number 2009/09/0052.) In
> case of problems, please call your organisation?x02019;s IT Helpdesk.
> Communications via the GSi may be automatically logged, monitored and/or
> recorded for legal purposes.
>
> For the latest data on the economy and society consult National Statistics
> at http://www.ons.gov.uk
>
>
> *********************************************************************************
>
>
> Please Note: Incoming and outgoing email messages are routinely monitored
> for compliance with our policy on the use of electronic communications
>
> *********************************************************************************
>
>
> Legal Disclaimer : Any views expressed by the sender of this message are
> not necessarily those of the Office for National Statistics
>
> *********************************************************************************
>
>
> The original of this email was scanned for viruses by the Government Secure
> Intranet virus scanning service supplied by Cable&Wireless Worldwide in
> partnership with MessageLabs. (CCTM Certificate Number 2009/09/0052.) On
> leaving the GSi this email was certified virus free.
> Communications via the GSi may be automatically logged, monitored and/or
> recorded for legal purposes.
>
Other related posts:
