Home » RDBMS Server » Performance Tuning » Table Shrink Space and Performance (11g Standard Edition,Linux x86_64)
Table Shrink Space and Performance [message #616387] Mon, 16 June 2014 08:37 Go to next message
preet_kumar
Messages: 204
Registered: March 2007
Senior Member
I would like to run the below given commands to reclaim space and HWM for one of the table.

alter table business enable row movement;
alter table business shrink space cascade;
alter table business disable row movement;

Please let me know does this operation locks the table and has any performance issue?

Does the Index space also get shrink and is the Index remain usable after the shrink is complete ?

Re: Table Shrink Space and Performance [message #616389 is a reply to message #616387] Mon, 16 June 2014 08:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I would like to run the below given commands to reclaim space and HWM for one of the table.
reclaim what space from where to where?

the amount of disk space is unchanged & remains the same before & after these commands.

why do you consider free space in a table to be a problem which requires any action?
Re: Table Shrink Space and Performance [message #616393 is a reply to message #616389] Mon, 16 June 2014 08:55 Go to previous messageGo to next message
pvsarat
Messages: 10
Registered: October 2012
Location: CHENNAI
Junior Member
before doing this shink space steps - have you purged any old data from the tables..? - If so then you can use these steps to reclaim the space.
Re: Table Shrink Space and Performance [message #616394 is a reply to message #616389] Mon, 16 June 2014 08:56 Go to previous messageGo to next message
preet_kumar
Messages: 204
Registered: March 2007
Senior Member
I am not talking here about disk space but to reset the HWM and table fragmentation.

Fragmentation comes with when we update/delete data in table.
The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation

High water mark of table actually defines the border line between (ever) used and unused (never) space. While performing full table scan, Oracle will always read the data up to HWM. And if there is lot of free space with-in HWM, that is read too, and hence degrading the performance of FTS.

This is the reason i want to run the shrink space operation.
Re: Table Shrink Space and Performance [message #616395 is a reply to message #616387] Mon, 16 June 2014 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Please let me know does this operation locks the table and has any performance issue?


1/ Yes, but not for all operations during all the time
2/ Yes, of course, you may also get errors in the applications.

Re: Table Shrink Space and Performance [message #616396 is a reply to message #616395] Mon, 16 June 2014 09:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Fragmentation comes with when we update/delete data in table.
fragmentation only exists between your ears.
post SQL & results that shows difference between free space & fragmented space
Re: Table Shrink Space and Performance [message #616397 is a reply to message #616396] Mon, 16 June 2014 09:16 Go to previous messageGo to next message
preet_kumar
Messages: 204
Registered: March 2007
Senior Member
@Blackswan What do you mean by "fragmentation only exists between your ear"

@pvsarat Yes i have deleted a huge amount of data from the table.

Below given the result
TABLE_NAME ACTUAL_MB OPTIMAL_MB CLAIMABLE_MB
--------- ---------------------- ------- -------------
Business_Plan 179811 142927 36884

Please if possible answer the below given questions with valid reason instead of vague responses.

* If operation locks the table during the entire shrink operation?
* Does the Index space also get shrink and is the Index remain usable after the shrink is complete ?

[Updated on: Mon, 16 June 2014 09:18]

Report message to a moderator

Re: Table Shrink Space and Performance [message #616398 is a reply to message #616397] Mon, 16 June 2014 09:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>@pvsarat Yes i have deleted a huge amount of data from the table.

will this occur again in the future?
Re: Table Shrink Space and Performance [message #616400 is a reply to message #616397] Mon, 16 June 2014 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Please if possible answer the below given questions with valid reason instead of vague responses.


See my answer above.

Re: Table Shrink Space and Performance [message #616401 is a reply to message #616400] Mon, 16 June 2014 10:34 Go to previous messageGo to next message
preet_kumar
Messages: 204
Registered: March 2007
Senior Member
@Michael Cadot

You only replied to the first question not to the second "Does the Index space also get shrink and is the Index remain usable after the shrink is complete ?"
Re: Table Shrink Space and Performance [message #616403 is a reply to message #616401] Mon, 16 June 2014 10:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
consider to do simple test run to see the answer for yourself.
Re: Table Shrink Space and Performance [message #617551 is a reply to message #616401] Tue, 01 July 2014 08:10 Go to previous message
martens_b2014
Messages: 13
Registered: June 2014
Junior Member
1. No, your index will not shrink.
2. Yes, you can still use your index although I would advice to coalesce, shrink or rebuild it.

Previous Topic: UNDO Setup Issue
Next Topic: I/O calibration
Goto Forum:
  


Current Time: Thu Mar 28 17:24:29 CDT 2024