02 March, 2008

ALTER TABLE ... SHRINK SPACE

It took me some time (as I built the test and validation scripts iteratively) to setup a test to evaluate the ALTER TABLE ... SHRINK SPACE command available in Oracle 10g. I ran these tests (see the document) on 32-bit 10.2.0.3 on Windows XP on my home PC.
(I couldn't extend the tests to larger sizes as "testing the tests" themselves would take signficant time on my PC !).
It seems as if, besides, reorganising the usage of blocks within a table, the command can also identify and drop extents (seems to have been intelligent enough to drop the last 3 extents although rows had been deleted from random blocks across multiple extents).

Update : There is an Oracle Bug logged for the high Undo and Redo generated by an ALTER TABLE ... SHRINK SPACE command. Bug #3888229 "HUGE REDO AND UNDO GENERATED DURING A TABLE SHRINK OPERATION. "

Update 10-Mar-08 : I have added Block, Redo and Undo statistics for the SHRINK operation. These indicate signficant activity.
I've also run a test with Uniform Size 128K instead of AutoAllocate. At the current table size, there is no significant difference.
The document (link above) has been updated.

16 comments:

Aman.... said...

Hemant,
A probably stupid question.When we do shrink space compact,HWM doesnt move.Only the rows are moved from left to right.Then in that case, for the subsequent DMLs , how Oracle determines where the row will go, in which block?If I understand it correctly, it should go below the HWM only means the space which has become empty after pcking the rows.But I shall appreciate if you can elaborate it.
Regards
Aman....

Hemant K Chitale said...

I don't understand what you mean by "Only the rows are moved from left to right".

When doing an INSERT (other than Direct Path), Oracle uses FreeLists in MSSM and BitMaps in ASSM to identify "candidate" blocks. Whether you have recently done a shrink or not, what matters is the blocks Oracle sees as "candidates" for the new row(s). If it finds no available blocks below the HWM, it increments the HWM by allocating new blocks.

So, if you have done a SHRINK SPACE COMPACT, the ASSM BitMap for that table segment would have been updated.

Aman.... said...

Hemant ,
Thanks for the reply.What I wanted to say that withthe shrinking rows are moved from right to left(sorry I was wrong in my first response).Now with shrink space compact, the HWM is not moved but only the rows are moved.So now assume that there are 2 completely empty blocks below the HWM and there are 3 empty blocks above the HWM,unused blocks.Now when the next insert is issued, where it will go?I guess should go below HWM only.Is this correct?
I agree to your point that the Bitmap is updated.So does this suits the point that I just mentioned?
Thanks alot for the reply.
Regards
Aman....

Hemant K Chitale said...

Available blocks below the HWM would be used.

Oracle allocates blocks above the HWM in either of
a) Direct Path
b) non availability of empty blocks below the HWM

Think : If, as in the example you quote, Oracle decided to use blocks above the HWM, what would the meaning of HWM be then ?

Aman.... said...

Hemant,
Thanks its pretty much clear now.Thanks a ton.
Regards
Aman....

tk said...

Does the "alter table move" reclaim space above HWM as well as below HWM?

I mean is the feespace above hwm is given back to tablespace if I issue "alter table move"

Hemant K Chitale said...

tk,

An ALTER TABLE .. MOVE may result in requiring lesser extents for the new table segment than the previous table segment -- in which case, the HWM is also lower.
Don't think of it as releasing space "above the HWM and below the HWM". ALL the extents used by the table segment are released, because the table segment gets allocated new extents !

Hemant

TK said...

Thanks sir;

Supose I have blocks in freelist or bitmap.
Some of these blocks are entirely empty, some of them has data.

When I issue "alter table move",
If the block is entirely empty, oracle will try to reclaim that space to tablespace.

Whatif the block is not entirely empty?
Does oracle move the rows from one block to another to make the block empty and then reclaim the

Hemant K Chitale said...

TK,

Oracle does a MOVE by allocating *extents*. It allocates new extents consisting of a fresh set of blocks. Existing blocks are ignored. Existing extents (and, therefore, the blocks they contained, whether the blocks were 0% or 1% or 99% full) are freed and released if the MOVE is successful.

Hemant

TK said...

Suppose this is the table:

b->block

b1b2b3b4-b5b6b7b8-b9b10b11b12

Lets say
b5 is entirely empty, b6 is also in freelist but has some rows in it.


Regarding the new table once I issue alter table move:
Does the b6 contain again same rows? or does oracle move these rows to some other blocks?

Hemant K Chitale said...

TK,

Read again "Oracle does a MOVE by allocating *extents*. It allocates new extents consisting of a fresh set of blocks.". If you cannot understand it, read the docs again and again. Run your own experiments.

Hemant

TK said...

so, the rows in the block doesnt change. am i right?

Hemant K Chitale said...

*New* extents with *new* blocks are allocated and the old extents and old blocks are released as free space.

"the rows in the block doesnt change" is *completely* the wrong thing to say.

Hemant

Anonymous said...

I'm trying to get the DBAs to use SHRINK on our 10g database. They are reluctant as they say it has some bugs.
To your knowledge, what bugs exist in 10g (10.2.0.4.3)

Hemant K Chitale said...

Anonymous,

I have already pointed out 'Bug #3888229 "HUGE REDO AND UNDO GENERATED DURING A TABLE SHRINK OPERATION. "'.


Hemant K Chitale

Anonymous said...

Hi guys,
Here is a good explanation how to reclaim the wasted space in a segment
http://dbpilot.net/2018/02/14/reclaiming-wasted-space-in-a-segment/