RE: table shrink

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 10 Dec 2009 10:25:27 -0500
Message-ID: <538C2C4E3FCF4E3490505D622A562B12_at_rsiz.com>

I use copy-keep as an umbrella for all of the methods having the same resulting information as deleting what is to be removed. Moving the data twice is usually suboptimal compared to moving the data once and doing the appropriate dance with the object names and references. (And "temporary" table has various distracting meanings in Oracle, so even if you choose to do the data movement that way, I would use an "interim" permanent table. Though I've had little success in de-ambigufying that in common conversation since 1989.)

Notably, in your case, copying the data back into the original table would not accomplish the apparent goal of releasing 40GB of extents allocated to that table.

It is probably worth mentioning that if that table is the only table in that tablespace, then copying the data to a diffent place and doing an offline drop of the existing tablespace is probably the cheapest way to get your storage back, and it will extend the reclamation all the way to your OS, or in the case of ASM, to the disk group.

Regards,

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of genegurevich_at_discover.com Sent: Wednesday, December 09, 2009 6:28 PM To: Mark W. Farnham
Cc: oracle-l_at_freelists.org
Subject: RE: table shrink

Mark,

When you refer to copy-keep are you referring to copying the data from my table to a temporary table and loading them back into the original table?

Thank you

Eugene Gurevich

<snip>

No.

The undo required will be about:

1. the space management changes undo from all the other things
2. the overhead for the "block deltas" for the rows being moved
3. the whole size of the row from the block it is "deleted" from
4. the whole size of the row in the block it is placed in
5. all the index updates for any row that moves.

But without examining the table details, we have no idea what fraction of the rows or total size might have to move to accomplish the shrink.

Now I don't see any size to keep info clause in the shrink clause. What I see is:
shrink space [compact][cascade]

and a list of restrictions about when you can use it (which mostly appear related to flows that would be really complex to code and really stupid to try to do anyway.)

So I think this means you've purged (or are planning to purge) 40G of 41G and then do the shrink. That's a really bad idea from the standpoint of how much work the computer has to do to accomplish the goal and how much undo you'll need. Copy-keep the 1G to somewhere else, even if you have to make a union view to keep it continuously available if new inserts are continuing during the move. Of course if you directly address the table right now, then you'll have a slight bump when you rename the table and create the view. If you already reference the table as an all inclusive view wrapper or a synonyn, then create or replace view or synonym will handle this for you. Sure, the database engine will have to reparse some queries, but the data will be continuously available. I think I've written about this before and it should be in the archives. Being able to do this thing, not the convenience of not having to drop a view or synonym before creating a different one of the same name, was the driving requirement of the Oracle VLDB advising Oracle to implement this with "breakable parse locks" in the early 1990's and it is a shame anyone purges by delete instead of using copy-keep. Of course if you can stand an outage, you can still use copy-keep and the much cheaper direct mode. Then you can unhook the old table and dispose of it at your leisure in a mode that only has undo for the dictionary and space management changes.

Obviously be careful and test.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of genegurevich_at_discover.com Sent: Monday, December 07, 2009 5:51 PM
To: oracle-l_at_freelists.org
Subject: RE: table shrink

Thank you, Mark

Based on the oracle's recomendation, about 1 G will be kept in the table and about 40G+ will be released. Does that this mean that the max undo I will need is 1G?

Thank you

Eugene Gurevich

For a quick rough estimate, remember that the only undo you'll get from the empty blocks is a tiny amount from the space management. It is also very unlikely that any block lower in extent order than the first empty block can
move. Finally, the total of blocks that still do have contents is a rough ceiling on the undo, so if you're keeping less than you're shrinking that is
a consideration.

If you can be out of action a bit, create table as select to keep the rows you want to retain is probably the cheapest thing unless the amount kept is greater than two times (and then measurements are required and the results vary depending on indexes, etc.) the amount to be shuffled, and you might have an opportunity to order the select so that in your destination your most frequently used access path gets the best possible cluster score and lowest cost for both the CBO and actual run time. Then of course you probably do a rename and re-index.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Adam Musch
Sent: Monday, December 07, 2009 10:58 AM To: genegurevich_at_discover.com
Cc: oracle-l_at_freelists.org
Subject: Re: table shrink

Remember, if it's moving rows (and therefore, changing ROWIDs), it's also got indexes to update. So it could require significantly more UNDO than the size of the table. If you can afford the downtime, you may be better off with impdp/expdp, alter table move, and/or online table redefinition.

On Mon, Dec 7, 2009 at 9:46 AM, <genegurevich_at_discover.com> wrote:
> Hello everyone:
>
> I am researching alter table shrink space command in 10.2.0.3 before I
try
> it in production. According to oracle adviser, there is a table that can
be
> shrank
> to release over 40G of space and another which can release over 100G. I
> plan to execute the commands during a maintenance window to minimize the
> usage of these tables. I have run a small test in dev and saw that these
> command have been using undo tablespace. That tablespace is only 20G
> in my database. Does it need to match the size of the 'freeable' disk
> space, in other word do I need to increase it to about 100G? I also was
> told that
> i can cancel this command any time (in case there is a performance
> degradation) and whatever disk space has been released will stay released
> and
> I can run the command again to release the rest of the disk space. I have
> not been able to find this in any oracle documentation however. I don't
> have any table in dev to test a large shrink command.
>
> If anyone had any experience with this command, can you please address my
> questions on undo TS and cancelling the command please. I am
> alsowondering whether there is any downside to this operation.
>
> Thank you
>
> Eugene Gurevich
>
>
> Please consider the environment before printing this email.
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
Adam Musch
ahmusch_at_gmail.com
--
http://www.freelists.org/webpage/oracle-l








Please consider the environment before printing this email.


--
http://www.freelists.org/webpage/oracle-l


Please consider the environment before printing this email.
i0zX+n{+i^


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 10 2009 - 09:25:27 CST