| Reg Segment advisor [message #515396] |
Mon, 11 July 2011 06:28  |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
Hi All,
I understand from oracle 10g, to reclaim the wasted space of a segment, we can do a segment level shrink in online through the following way.
--Enable Row Movement.
ALTER TABLE scott.emp ENABLE ROW MOVEMENT;
-- Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;
-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;
Now My concern is, when any package,procedure,functions or cursors are refering this emp table, can we fire the below command to enable the row movement of that table?
--Enable Row Movement.
ALTER TABLE scott.emp ENABLE ROW MOVEMENT;
As per my knowledge, it is a DDL statement and if we fire a DDL statement when package or cursor accessing this table, the package or cursor will be getting as INVALID.
Then how oracle saying as we can do this activity in online.
I cannot able to understand. Pls correct me if I am wrong.
|
|
|
|
|
|
| Re: Reg Segment advisor [message #515412 is a reply to message #515403] |
Mon, 11 July 2011 07:45  |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
Thanks for your response.
So altimately can I take your answer as, executing shrink space statement during online will bring u in risk side at some time?
(Or)
To avoid risk 100%,we need to execute this shrink space statement after declaring outage for that particular table?
|
|
|
|
| Re: Reg Segment advisor [message #515417 is a reply to message #515412] |
Mon, 11 July 2011 08:05 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:So altimately can I take your answer as, executing shrink space statement during online will bring u in risk side at some time?
Yes
Quote:To avoid risk 100%,we need to execute this shrink space statement after declaring outage for that particular table?
It is not mandatory but better (if you don't want an unexpected outage).
But the question remains: "do you really need to shrink space?"
Regards
Michel
[Updated on: Mon, 11 July 2011 08:06] Report message to a moderator |
|
|
|
| Re: Reg Segment advisor [message #515528 is a reply to message #515417] |
Tue, 12 July 2011 02:18 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
yes. Every week I am doing defragmentation activity for 10 to 15 tables by using alter table move.
To avoid the outage, thought of approching shrink space way. but now I understood, alter table move is best instead shrink space,since it is faster and also both way requires outage.
Can I have any best way than this, if exist?
|
|
|
|
|
|
| Re: Reg Segment advisor [message #515530 is a reply to message #515396] |
Tue, 12 July 2011 02:28 |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
>if we fire a DDL statement when package or cursor accessing this table, the package or cursor will be getting as INVALID.
>Then how oracle saying as we can do this activity in online.
Because the package, cursor etc will be recompiled/reparsed at the next call.
Also remember that you need to ENABLE ROW MOVEMENT only *once* in the lifetime of a table. If you are shrinking the table every week (I wonder if you really need to do this every week), you don't have to ENABLE ROW MOVEMENT every week ! It is a table attribute, once set always present (unless you drop and recreate the table).
Hemant K Chitale
|
|
|
|
| Re: Reg Segment advisor [message #515532 is a reply to message #515529] |
Tue, 12 July 2011 02:32 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
because those tables are having heavy DML's. that we can't avoid. And the project currently not willing to add storage in that box.
|
|
|
|
| Re: Reg Segment advisor [message #515535 is a reply to message #515532] |
Tue, 12 July 2011 02:39 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
Hi hemant,
Thanks for your response.
I agree all your points.
But If we keep the row movement enabled, the performance of the queries accessing that table will be getting degraded. So we can't keep the row movement enabled all the time.
Pls correct me if am wrong
|
|
|
|
|
|
| Re: Reg Segment advisor [message #515537 is a reply to message #515535] |
Tue, 12 July 2011 02:51 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:But If we keep the row movement enabled, the performance of the queries accessing that table will be getting degraded. Hi - please can you explain why you say this? I haven't yet attempted to falsify your hypothesis, but I cannot see why enabling row movement would have any bad effect.
|
|
|
|
| Re: Reg Segment advisor [message #515584 is a reply to message #515537] |
Tue, 12 July 2011 05:01 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
Quote:Hi - please can you explain why you say this? I haven't yet attempted to falsify your hypothesis, but I cannot see why enabling row movement would have any bad effect.
"enable row movement" can cause Oracle to move rows to discontinuous data blocks. so the order of the index entry or blocks will be getting changed causes clustering factor value to high than previous causing excessive I/O.
|
|
|
|
| Re: Reg Segment advisor [message #515586 is a reply to message #515584] |
Tue, 12 July 2011 05:06 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
because those tables are having heavy DML's. that we can't avoid
Quote:What make you think that you can't avoid it?
we are getting high number of inserts and deletes on this table. The application design is as such. we cannot think of changing the application design just because of avoiding fragmentaion. Thats why I said as, we cannot avoid.
|
|
|
|
| Re: Reg Segment advisor [message #515587 is a reply to message #515584] |
Tue, 12 July 2011 05:07 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:"enable row movement" can cause Oracle to move rows to discontinuous data blocks.
This is totally wrong, Oracle does not move rows unless you tell it to do so (with an ALTER TABLE for instance).
Quote:so the order of the index entry or blocks will be getting changed
Where did you see that? The position of a row in an index is determined by the value of the index columns for this row not by its rowid.
Regards
Michel
[Updated on: Tue, 12 July 2011 05:12] Report message to a moderator |
|
|
|
| Re: Reg Segment advisor [message #515589 is a reply to message #515584] |
Tue, 12 July 2011 05:09 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
No, you have misunderstood this. Enabling row movement means that Oracle will permit operations that cause rowids to change. These operations can include SHRINK SPACE, and DML on partition key columns. It doesn't cause anything - it merely makes things possible.
|
|
|
|
| Re: Reg Segment advisor [message #515592 is a reply to message #515589] |
Tue, 12 July 2011 05:13 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
But after shrinking the space, the order of the rowid will be getting changed. In that case, the query performance will be getting degraded. Am I right or wrong?
I think in that case, we need to rebuild the index to order the index entries. pls correct me if I am wrong.
|
|
|
|
| Re: Reg Segment advisor [message #515593 is a reply to message #515592] |
Tue, 12 July 2011 05:17 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:But after shrinking the space, the order of the rowid will be getting changed. In that case, the query performance will be getting degraded. Am I right or wrong?
So why do you shrink?
This is wrong, it may be better, it may worse, it may not change, this is one of the reason we asked "Why do you do that?".
Quote:I think in that case, we need to rebuild the index to order the index entries
See my previous answer.
The property of an index is to have its entries sorted otherwise it is (most often) useless.
Regards
Michel
|
|
|
|
| Re: Reg Segment advisor [message #515596 is a reply to message #515592] |
Tue, 12 July 2011 05:34 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
A table shrink operation is, in effect, a series of insert/delete/commit statements: the end result is that rows are relocated, as far as possible, towards the first blocks of the segment. The clever part is that insert and delete triggers do not fire. Indexes are maintained. Following this DML phase (which may take a long time) the high water mark of the segment is brought down, in order to release the free space (this step is virtually instantaneous). So, to revert to your original question, this operation is "online", except for individual row locks as the rows are moved, and a table lock when the high water mark is adjusted.
As Michel says, you need to be clear on why you want to do this. I can see no benefit, unless the number of rows in the table is continually reducing. If it is constant or increasing, there is no point.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
